:order based on an aggregate value?

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

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,

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'}"


class Sale < ActiveRecord::Base
  belongs_to :referral, :counter_cache => true

2. (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.



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

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](http://s.buyer.name) [s.vendor.name](http://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,

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.