Let's say I have 2 models:
model Venue has many Fans
model Fans has many Venues
In my app, Fans can bookmark their favorite Venues.
Le's say I want to list the venues, sorted by the number of Fans that
has bookmarked them, in descending order.
So, using eager loading:
I don't believe this can be done in a single query with ActiveRecord
(at least in MySQL). The set of options you want would look something
like this.
:joins => "LEFT JOIN fans AS fans_to_count ON venues.id =
fans_to_count.venue_id"
:group => "fans_to_count.venue_id"
:order => "COUNT(fans_to_count.id)"
Unfortunately this does not work because the COUNT(fans_to_count.id)
is an invalid ORDER BY clause (at least in MySQL). The reason is
because the group functions such as COUNT must be in the SELECT
clause, not the ORDER clause. But when you are using eager loading
(:include), ActiveRecord sets up the SELECT clause explicitly and
doesn't use an :select option you pass. There is currently no way to
add an additional SELECT component which is all that would be
necessary.
Instead you will have to do two queries. First you group the IDs of
the venues you want with something like:
venues = Venue.find(:all,
:joins => "LEFT JOIN fans AS fans_to_count ON venues.id =
fans_to_count.venue_id",
:group => "fans_to_count.venue_id",
:select => "id,COUNT(fans_to_count.id) AS count",
:order => "count DESC")
ids = venues.map(&:id)
Then you do your original query with
:conditions => {:id => ids} # AR automatically converts an array of
ids to IN(1,2,3,4) SQL syntax.
They won't come back in order, so you'll have to sort in Ruby. But
that will still be faster than the n+1 queries necessary without eager
loading. Ugly, I know, but ActiveRecord would need some hacks to get
around this. Now that I think about it, I might make a plugin that
lets you do something like :additional_select that would let you get
around this.
Venue.find_by_sql( "SELECT v.* FROM venues v, fans_venues fv WHERE v.id=fv.venue_id GROUP BY v.venue_id ORDER BY count( distinct fv.fan_id ) DESC" );
because i have no idea how to do it w/ the standard find. you could modify from that to take your :include => :fans into account but off the top of my head i don't remember what that sequel looks like...
Unfortunately this does not work because the COUNT(fans_to_count.id)
is an invalid ORDER BY clause (at least in MySQL). The reason is
because the group functions such as COUNT must be in the SELECT
clause, not the ORDER clause. But when you are using eager loading
(:include), ActiveRecord sets up the SELECT clause explicitly and
doesn't use an :select option you pass. There is currently no way to
add an additional SELECT component which is all that would be
necessary.
Not true. You can have the count in the order clause with mysql.
I do it all the time. Not sure which mysql might have introduced that but it is there.
To convert to the standard find you just split it up into it's
appropriate pieces in the
options, :select, :joins, :conditions, :group and :order. However the
whole point is use eager loading, and so the GROUP BY and ORDER
clauses need to be structured to not clobber the eager loaded rows as
this GROUP BY statement will do.
There is no way to replicate the eager loading code manually. It
aliases tables as t0, t1, etc and columns as t0_r0, t0_r1 to avoid
name collisions, and as far as I know there is no way to hook directly
the model building process. That means you are stuck with
whatever :select Rails generates for you. You also can't group on the
joined table from eager loading because that would destroy the eager
loading itself. So you must add your own explicit join with it's own
table name to perform the group and count.
The original options I posted are what you need, but as mentioned in
my other message, you will need at least MySQL 5 or it will blow up.