Find and order by association's associated counter_cache

Question

I have a number of associated models:

Company has_many :clients (which belongs_to :company)  
Client has_many :groups (which belongs_to :client)
Group has_many :orders (which belongs_to :group, :counter_cache => true)

Now, I need to find all companies and sort them by their orders, highest to lowest. Since I have the groups orders_count column, I am pretty sure I can just order by groups.orders_count. Then I need to iterate over the companies, showing the total orders placed alongside.

I've been trying to come up with a find for this. I have a MySQL database, if it comes down to a find_by_sql or something.

companies = Company.find(
  :all,
  :include => { :clients => :groups },
  :select => "companies.*, groups.orders_count",
  :group => "companies.id",
  :order => "groups.orders_count"
)

Any help or push in the right direction appreciated! Thanks.

Answer 1

Okay, this took me way too long to figure out. I need to pick up a book on SQL ASAP. At any rate, this was what I wanted:

sql = "groups.created_at BETWEEN :start_date AND :end_date"
options = { :start_date => 1.month.ago, :end_date => Date.today }

@companies = Company.all(
  :select => 'companies.*, count(orders.id) as counter',
  :joins => { :clients => { :groups => :orders } },
  :group => 'companies.id',
  :order => 'counter DESC',
  :conditions => [sql, options]
)

The :joins makes counter work as a column_name for company; why :includes does not, I don't know, and if someone has a nice answer I would love to learn why. So I can write:

@companies.first.counter

And get back the total number of orders for that company.

EDIT

Found out I was getting almost the results I wanted, except for the clients with no groups/orders in a date range. Every result had at least 1 for counter.

Realized that rails joins uses INNER JOIN; LEFT JOIN will return objects that don't have corresponding children. So I changed to this:

@companies = Company.all(
  :select => 'companies.*, count(orders.id) as counter',
  :joins => "LEFT JOIN clients ON clients.company_id = companies.id
    LEFT JOIN groups ON groups.client_id = clients.id LEFT JOIN orders ON orders.group_id = groups.id",
  :group => 'companies.id',
  :order => order_by,
  :conditions => [sql, options]
)

I also am remembering now that :includes eager loads (doesn't necessarily do a join), which is probably why I couldn't use that. This might be different in Rails 3. See here: Rails :include vs. :joins