Adding values from a db the rails way?


#1

I have a log table which has a column recording the number of hours
worked on a task. So there’ll be multiple lines for a particular task.

I want to retrieve the total number of hours worked on a task, so that I
can include the information when one views the task details.

I can write a sql query to do this easily enough, like “SELECT
SUM(hours) AS total FROM tasklog WHERE task_id = x”, but what’s the
rails way to do this?

thx
n.


#2

I’ve solved it like this:

def self.time_logged_for_task(this_task)
self.connection.select_value(“SELECT SUM(hours) FROM timelogs tl
JOIN tasks t ON tl.task_id = t.id WHERE tl.task_id=” + this_task.to_s)
end

If there’s a better way, please let me know! :wink:

n.

Nick C. wrote:

I have a log table which has a column recording the number of hours
worked on a task. So there’ll be multiple lines for a particular task.

I want to retrieve the total number of hours worked on a task, so that I
can include the information when one views the task details.

I can write a sql query to do this easily enough, like “SELECT
SUM(hours) AS total FROM tasklog WHERE task_id = x”, but what’s the
rails way to do this?

thx
n.


#3

check out ActiveRecord::Calculations
(http://api.rubyonrails.com/classes/ActiveRecord/Calculations/ClassMethods.html)

you can do:

Timelog.sum(‘hours’, :conditions => [‘task_id=?’, this_task])

based on what i am seeing you don’t need the join

Nick C. wrote:

I’ve solved it like this:

def self.time_logged_for_task(this_task)
self.connection.select_value(“SELECT SUM(hours) FROM timelogs tl
JOIN tasks t ON tl.task_id = t.id WHERE tl.task_id=” + this_task.to_s)
end

If there’s a better way, please let me know! :wink:

n.

Nick C. wrote:

I have a log table which has a column recording the number of hours
worked on a task. So there’ll be multiple lines for a particular task.

I want to retrieve the total number of hours worked on a task, so that I
can include the information when one views the task details.

I can write a sql query to do this easily enough, like “SELECT
SUM(hours) AS total FROM tasklog WHERE task_id = x”, but what’s the
rails way to do this?

thx
n.


#4

That’s what I was looking for! I needed the join for some more
conditions, so it now looks like:

def self.time_logged_for_task(this_task)
Timelog.sum(‘hours’,
:conditions => [‘task_id=? OR t.parent_id=?’,
this_task.to_s, this_task.to_s],
:joins => “JOIN tasks t ON task_id = t.id”)
end

thx!
n.

jeremy wrote:

check out ActiveRecord::Calculations
(http://api.rubyonrails.com/classes/ActiveRecord/Calculations/ClassMethods.html)

you can do:

Timelog.sum(‘hours’, :conditions => [‘task_id=?’, this_task])

based on what i am seeing you don’t need the join

Nick C. wrote:

I’ve solved it like this:

def self.time_logged_for_task(this_task)
self.connection.select_value(“SELECT SUM(hours) FROM timelogs tl
JOIN tasks t ON tl.task_id = t.id WHERE tl.task_id=” + this_task.to_s)
end

If there’s a better way, please let me know! :wink:

n.


#5

Hello Nick,

I have a log table which has a column recording the number of hours
worked on a task. So there’ll be multiple lines for a particular task.

I want to retrieve the total number of hours worked on a task, so that I
can include the information when one views the task details.

I can write a sql query to do this easily enough, like “SELECT
SUM(hours) AS total FROM tasklog WHERE task_id = x”, but what’s the
rails way to do this?

See ActiveRecord::Calculations in Rails 1.1

@total = Tasklog.sum(‘hours’, :conditions => [‘task_id = ?’, x] )

But maybe when manipulating time durations, it’s easier to store
minutes instead of hours ?
0.33333 hour = 20 minutes.

Then when you display durations, you convert in hours and minutes.
Just an idea… Well, it’s up to you.

 -- Jean-François.