CODEWITHSUNDEEP
mysqlruby-on-railsruby-on-rails-3
neimad
neimad

Reputation: 229

Rails 3 SQL query select with joins

I have a problem with select fonction and joins.

here is my current query.

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

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 %>

this is the error i get

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:

thanks

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

<%= debug @building %>

give me 

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

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

thanks

-- Update 14/09

Here is my new controller

  @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

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?

Thanks.

Upvotes: 0

Views: 714

Answers (2)

Anuj
Anuj

Reputation: 1747

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")

Upvotes: 2

apneadiving
apneadiving

Reputation: 115511

Look at your code:

Building.select('buildings.name, floors.number, spaces.number)...

You don't select the building id, so Rails is a bit lost when comes the time to retrieve it.

Upvotes: 2

Related Questions