Rails, finding object with multiple dates in has_many assocation

I'm pretty new to Rails, and the problem I'm bumping into is described as followes:

First, there is a table with houses. The houses have multiple dates associated with h开发者_JAVA百科as_many :dates

Second there is the table with dates. Dates have the association belongs_to :house

Now, in a search, a user can select a start and end-date. And what I want to do, is search each house for it's dates, and check if these are between the start and end-date. If one or more of the dates is within the start/end date, it needs to be found.

These are my conditions to search a house for other criteria:

parameters.push( [ params[:house_category_id], "houses.house_category_id = ?" ] );
parameters.push( [ params[:province_id], "houses.province_id = ?" ] );

I've created a method to create valid conditions with these parameters.

Please tell me, if it's not clear enough, cause it's a little hard to explain.

I've tried searching for this, but it got me on all kind of other pages except the pages with an answer..

Thanks

Not sure if I understand your problem correctly, but according to what I understood, you can get the desired result using the following:

res = House.find(:all, :select => 'distinct houses.*', :joins => "INNER JOIN dates ON dates.house_id = houses.id", :conditions => "dates.date < '2011-01-12' AND dates.date > '2011-01-11'")

where '2011-01-12' & '2011-01-11' are the dates selected by the user.

Let me know if this is what you were looking for or post with more details.

Now another question pops in my mind, but I don't know if that is possible.. If you forget about the from-to for this situation, is there any possibility that a I can check this sort of associations like so:

A house has 2 dates attached.

A User selects 2 dates.

Now I only want to select the house if these 2 dates both correspond..

Like this:

@houses = House.find(:all, :select => 'distinct houses.*', :joins => "INNER JOIN dates ON dates.house_id = houses.id", :conditions => "dates.date = '2011-01-12' AND dates.date = '2011-01-11'")

I tried the code above and got an SQL like this:

SELECT distinct houses.* FROM `houses` INNER JOIN dates ON dates.house_id = houses.id WHERE (dates.date = '2011-01-15' AND dates.date = '2011-01-22')

But it doesn't seem to work, is this possible at all in a single query or single find( )?