Querying complex data in Rails 3.0

I have a data model where users have timelogs (which have dates).

I'm trying to query my DB in a way that will let me spit out a table of data, listing each user that has timelogs, and the sum of the time logged for each day in the period queried (similar in essence to a weekly timesheet).

I'm having problems figuring out a way of doing this. 开发者_开发问答Any ideas?

A lot of the details will depend on your data model, but the short answer is to create a relationship first:

class User < ActiveRecord::Base
  has_many :user_timelogs
  has_many :timelogs, :through => :user_timelogs
end

class UserTimelog < ActiveRecord::Base
  belongs_to :user
  belongs_to :timelog
end

class Timelog < ActiveRecord::Base
  has_many :user_timelogs
  has_many :users, :through => :user_timelogs
end

Once you do that, you can query timelogs for users:

User.all.timelogs

You can add on additional queries (specific dates, sums, etc.). Check out the Rails guides for more info on how to narrow down that query:

http://guides.rubyonrails.org/active_record_querying.html

Try this, assuming the times are stored as number of seconds in a 'duration' column for each timelog row:

Timelog.where(...). # your time period
        joins(:users).
        group('users.id').
        sum('duration')

This will give you a hash with the user ids as keys, and the sum of the durations as values.