HABTM: Find sorted by number of associations


#1

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


#2

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


#3

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