# :order based on an aggregate value?

**URL:** <https://www.ruby-forum.com/t/order-based-on-an-aggregate-value/113329>\
**Category:** Rails\
**Created:** [August 29, 2007, 10:29pm UTC](https://www.ruby-forum.com/t/order-based-on-an-aggregate-value/113329 "2007-08-29T22:29:08Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![jacob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f1d935/32.png) [@jacob](https://www.ruby-forum.com/u/jacob)\
**Post date:** [August 29, 2007, 10:29pm UTC](https://www.ruby-forum.com/t/order-based-on-an-aggregate-value/113329/1 "2007-08-29T22:29:08Z")

</div>

Hi all,

I’m building an app where each :referral has\_many :sales.

In a list of all referrals, I’m displaying referral.date,  
referral.other\_details, and also referral.sales.count. I want to able  
to sort this list by that last column – the number of sales per  
referral.

Abstractly, I want to do something like  
@referrals=Referral.find(:all, :order=\>sales.count\_all). But nothing  
like this works, because when this initial SQL statement is executed,  
the total sales per referral haven’t yet been calculated. They get  
calculated afterwards, in n separate queries.

How to proceed? @referrals=Referral.find(:all, :include=\>:sales)  
doesn’t really help. That would allow me to order by a column in the  
sales table, but it doesn’t get me any closer to being able to sort by  
the aggregated number of sales per referral.

Any thoughts on how to do this? I think I can write the SQL to do it  
manually - but can it be accomplished without dropping to find\_by\_sql?

Thanks much,  
Jacob

---

<div class="post-metadata">

**Author:** ![jacob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f1d935/32.png) [@jacob](https://www.ruby-forum.com/u/jacob)\
**Post date:** [August 30, 2007, 2:01am UTC](https://www.ruby-forum.com/t/order-based-on-an-aggregate-value/113329/2 "2007-08-30T02:01:54Z")

</div>

Hi Jacob,

1. (better) solution:  
class Referral \< ActiveRecord::Base  
has\_many :sales

def self.ordered\_by\_sales\_count(asc = true)  
find :all, :order =\> “sales\_count #{asc ? ‘ASC’ : ‘DESC’}”  
end

end

class Sale \< ActiveRecord::Base  
belongs\_to :referral, :counter\_cache =\> true  
end

1. (untested) solution:  
Referral.find :all, :include =\> :sales,  
:order =\> ‘count(sales.referral\_id)’

The 2. solution is what I’d first try when I’m in SQL learning mode.  
In order to get solution #1 to work you need to create a column called  
sales\_count in the referrals table.

Regards  
Florian

---

<div class="post-metadata">

**Author:** ![jacob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f1d935/32.png) [@jacob](https://www.ruby-forum.com/u/jacob)\
**Post date:** [September 2, 2007, 3:55pm UTC](https://www.ruby-forum.com/t/order-based-on-an-aggregate-value/113329/3 "2007-09-02T15:55:09Z")

</div>

Florian,

Thanks for pointing me to counter\_cache and friends. I had contemplated  
implementing this manually and did not realize Rails has support for  
this  
built in. I’m now going to add approx 15 counter\_caches across various  
models. 🙂

But what if I want to sort by a piece of data that is produced  
on-the-fly  
via a method call? For example, there’s a “sale status” method which  
evaluates some logic and returns (for example) “green”, “red”, or  
“blue”. In  
my view, I’m interating over @sales.each do |s| :

 s.date s.buyer.name s.vendor.name [...] s.sale\_status 

Since sale\_status isn’t stored in the DB, i can’t think of any way to  
sort  
the list by sale\_status using :order.

Can anyone suggest how this might work, or how they’ve solved similar  
problems? All I can think of is that I may need to switch to client-side  
sorting to order by values that aren’t stored in the DB. If so, any  
suggestions on the best framework/plugin/strategy for client-side  
sorting?

Thanks again,  
Jacob

p.s. Florian, I don’t think your solution #2 will work – the SQL would  
need  
a group by clause to count up the sales per referral. And of course I  
can’t  
do a group by, because I need the query to return one row per sale. I  
think  
the raw SQL solution would be a subquery to do the counting, joined back  
to  
the main query by sale\_id.