# HABTM: Find sorted by number of associations

**URL:** <https://www.ruby-forum.com/t/habtm-find-sorted-by-number-of-associations/59510>\
**Category:** Rails\
**Created:** [May 3, 2006, 7:11pm UTC](https://www.ruby-forum.com/t/habtm-find-sorted-by-number-of-associations/59510 "2006-05-03T19:11:03Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![blewis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/c57346/32.png) [@blewis](https://www.ruby-forum.com/u/blewis)\
**Post date:** [May 3, 2006, 7:11pm UTC](https://www.ruby-forum.com/t/habtm-find-sorted-by-number-of-associations/59510/1 "2006-05-03T19:11:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![blewis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/c57346/32.png) [@blewis](https://www.ruby-forum.com/u/blewis)\
**Post date:** [August 3, 2006, 2:22pm UTC](https://www.ruby-forum.com/t/habtm-find-sorted-by-number-of-associations/59510/2 "2006-08-03T14:22:07Z")

</div>

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/](http://rubyonrails.com/rails/classes/ActiveRecord/Associations/)  
ClassMethods.html#M000532\> for more info…

-Brian

---

<div class="post-metadata">

**Author:** ![blewis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/c57346/32.png) [@blewis](https://www.ruby-forum.com/u/blewis)\
**Post date:** [August 3, 2006, 6:06pm UTC](https://www.ruby-forum.com/t/habtm-find-sorted-by-number-of-associations/59510/3 "2006-08-03T18:06:19Z")

</div>

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. 😉 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/](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](http://blog.hasmanythrough.com/articles/2006/06/12/when-associations-arent-enough-part-2)

–  
Josh S.  
[http://blog.hasmanythrough.com](http://blog.hasmanythrough.com)