Hey all,
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:
venues_sorted_by_fans = Venue.find(:all, :include => :fans, :order =>
‘COUNT ???’)
What do I COUNT by in the SQL part? I admit I’m no SQL expert, but I
have tried searching for the proper answer. Hopefully someone can help
me here.
Thanks!
Chad
you were almost there
venues_sorted_by_fans = Venue.find(:all, :include => :fans, :order =>
‘COUNT
DESC’)
to order by count descending
I’m sorry, I actually meant my models are as such:
model Venue HABTM Fans
model Fans HABTM Venues
Hmmm that won’t sort by the count of fans that are associated with
each venue…
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.
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.
I would do:
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…
YES! Perfect! That’s exactly what I needed. Thank you SO MUCH!
Chad
On May 20, 6:33 pm, Sean T Allen [email protected] wrote:
I do it all the time. Not sure which mysql might have introduced that
but it is there.
At least MySQL 5.0 because it doesn’t work in 4.1
On 5/20/07, Chad W [email protected] wrote:
So, using eager loading:
Why don’t you create a new model for the relationship, and then cache
the size of fans.
Then you could do something like
EventBooking.find :all, :include => :fans, :order => “fans_count”
EventBooking would just have id, venue_id, fan_id, and fans_count.
Venue use has_many :through to transparently have access to fans.
Pat
Or rubyize it and do
Venue.find(:all, :include => :fans).sort{|x,y| x.fans.count <=>
y.fans.count}
On May 20, 6:30 pm, Sean T Allen [email protected] wrote:
I would do:
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…
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.
That won’t work with a limit.
On May 20, 8:14 pm, Keynan P. [email protected]