Rails - Apr 2006 - HABTM: Find sorted by number of associations

Hi,
  I''ve been googling and searching the forums for some time
but can''t seem to find exactly what I''m  looking for.  

Suppose User HABTM Products (for example a favorites list).  I would like to
construct a single  query for finding Products ordered by the number of
 Users that have flagged them as favorites.

Although I can construct the query using SQL a with a 
SELECT products.*, count(products_users.user_id)  FROM products 
LEFT OUTER JOIN ... GROUP BY etc.  -

I was wondering if there''s a way to do this directly 
from ActiveRecord::Base.find( ...) ? 

I can''t seem to get the fields correct for this.

Thanks,
Ben

Well, it turns out that Rails has a built in way to help you with  
this: the :counter_cache option that''s part of belongs_to. However,  
you don''t use belongs_to with HABTM, so I would take this as a sign  
that you need to remove your HABTM and turn it into a Join Model,  
using the has_many :through association. ;) That way you can add a  
Favorite model to your app, and for each belongs_to you add  
a :counter_cache =>true.

In your User and Project model you add a favorites_count int column,  
with a default of zero. That way, Rails will automatically keep track  
of how many Favorites are linked to each User and Project, giving you  
a very easy way to use that column for your sort order.

See <http://rubyonrails.com/rails/classes/ActiveRecord/Associations/ 
ClassMethods.html#M000532> for more info...

-Brian


On Apr 30, 2006, at 06:26 AM, Ben Lee wrote:
>   I''ve been googling and searching the forums for some time
> but can''t seem to find exactly what I''m  looking for.
>
> Suppose User HABTM Products (for example a favorites list).  I  
> would like to
> construct a single  query for finding Products ordered by the  
> number of
>  Users that have flagged them as favorites.
>
> Although I can construct the query using SQL a with a
> SELECT products.*, count(products_users.user_id)  FROM products
> LEFT OUTER JOIN ... GROUP BY etc.  -
>
> I was wondering if there''s a way to do this directly
> from ActiveRecord::Base.find( ...) ?
>
> I can''t seem to get the fields correct for this.
>
> Thanks,
> Ben

Brian Hughes wrote:
> Well, it turns out that Rails has a built in way to help you with
> this: the :counter_cache option that''s part of belongs_to.
However,
> you don''t use belongs_to with HABTM, so I would take this as a
sign
> that you need to remove your HABTM and turn it into a Join Model,
> using the has_many :through association. ;) That way you can add a
> Favorite model to your app, and for each belongs_to you add
> a :counter_cache =>true.
> 
> In your User and Project model you add a favorites_count int column,
> with a default of zero. That way, Rails will automatically keep track
> of how many Favorites are linked to each User and Project, giving you
> a very easy way to use that column for your sort order.
> 
> See <http://rubyonrails.com/rails/classes/ActiveRecord/Associations/
> ClassMethods.html#M000532> for more info...
If a join model is an option, you might be able to do something with a 
tweak of this query code...

http://blog.hasmanythrough.com/articles/2006/06/12/when-associations-arent-enough-part-2

--
Josh Susser
http://blog.hasmanythrough.com

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