help with ActiveRecord joins

rubyonrails-talk
1

I'm working on a fairly complex join query and could use a bit of advice from more expert users of ActiveRecord.

I have an events table with quite a lot of data, with has_many relations to categories, age_groups, and event_images. In my first naive implementation, each event had a date, so to load all upcoming events efficiently, I'd do:

Event.find(:all, :conditions=>['start_date >= ?', Date.today], :includes=[:categories, :age_groups, :event_images])

This works well, and returns all data in one query which is good, but is perhaps inefficient in that the amount of data in the events table dwarfs that in the categories, age_groups, and event_images tables. It would perhaps be better to load all of the events in one query, then load all of the categories for all of the events in another query, then another for age_groups, and another for event_images. It's certainly easy enough to just let that happen automatically for each event individually, but then I'm looking at 3*n + 1 queries, rather than the 4 queries I think would be optimal. Is there a way to do eager loading of associations for a collection of AR objects all at once?

In addition, this naive approach to event date modeling is inadequate, as events may occur on several specific dates, or on recurring dates. I've implemented this by adding an occurrences table, which lists specific dates for an event, and with a recurrences table, which specifies the rules by which an event recurs. I then have a table which simply enumerates dates up to an event horizon, and I get all events on dates in whatever range I'm looking at with a query like:

SELECT events.*, library_dates.start_date FROM library_dates LEFT OUTER JOIN events ON 1=1 WHERE events.id IN (SELECT event_id FROM recurrences WHERE monthday IS NOT NULL AND DATEPART(dd, start_date) = monthday OR   (monthday IS NULL AND weekday IS NOT NULL AND DATEPART(dw, start_date) = weekday AND     (monthweek IS NULL OR datepart(dd, start_date) BETWEEN 7*(monthweek-1) + 1 AND 7*(monthweek-1) + 7)   )) OR events.id IN (SELECT event_id FROM occurrences WHERE startdate = start_date)

(the DATEPART function is sqlserver-specific, works like DAYOFWEEK() and DAYOFMONTH() in mysql)

I'm wondering the best way to use this query in ActiveRecord. Would it be better to write an Event.find_by_daterange method that uses find with a manual :joins options, or using find_by_sql, or create view which encapsulates this query and do queries on a corresponding model object?

Thanks for any advice.

- donald

2

No replies yet, reckon everyone's still on break or my question is simply too stupid and/or arcane, but just in case anyone's interested, I came up with an elegant solution to the second part of my question at least:

In addition, this naive approach to event date modeling is inadequate, as events may occur on several specific dates, or on recurring dates. I've implemented this by adding an occurrences table, which lists specific dates for an event, and with a recurrences table, which specifies the rules by which an event recurs. I then have a table which simply enumerates dates up to an event horizon, and I get all events on dates in whatever range I'm looking at with a query like:

SELECT events.*, library_dates.start_date FROM library_dates LEFT OUTER JOIN events ON 1=1 WHERE events.id IN (SELECT event_id FROM recurrences WHERE monthday IS NOT NULL AND DATEPART(dd, start_date) = monthday OR   (monthday IS NULL AND weekday IS NOT NULL AND DATEPART(dw, start_date) = weekday AND     (monthweek IS NULL OR datepart(dd, start_date) BETWEEN 7*(monthweek-1) + 1 AND 7*(monthweek-1) + 7)   )) OR events.id IN (SELECT event_id FROM occurrences WHERE startdate = start_date)

(the DATEPART function is sqlserver-specific, works like DAYOFWEEK() and DAYOFMONTH() in mysql)

I'm wondering the best way to use this query in ActiveRecord. Would it be better to write an Event.find_by_daterange method that uses find with a manual :joins options, or using find_by_sql, or create view which encapsulates this query and do queries on a corresponding model object?

The elegant solution is to create an ActiveRecord model object for library_dates, and to encapsulate the icky query above into a view which contains two columns, library_date_id and event_id. If I, as should have been obvious from the outset, name the view events_library_dates, suddenly I can use habtm on the LibraryDate and Event objects! Now, if I want to get a list of all upcoming events broken down by date, and go ahead and include the categories, age_groups, etc., I can just do:

LibraryDate.find(:all, :includes=>{:events=>[:categories, :age_groups, :event_images]}, :conditions=>'events.id IS NOT NULL')

I'm still unnecessarily transferring a bunch of data from the sql server, and am thus interested in any ideas for the first part of my question, but I'm quite pleased at the clarity and performance of this solution.

- donald