Rails - Aug 2007 - :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
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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

<tr>  <td>s.date</td>  <td>s.buyer.name</td>  
<td>s.vendor.name</td>  [...]
<td>s.sale_status</td>  </tr>

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.


On 8/29/07, Florian Aßmann <florian.assmann-htSm2yLGOjU@public.gmane.org>
wrote:
>
>
> 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
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---