Need help optimizing this database query...

Question

I was hoping I could get some help optimizing this query for a rails site:

Vacation
has_many :departures
has_many :photos

Departure
belongs_to :vacation

Photo
belongs_to :vacation

I need to find a list of vacations ordered and displayed by departure. So if a vacation has two departures, it should show up in the list twice (once for each departure).

@dates = Departure.find(:all, 
                        :order => "start_at", 
                        :include => [{:vacation => :photos}],
                        :conditions => ["vacations.duration > 1 AND start_at > ?", Time.now])

The issue is that I THEN need to collect all the future departures for each vacation, which results in a new query for each departure listed.

Any ideas on how to better accomplish this?

Answer 1

You don't have a limit on that query, so wouldn't you already have everything you need pulled into memory? You have an array with every future departure for each vacation you want to display.

So use the array you already have.

vacation_id = 3 # Or whatever - this would obviously be in a loop.
@dates_for_vacation = @dates.select{|d| d.vacation_id == vacation_id}

Answer 2

I think you can pull the data in via a query on the vacation, rather than departures.

@vacations = Vacation.find(:all, 
                    :order => "departures.start_at", 
                    :include => [:departure, :photos],
                    :conditions => ["vacations.duration > 1 AND departures.start_at > ?", Time.now])

This will find all the vacations for the time period, but also load the associated departure and photo records, reducing the query load considerably.

You may need to play with the query to get the right departure records.