:order based on an aggregate value?

Rails
1

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

2

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

3

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