I am sorry if I am asking the question poorly. I have a Rails 3.1 app with models (simplified) like so:
class Employee < ActiveRecord::Base
has_many :merged_children, :class_name => 'Employee', :foreign_key => "merge_parent_id"
has_many :timesheets
def total_time
merged_children.timesheets.in_range(range).hours_minutes.sum
end
end
class Timesheet < ActiveRecord::Base
belongs_to :employee
def in_range(range)
# filter records based on transaction_date in range
end
def hours_minutes
(hours + minutes/60.0).to_f
end
end
Note: The in_range method acts as a scope, essentially, and hours_minutes is a calculation. hours_minutes is valid for each timesheet record in the resulting dataset, and then total_time should sum those values and return the amount.
The "total_time" method is not working because employee.merged_children returns an array and timesheets is meant to run against a single Employee object.
Is there any way to structure the "total_time" so that it still sends one query to the db? It seems inelegant to iterate over the merged_children array, issuing a query for each. Not sure if a direct call to an Arel table would help or hurt, but I am open to ideas.
If we get it right, the resulting SQL should effectively look something like:
SELECT sum(hours + minutes/60.0)
FROM employees e1 join employees e2 on e1.id = e2.merge_parent_id join timesheets t on t.employee_id = e2.id
WHERE e1.id = [@employee.id] and t.transaction_date BETWEEN [@range.begin] and [@range.end]
Thanks so much!
The easiest thing here might be to add
has_many :children_timesheets, :through => :merged_children, :source => :timesheets
To your employee model,
Then (assuming in_range is actually a scope, or a class method that does a find)
children_timesheets.in_range(...)
Should be the collection of timesheets you're interested in and you can do something like
children_timesheets.in_range(...).collect(&:hours_minutes).sum