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:
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 =
(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.