Advanced? ActiveRecord/SQL question.

rubyonrails-talk
1

I asked this in a different form some weeks ago and got no response, let me try again in hopes that I can make my question clearer.

Let's say I have the following models

class Author < ActiveRecord::Base    has_many :signings end

class Signing < ActiveRecord::Base   belongs_to: :author   # Has an attribute when, which is a DateTime end

Now what I want to do is find all authors who have at least one signing scheduled on or after a particular date. In other words, I want to exclude authors who won't be signing on or after that date.

Since I don't have a black belt in SQL, I'm not sure how to do this.

I think I want some kind of join of authors to signings, and a query which groups by author and a where clause involving the maximum value of where for each signing in the group. But I'm having a hard time visualizing the actual SQL for this.

And for extra points, is there a way to do this with find, :include =>, :conditions, rather than find_by_sql?

2

results = Author.find :all,   :include => :signings,   :conditions => ['signings.when >= ?', '2007-10-01']

3

Try something along the lines of:

Author.find(:all, :include => [:signings], :conditions => ["signing.when > ?", Date.today])

I'm not 100% sure of the syntax of the condition string, but I'm pretty sure thats right.

4

Thanks that SEEMS to work although I'm not sure exactly why. This generates SQL:

SELECT authors.`id` AS t0_r0, authors.`name` AS t0_r1, signings.`when` AS t1_r2, signings.`location` AS t1_r3 FROM authors LEFT OUTER JOIN signings ON signings.author_id = author.id WHERE (signings.begin_date >= '2007-10-16 10:45:46')

As I understand it the left outer join produces a reiation which would have multiple rows for the same author. So why don't I get multiple copies of authors. (Admittedly tis is an intentionally naive question, but I'm curious).

5

You will get multiples. From what I see, you should get one per signing that occurs after that date. It's possible the rails code is internally making them unique?

--Michael

6

Thanks that SEEMS to work although I'm not sure exactly why. This generates SQL:

SELECT authors.`id` AS t0_r0, authors.`name` AS t0_r1, signings.`when` AS t1_r2, signings.`location` AS t1_r3 FROM authors LEFT OUTER JOIN signings ON signings.author_id = author.id WHERE (signings.begin_date >= '2007-10-16 10:45:46')

As I understand it the left outer join produces a reiation which would have multiple rows for the same author. So why don't I get multiple copies of authors. (Admittedly tis is an intentionally naive question, but I'm curious).

short answer: becase the rails code sorts that out for you
(or :include would be pretty useless). Long answer: read associations.rb

Fred

7

Yes, I looked at the ActiveRecord code and this is what it seems to be doing.

When you do a find involving associations (e.g. the find is scoped or has the :include option), then AR instantiates an internal class called JoinDependency, it then does the query and calls the instantiate method on the JoinDependency.

This builds a hash table keyed by the primary key of the base table of the join, and uses this to instantiate one and only one AR object for each id. It then pushes the appropriate other objects to the base objects association collections from each row of the result.

8

"Pay no attention to that man behind the curtain!"

:~)

9

I've been looking at this a bit more, and it raises a concern.

While

results = Author.find :all, :include => :signings, :conditions => ['signings.when >= ?', '2007-10-01']

works, it instantiates an AR object for each signing, effectively pre-loading the signings association for each author. Many times this is what's wanted

However, since my example is really an analog for a more complex situtation, what if there are LOTS of signings, and they are fairly heavy-weight objects. The use case here is that I want to present a list of 'Authors' filtered by those who actually have a 'signing' to attend, and then when an author is selected to present his/her signings for selection.

Is there an easy way to do the search to find all of the 'authors' but leave instantiating the signings association collection until it's needed?

I tried (Item is the real name for the Author analog, and Schedules for signing: Item.find(:all, :joins => ' LEFT OUTER JOIN schedules ON schedules.item_id', :conditions => ['schedules.begin_date > ?', Time.now])

But that returns 26520 Item objects while:

Item.find(:all).length => 34

The problem here is that one AR object is being instantiated for each row returned by the outer join.

I imagine that it's an enhancement request to be able to have my cake and eat it too, i.e. to be able to 'include' :schedules for purpose of the where clause, and to maintain identity for the resulting Item objects, but NOT pre-instantiate the schedules association.

Now I could of course get the expanded list and uniq it, but if I benchmark:

Item.benchmark('join') {Item.find(:all, :joins => ' LEFT OUTER JOIN

schedules ON schedules.item_id', :conditions => ['schedules.begin_date

?', Time.now]).length}

=> 26520

Item.benchmark('include') {Item.find(:all, :include => :schedules,

:conditions => ['schedules.begin_date > ?', Time.now]).length}=> 25

The log shows: join (2.27320) include (0.18201)

So the cat's already out of the bag as far as time efficiency.

10

Try Item.find :all, :select => 'items.*', :group => 'items.id', :joins => ..., :conditions => ...

Fred

11

find_by_sql? This should let you hand-craft exactly the data you want.

--Michael

12

Looks like you want a GROUP BY * and drop the signings from the output (unless you want to grab a count...)