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

Brian H. 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. :wink: 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 S.
http://blog.hasmanythrough.com

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs