CODEWITHSUNDEEP
mysqlruby-on-railsruby-on-rails-3activerecord
Yannis
Yannis

Reputation: 5426

ActiveRecord query: order by a sum on an included model

I'm trying to get the projects sorted by the total price of their respective items. Something like:

Project.includes(:items).order('SUM(items.price)')

With this code, ActiveRecord returns only the first project. What am I missing?

Upvotes: 4

Views: 2519

Answers (2)

Simon
Simon

Reputation: 25993

I've not tried the v3 stuff out yet, but I'd assume it would be something like

Product.joins(:items).group('products.id').order('SUM(items.price)')

Upvotes: 8

Greg Dan
Greg Dan

Reputation: 6338

Project.find_by_sql "
  select projects.* 
  from projects 
  left join (
    select project_id, sum(price) as items_sum
    from items 
    group by project_id) as sums on
  project.id = sums.project_id
  order by sums.items_sum

Above SQL should work well on most DB systems (MySQL, PostgreSQL, ...).

AR record includes is mostly used as eager loading solution. And I am not sure if you can use it that way - to order records of parent table.

Upvotes: 0

Related Questions