Advanced? ActiveRecord/SQL question

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

–
Rick DeNatale

My blog on Ruby
http://talklikeaduck.denhaven2.com/

2

On 10/16/07, Rick DeNatale [email protected] wrote:

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.

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

On 10/16/07, Bob S. [email protected] wrote:

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

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).

Rick DeNatale

My blog on Ruby
http://talklikeaduck.denhaven2.com/

5

On 16 Oct 2007, at 16:20, Rick DeNatale wrote:

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

6

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

7

On 10/16/07, Rick DeNatale [email protected] wrote:

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.

“Pay no attention to that man behind the curtain!”

:~)

8

On 10/16/07, Michael G. [email protected] wrote:

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?

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.

–
Rick DeNatale

My blog on Ruby
http://talklikeaduck.denhaven2.com/

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.

Rick DeNatale

My blog on Ruby
http://talklikeaduck.denhaven2.com/

10

On 17 Oct 2007, at 16:37, Rick DeNatale wrote:

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’]

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])

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…)