Ruby on Rails - Where clause in multiple tables

Question

Using rails 3.2.3.

This is driving me crazy. I know that active record is supposed to simplify these types of actions but I can't get my head around it.

I have a Hotel and Facility model. Both have a has_and_belongs_to_many relation. A hotel can have many facilities and my goal is to search a hotel by its name/location, rating and facilities.

I am able to search by the first two but can't search by facilities (they are checkboxes on the search form).

View code:

<%= form_tag hotels_path, :method =>'get' do%>
        <p>
            <b>Location:</b>
            <%= text_field_tag :search, params[:search]%><br /><br />


            <b>Rating:</b><br />
            <%= radio_button_tag :ranking, '5'%>5 Stars<br />
            <%= radio_button_tag :ranking, '4'%>4 Stars<br />
            <%= radio_button_tag :ranking, '3'%>3 Stars<br />
            <%= radio_button_tag :ranking, '10'%>Hostels<br /><br />


            <b>Facilities:</b><br />
            <% for facility in Facility.find(:all) %>
                <div class="field">
                    <%= check_box_tag "fc[]", facility.id%>
                    <%= facility.description %>
                </div>
            <% end %>

            <%= submit_tag "Search", :name => nil%>
        </p>
        <%end%>

hotel controller:

 def index
    @hotels= Hotel.search(params)

    respond_to do |format|
      format.html
      format.json { render :json => @hotels }
    end
  end

hotel model:

 def self.search(params)

     if params

      arel = where('city LIKE ? OR name LIKE ?', "%#{params[:search]}%", "%#{params[:search]}%")
      arel = arel.where('ranking = ?', params[:ranking]) if params[:ranking].present?
      #arel = arel.where (' = ?', params([:fc])) -> i dont know what to put here

      arel
     else
       all
     end
    end

Basically it needs to fetch the name/location, narrow it by ranking and then narrow it even more by the selected checkboxes. I can't get the last one. The name/location and rating are in the hotels table but each hotel's facilities are in the facilities_hotels joint table (that table has for keys the hotel_id and the facility_id) By the way, I am able to create a hotel with all the facilities I want, so the relation between the two is correct.

Any help is highly appreciated

score 5 · Accepted Answer · answered Apr 20 '12 at 17:54

5

You need to add a joins clause to get it to join to the other table.

.joins(:facilities_hotels).where('facilities_hotels.facility_id = ?', params[:fc])

answered Apr 20 '12 at 17:54

DGM

26,629
7
58
79

Thank you DGM that was very enlightening. Although I have another issue with it. It says :Association named 'facilities_hotels' was not found; perhaps you misspelled it? Extracted source (around line #72): <% @hotels.each do |hotel|%> Do you have any idea of why this is happening? Its weird sating the association isn't formed. Thank you again for your answer in such a short notice. – Silver Apr 20 '12 at 21:39
It should look for whatever your has_many call is to. If it is a habtm call, then the call may need to be more explicit: `joins('INNER JOIN facilities_hotels')` – DGM Apr 20 '12 at 22:29
For more information, see: http://guides.rubyonrails.org/active_record_querying.html#joining-tables – DGM Apr 20 '12 at 22:30
Sorry to be bothering but i have anoher issue.Although i've searched,im getting an error on my view on `<% @hotels.each do |hotel|%>?` With your code, if I select one facility, it doesnt filter, and if I select 2 or more, it gives out that error. Also, if I do this on the model `arel.joins('INNER JOIN facilities_hotels ON facilities_hotels.facility_id = ?', params[:fc])` it also crashes. Sorry to be asking here again, your answer was very helpfull,but it is asking me to change the view code to display hotels and I don't know what to modify this (`<% @hotels.each do |hotel|%>?`)to. Thank you – Silver Apr 22 '12 at 11:33

Ruby on Rails - Where clause in multiple tables

1 Answers1