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.

score 0 · Answer 1 · answered Sep 12 '12 at 19:19

0

Using groups.orders_count could be an issue. If a company two clients and each client has one group, which group.orders_count are you sorting on? Correct me if I'm wrong there.

This doesn't take advantage of counter_cache but it should work (my Rails 2.x is a bit rusty):

companies = Company.all(
  :select => "companies.*",
  :include => { :clients =>  [ :groups => :orders ] },
  :group => "companies.id",
  :order => "count(orders.*) DESC")

answered Sep 12 '12 at 19:19

Tom L

3,389
1
16
14

Ah, I see, ordering like that makes sense (although I believe it needs to be count(orders.id) DESC). But, how can I now get to that count for the companies, without iterating through each company and just manually making a hash? – d3vkit Sep 12 '12 at 20:50

score 0 · Accepted Answer · edited May 23 '17 at 12:03

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

Find and order by association's associated counter_cache

2 Answers2