Forum: Ruby on Rails HABTM: Find sorted by number of associations

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Dbfdb3647a1496ade767cb649d88afc4?d=identicon&s=25 Ben Lee (Guest)
on 2006-05-03 19:11
(Received via mailing list)
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
7c4087d053eb02d099a17d91ba5e33b5?d=identicon&s=25 Brian Hughes (Guest)
on 2006-08-03 14:22
(Received via mailing list)
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/...
ClassMethods.html#M000532> for more info...

-Brian
9f0f89bbd9e1ecfbaab6584e429b7a2f?d=identicon&s=25 Josh Susser (jsusser)
on 2006-08-03 18:06
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/...
> 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...

--
Josh Susser
http://blog.hasmanythrough.com
This topic is locked and can not be replied to.