Rails - May 2007 - Model.find(:all, :include => :children, :order => "COUNT(children)")???

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 received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

you were almost there

venues_sorted_by_fans = Venue.find(:all, :include => :fans, :order =>
''COUNT
DESC'')

to order by count descending



On 5/21/07, Chad W <superwick-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
>
> 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 received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

I''m sorry, I actually meant my models are as such:
model Venue HABTM Fans
model Fans HABTM Venues

On May 20, 7:50 pm, Chad W
<superw...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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 received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Hmmm that won''t sort by the count of fans that are associated with
each venue....

On May 20, 7:56 pm, "Dion Hewson"
<dionhew...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> you were almost there
>
> venues_sorted_by_fans = Venue.find(:all, :include => :fans, :order =>
''COUNT
> DESC'')
>
> to order by count descending
>
> On 5/21/07, Chad W
<superw...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>
>
> > 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 received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.

On May 20, 5:50 pm, Chad W
<superw...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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 received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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


Chad W wrote:
> 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 received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
> For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
> -~----------~----~----~----~------~----~------~--~---
>
>

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

YES! Perfect! That''s exactly what I needed. Thank you SO MUCH!

Chad

On May 20, 8:30 pm, Sean T Allen
<s...-j6/ZtAoqnBr6dDqEjws1Cg@public.gmane.org>
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...
>
> Chad W wrote:
> > 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
>
> > >
>
>
>  smime.p7s
> 5KDownload

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On May 20, 6:33 pm, Sean T Allen
<s...-j6/ZtAoqnBr6dDqEjws1Cg@public.gmane.org>
wrote:
> > 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.
At least MySQL 5.0 because it doesn''t work in 4.1


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On May 20, 6:30 pm, Sean T Allen
<s...-j6/ZtAoqnBr6dDqEjws1Cg@public.gmane.org>
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.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5/20/07, Chad W <superwick-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> 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
>
>
> >
>
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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Or rubyize it and do

Venue.find(:all, :include => :fans).sort{|x,y| x.fans.count <=> 
y.fans.count}

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

That won''t work with a limit.

On May 20, 8:14 pm, Keynan Pratt
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> Or rubyize it and do
>
> Venue.find(:all, :include => :fans).sort{|x,y| x.fans.count <=>
> y.fans.count}
>
> --
> Posted viahttp://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---