# Order by number of occurances in a has\_many relationship

**URL:** <https://www.ruby-forum.com/t/order-by-number-of-occurances-in-a-has-many-relationship/162407>\
**Category:** Rails\
**Created:** [March 9, 2009, 5:42pm UTC](https://www.ruby-forum.com/t/order-by-number-of-occurances-in-a-has-many-relationship/162407 "2009-03-09T17:42:35Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![rhubarbcrumble](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ba9def/32.png) [@rhubarbcrumble](https://www.ruby-forum.com/u/rhubarbcrumble)\
**Post date:** [March 9, 2009, 5:42pm UTC](https://www.ruby-forum.com/t/order-by-number-of-occurances-in-a-has-many-relationship/162407/1 "2009-03-09T17:42:35Z")

</div>

I can’t seem to find anything to explain how I might be able to order a  
list according to the number of occurrences of an item in a has\_many  
relationship, so maybe someone could help me learn how…

To illustrate, imagine I have the following situation:

I have one table with details for a number of people, and one table of  
items. Each item belongs to one person and each person can have may  
items.

In my view I have something like this:

\<% for person in @people %\>

 \<%=h person.name %\> \<%=h person.items.count %\> \<% end %\>

I can get the list to order by the id/name etc. of the people, but I  
really want to order it by the number of items belonging to each person,  
something like so:

@people = Person.find(:all, :order =\> ‘person.items.count desc’)

except that this brings up an ‘unknown column’ error.

Any ideas for getting around this?

---

<div class="post-metadata">

**Author:** ![rhubarbcrumble](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ba9def/32.png) [@rhubarbcrumble](https://www.ruby-forum.com/u/rhubarbcrumble)\
**Post date:** [March 9, 2009, 6:03pm UTC](https://www.ruby-forum.com/t/order-by-number-of-occurances-in-a-has-many-relationship/162407/2 "2009-03-09T18:03:26Z")

</div>

On Mon, Mar 9, 2009 at 11:42 AM, Sandy T.  
[removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> @people = Person.find(:all, :order =\> ‘person.items.count desc’)
> 
> except that this brings up an ‘unknown column’ error.
> 
> Any ideas for getting around this?

@people.sort!{ |a,b| a.items.count \<=\> b.items.count }

–  
Greg D.  
[http://destiney.com/](http://destiney.com/)

---

<div class="post-metadata">

**Author:** ![rhubarbcrumble](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ba9def/32.png) [@rhubarbcrumble](https://www.ruby-forum.com/u/rhubarbcrumble)\
**Post date:** [March 9, 2009, 7:00pm UTC](https://www.ruby-forum.com/t/order-by-number-of-occurances-in-a-has-many-relationship/162407/3 "2009-03-09T19:00:34Z")

</div>

Greg D. wrote:

> On Mon, Mar 9, 2009 at 11:42 AM, Sandy T.  
> [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> 
> > @people = Person.find(:all, :order =\> ‘person.items.count desc’)
> > 
> > except that this brings up an ‘unknown column’ error.
> > 
> > Any ideas for getting around this?
> 
> @people.sort!{ |a,b| a.items.count \<=\> b.items.count }
> 
> –  
> Greg D.  
> [http://destiney.com/](http://destiney.com/)

Cheers for that Greg, it works fine but is a little heavy on the  
database (predictable I guess). However, this has steered me towards  
creating a counter cache in the people table, which I hadn’t considered  
before and which cuts down hugely on the number of SQL queries.

Thanks!