Łukasz Korol Łukasz Korol - 1 year ago 38
Ruby Question

Getting most busy employee from the query

I have two models:

# Table name: work_schedules
# id :integer not null, primary key
# start_time :time not null
# end_time :time not null
# day_of_week :string not null
# updated_at :datetime not null
# person_id :integer

# Table name: people
# id :integer not null, primary key
# pesel :string not null
# first_name :string not null
# last_name :string not null

I want to get the most busy employee in query.
First I should count hours(end_time - start_time) and count day_of_week(unique attribute scope: person_id). I don't have any idea how to create properly query this.
What I tried:

@most_busy = Person.joins(:work_schedules).where(%q{end_time - start_time}).first

Answer Source

For all days of the week (i.e., summing across all days of the week): WorkSchedule.group(:person_id).sum('extract(epoch from work_schedules.end_time) -extract(epoch from work_schedules.start_time)').sort_by { |_,v| -v}.first.first

This will give you the ID of the Person who has worked the most over all WorkSchedules. I've only tested it with PostgreSQL.

What's going on in this query:

  1. Get all the work schedule objects and group them by person ids
  2. For each of these groups of work schedules, get the sum of the differences between the start and end times. The extract epoch from tells SQL to get the timestamps as integers (UNIX timestamps, I believe). It is necessary because otherwise the result of the query gives these sums as strings such as "17 days 136:57:42.748124" which ruby cannot sort correctly.
  3. The SQL query results in hash of the form {person_id: time_scheduled_in_seconds}.
  4. The sort_by sorts the query in reverse of the values. We aren't using the keys from the hash so we use an underscore rather than a name to ignore them.
  5. Then we take the first item in this hash (hence the first ".first").
  6. Since that actually returns an array with the person_id as the first value and the number of seconds as the second value, we keep only the first item (hence the second ".first")