# Help with ActiveRecord joins

**URL:** <https://www.ruby-forum.com/t/help-with-activerecord-joins/91491>\
**Category:** Rails\
**Created:** [April 9, 2007, 7:05pm UTC](https://www.ruby-forum.com/t/help-with-activerecord-joins/91491 "2007-04-09T19:05:17Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![BallS\_Donald\_A\_Jr\_S](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/da6949/32.png) [@BallS\_Donald\_A\_Jr\_S](https://www.ruby-forum.com/u/BallS_Donald_A_Jr_S)\
**Post date:** [April 9, 2007, 7:05pm UTC](https://www.ruby-forum.com/t/help-with-activerecord-joins/91491/1 "2007-04-09T19:05:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![BallS\_Donald\_A\_Jr\_S](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/da6949/32.png) [@BallS\_Donald\_A\_Jr\_S](https://www.ruby-forum.com/u/BallS_Donald_A_Jr_S)\
**Post date:** [April 10, 2007, 12:21am UTC](https://www.ruby-forum.com/t/help-with-activerecord-joins/91491/2 "2007-04-10T00:21:04Z")

</div>

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