Order by the sum of an associations property

Question

I have a Department model with an expenses association:

class Department < ActiveRecord::Base
  has_many :expenses
end

class Expense < ActiveRecord::Base
  belongs_to :department
end

An expense has an amount property:

e = Expense.new
e.amount = 119.50

I want 2 queries now:

1. list all departments, ordered by SUM of expenses.

2. same as #1, but grouped by month i.e. jan, feb, march, ...

Answer 1

For #1, the following code will get you the department ids sorted by sum of expenses:

Expense.select('department_id, sum(amount) as total').group('department_id').order('total desc')

Here is a sample code on how to use the returned objects:

Expense.select('department_id, sum(amount) as total').group('department_id').order('total desc').each { |dep| print "Department ID: #{dep.department_id} | Total expense: #{dep.total}\n" }

This will print something like:

Department ID: 2 | Total expense: 119.50
Department ID: 1 | Total expense: 54.34
Department ID: 10 | Total expense: 23.43

For #2, you can similarly add the month grouping along with the sum:

Expense.select('department_id, extract(month from created_at) as month, sum(amount) as total').group('department_id, month').order('month asc, total desc')

Again, a sample code to demonstrate how to use it:

Expense.select('department_id, extract(month from created_at) as month, sum(amount) as total').group('department_id, month').order('month asc, total desc').each { |dep| print "Department ID: #{dep.department_id} | Month: #{dep.month} | Total expense: #{dep.total}\n" }

This will will print something like:

Department ID: 2 | Month: 1 | Total expense: 119.50
Department ID: 1 | Month: 1 | Total expense: 54.34
Department ID: 10 | Month: 1 | Total expense: 23.43
Department ID: 1 | Month: 2 | Total expense: 123.45
Department ID: 2 | Month: 2 | Total expense: 76.54
Department ID: 10 | Month: 2 | Total expense: 23.43

... and so on.

Of course, once you have the department Ids, you can use Department.find() to get the rest of information. I believe ActiveRecord does not support getting at the same time all the Department fields directly without using raw SQL.

EDIT ----

If you want to include the department fields you can either:

1 - Load them in separate queries like:

Expense.select('department_id, sum(amount) as total').group('department_id').order('total desc').each do |department_expense|
    # In department_expense you have :department_id and :total
    department = Department.find(department_expense.department_id)
    # In department now you have the rest of fields
    # Do whatever you have to do with this row of department + expense
    # Example
    print "Department #{department.name} from #{department.company}: $#{department_expense.total}"
end

Advantage: Using ActiveRecord SQL abstractions is nice and clean.
Drawback: You are doing a total of N+1 queries, where N is the number of departments, instead of a single query.

2 - Load them using raw SQL:

Department.select('*, (select sum(amount) from expenses where department_id = departments.id) as total').order('total desc').each do |department|
    # Now in department you have all department fields + :total which has the sum of expenses
    # Do whatever you have to do with this row of department + expense
    # Example
    print "Department #{department.name} from #{department.company}: $#{department.total}"
end

Advantage: You are doing a single query.
Drawback: You are losing the abstraction that ActiveRecord is providing to you from SQL.

Both will print:

Department R&D from Microsoft: $119.50
Department Finance from Yahoo: $54.34
Department Facilities from Google: $23.43