Advanced? ActiveRecord/SQL question.

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?

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

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.

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

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

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

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.

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

:~)

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.

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

Fred

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

--Michael

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