Ruby on Rails: Search on has_many association with inner join and other conditions

I've been searching for this for a long time now, and I can't get to find the answer anywhere. What I have is a basic search function to find houses, everything works, and till so far the main piece for getting houses looks something like this (the conditions are for example):

@houses = House.all(
    :conditions => ["houses.province_id = ? AND open_houses.date > ? AND houses.surface = ?", "4", "Tue, 08 Feb 2011", "125"],
    :select => 'distinct houses.*',
    :joins => "INNER JOIN open_houses ON open_houses.house_id = houses.id" )

Now, I have this has_many association for the specifications of a house (like balcony, swimming pool etc).. For this I have the standard setup. A table for the spec-names, and a table with the house_id and the specification_id.

But now, I need to add these to the search function. So someone can find a house with Swimming pool AND a balcony.

I'm sure there is a solution, but I just don't know where to put it in the code, and how.. Google just get's me to pages like it, but not to pages explaining exactly this.

This is what my params look like:

Parameters: {"dateto"=>"", "commit"=>"ZOEKEN!", "pricefrom"=>"", "location"=>"", "province_id"=>"4", "rooms"=>"", "datefrom"=>"", "surface"=>"125", "utf8"=>"✓", "priceto"=>"", "filters"=>{"by_specifications"=>["2", "5", "10"]}, "house_category_id"=>""}

Hope someone can help, if something is unclear please let me know.

Thanks

EDIT: Oke, I've got it to work! Thanks very much!

There's only one little problem: A house shows up if either one of the specs exists for the house.. So it's an OR-OR query, but what I want is AND-AND..

So if a user searches for balcony and garage, the house must only show up if both of these exist for the house.. What I did now is this: for each specification searched, a query is being made.. (code is below)

I'm wondering, is this the right way to go? Because it works, but I get double matches.. ( I filter them using "uniq" ) The problem with uniq is that I can't get "will_paginate" to work with it..

This is my final code开发者_如何学Python:

def index
  ActiveRecord::Base.include_root_in_json = false

  # I'm creating conditions with a function I made, PM me for the code..
  conditions = createConditions( createParameters( ) );

  query = House.includes( :open_houses ).where( conditions )

  unless params[:specifications].blank?
    query = query.joins( :house_specifications )
    query = query.group( 'open_houses.id' )
    query = query.where( 'house_specifications.specification_id' => params[:specifications] )
    query = query.order( 'count(open_houses.id) DESC' )
    # query = query.having( 'count(open_houses.id) = ?', [params[:specifications].length.to_s] )
  end

  query = query.order( (params[:sort].blank?)? "open_houses.date ASC, open_houses.from ASC" : params[:sort] )

  if params[:view] == "list"
    page = params[:page] unless params[:page].blank?
    @houses = query.all.uniq.paginate( :page => page || "1", :per_page => 5 )
  else
    @houses = query.all.uniq
  end

  respond_to do |format|
    format.html
    format.js
  end
end

Thanks for the help, really appreciate it!

---

Try this (Rails 3):

House.joins(:houses_specifications).
      where('houses_specifications.specification_id' => params[:by_specifications]).
      where(...).all

You can have a bunch of if's and case's to add filters, group_by's, limits etc before you run that final .all on the query.

house_query = House.where(:pink => true)
unless params[:by_specifications].blank?
  house_query = house_query.joins(:houses_specifications).
                            where('houses_specifications.specification_id' => params[:by_specifications])
end
...
@houses = house_query.all

Edit: New and improved version that doesn't query directly on the join table for readability, group_by for distinctness and does an intersect to get houses with all specs.

query = House.joins(:open_houses).where(conditions)

unless params[:specifications].blank?
  query = query.joins(:specifications).
                group('houses.id').
                where(:specifications => params[:specifications]).
                having('count(*)=?', params[:specifications].count)
end

if params[:view] == "list"
  @houses = query.all.paginate(:page => params[:page])
else
  @houses = query.all
end

You can change the "having" to an "order" to get those with the most matching specs first.