:order based on an aggregate value?

rubyonrails-talk
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

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.

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](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, 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.