Help with ActiveRecord joins

Rails
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:

events.*, library_dates.start_date
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