rails, activerecord sum then order

i have a Job model that belongs_to User, and User has_many jobs. I want to create an AR query that calculates the total number of work days per user, then orders in Descending order.

I have this so far, but is giving me an error: (column "Job.id" must appear in the GROUP BY clause or be used in an aggregate function)

@work_days = Job.group(:user).order('SUM(total_days)')

I can't seem to get the .order method to work - is there something I am missing? Thanks in advance!

Answers


You could write your query :-

Job.group(:user_id).select('SUM(total_days) as tot').order('tot desc')

With slight variation from earlier

Job.group(:user_id).order('sum_total_days DESC').sum(:total_days)

The existing answers didnt work for me, I had to edit the group method slightly. If I was using your models (i.e. user) I would have needed to list every single user attribute in the group method.

i.e. something like:

user_attrs = User.new.attributes.keys.map { |key| "users.#{key}" }.join(",")
 Job.group(user_attrs).order('sum_total_days DESC').sum(:total_days)

I don't know if this would actually work in your situation but having spent an annoying amount of time on this issue I thought I would share what worked for me.


Need Your Help

Design of a high-performance sorted data structure read by many threads and written by few

multithreading algorithm delphi data-structures thread-safety

I have an interesting data structure design problem that is beyond my current expertise. I'm seeking data structure or algorithm answers about tackling this problem.

Here route API - Calculating toll cost

here-api

I am trying to calculate the toll cost between two points in Australia.