Rails 3 SQL查询选择联接 [英] Rails 3 SQL query select with joins

问题描述

我对选择功能和连接有疑问.

I have a problem with select fonction and joins.

这是我当前的查询.

@search = Building.joins('INNER JOIN "floors" ON "floors"."building_id" = "buildings"."id" INNER JOIN "spaces" ON "spaces".floor_id = "floors".id')

但是我想选择更多的选项来使用我尝试过的floor.number，space.number

but i want to have more option in my select to use floors.number, space.number i tried this

@search = Building.select('buildings.name, floors.number, spaces.number).joins('INNER JOIN "floors" ON "floors"."building_id" = "buildings"."id" INNER JOIN "spaces" ON "spaces".floor_id = "floors".id')

我认为我犯了错误..这是我的观点

in my view i got erreor.. here is my view

<% for b in @building do %>

<div style='width:100%;margin-left:auto;margin-right:auto;margin-top:5px;'>

    <div style="float:left;width:50%"><%= b.name %></div>
    <div><%= link_to 'view', building_path(b) %></div>

</div>

<% end %>

这是我得到的错误

ActionController::RoutingError in Search_engine#show

Showing /Users/stephanebaribeau/Sites/cadifice/app/views/search_engine/show.html.erb where line #21 raised:

No route matches {:action=>"show", :controller=>"buildings", :id=>#<Building name: "gigi">}

Extracted source (around line #21):

18:     <div style='width:100%;margin-left:auto;margin-right:auto;margin-top:5px;'>
19:     
20:         <div style="float:left;width:50%"><%= b.name %></div>
21:         <div><%= link_to 'view', building_path(b) %></div>
22:         
23:     </div>
24: 

谢谢

在选择上添加了building.id，floors.id和spaces.id之后，我尝试显示floor.number和spaces.number

After adding building.id, floors.id and spaces.id on my select i try to show the floors.number and spaces.number

<%= debug @building %>

给我

[#<Building id: 9, name: "234234">]

我不知道为什么我只有2个元素，也许是因为选择位于Building.select上?

i dont know why i have only 2 elements, maybe it's because the select are on a Building.select?

谢谢

-更新14/09

这是我的新控制器

  @search = Building.select('buildings.id, buildings.slug, floors.id, spaces.id, buildings.name, floors.number, spaces.number').joins('INNER JOIN floors ON floors.building_id = buildings.id INNER JOIN spaces ON spaces.floor_id = floors.id')
  @search = @search.where("buildings.name like '%#{params[:building_name]}%'") if !params[:building_name].blank?
  #@search = @search.where("buildings.name like ?", params[:building_name]) if !params[:building_name].blank?
  if params[:space_type].present?
    @search = @search.where("spaces.space_type_id = ?", params[:space_type][:space_type_id]) if !params[:space_type][:space_type_id].blank?
  end
  @search = @search.where("floors.min_net_rent >= #{params[:floor_min_rent]}") if !params[:floor_min_rent].blank?
  @search = @search.where("floors.max_net_rent <= #{params[:floor_max_rent]}") if !params[:floor_max_rent].blank?

  @building = @search

我现在遇到的问题是调试给了我什么.距大楼只有2个田地.是因为有Building.select吗?我怎样才能在我的选择字段中得到所有信息?

The problem i have now is what debug give me. only 2 fields from the building. Is it because of the Building.select? How can i get all inside my selectfield?

谢谢.

推荐答案

您可以这样做... 希望它有用

You can do it like this... Hope its useful

@search = Building.select("buildings.name, floors.number, spaces.number").joins("INNER JOIN floors ON floors.building_id = buildings.id INNER JOIN spaces ON spaces.floor_id = floors.id")

这篇关于Rails 3 SQL查询选择联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！