CODEWITHSUNDEEP
sqlruby-on-railsactiverecordgroup-by
Bharat
Bharat

Reputation: 2459

Group Query in Rails ActiveRecord

I am trying to do a group by (sql query) using ActiveRecord in Rails on a table with the following structure:

id (integer)
user_id (integer, foreign key from the users table)
work_date (date, e.g., 2014-06-02)
computed_hours (integer, e.g., 4)
computed_minutes (integer, e.g., 30)

I want the query to return the sum of computed_hours and computed_minutes totaled for a specific user for each day of a particular month:

So, for instance, say for, user_id = 2, I would like it to return

2014-06-02 4.5 (computed_hours and computed_minutes totaled) 2014-06-05 3.25 ......

How could this be written in ActiveRecord assuming that the table above is called billables and that the corresponding model's name is Billable?

Thanks in advance for your time.

Upvotes: 0

Views: 2009

Answers (1)

Ruby Racer
Ruby Racer

Reputation: 5740

Assuming you pass user_id and month (as part of a date) as parameters, this should work:

userid      = params[:user_id]
date_start  = Date.parse(params[:date]).at_beginning_of_month - 1.days
date_end    = Date.parse(params[:date]).at_end_of_month       + 1.days

Billable.all.
         select('work_date, 
                 sum(computed_hours)   as total_hours,
                 sum(computed_minutes) as total_minutes').
         where( 'user_id = ? and (work_date > ? and work_date < ?)', 
                 userid, date_start, date_end).
         group( :work_date)

Then, for each @billable of the resulting rows you'll have:

@billable.user_id
@billable.total_hours
@billable.total_minutes

UPDATE:

In the likely event that you are using Postgres, this will produce an unthinkable error which you can easily override.

The resulting SQL will try to order the query by "billable"."id" which will result in Postgres requiring to use GROUP BY "billable"."id" as well.

You can bypass this behaviour, simply by requesting NO ORDERING or ordering by a field of your choice (should be in this case work_date).

Thus, the solution would require changing the last line to:

         group(:work_date).order(false)

or

         group(:work_date).order(:work_date)

Upvotes: 0

Related Questions