Activerecord equivalent of the SQL "SUM()" function


#1

Search doesn’t seem to be working, so forgive me if I’m covering old
ground here, but I’m having a difficult time figuring out how to
implement a “SUM()” function using Active record. Obviously, I could
just create a query, but I’d rather make it part of my active record
object.

Basically I have an object called “Report < ActiveRecord::Base” with a
field called “hours” (work hours). Here’s how I access it from within my
helper function:

Report.find(:first, :conditions => “date = '” <<
current_date.strftime("%Y-%m-%d") << “’ AND user_id = '” <<
@user.id.to_s << “’”)

However, in some cases I want the exact same info as above, except a
running total of the entire week. Here’s more or less how I’d like to
access it (though this returns the first field in a record set, rather
than a single, summed record):

Report.find(:first, :conditions => “date >= '” << begin_date << “’ AND
date <= '” << end_date << “’ AND user_id = '” << @user.id.to_s << “’”)

As I said, the above just returns the first field of a recordset for the
given date range – not a sum of the entire week. I could iterate
through that record set and create a running total, but that doesn’t
seem like the right way to do it.


#2

Thanks for the info and quick reply. I had a feeling I was going to get
frowned upon for those strings. I come from a PHP background, so I have
a few habits to break.

So without rails 1.1 or edge rails, how would one go about using SUM?
I’m on a very, very tight deadline, and basically just need to get a
simple app done in the next day or so. I’m not sure I have the time to
update my on line server’s rails software right now.


#3

On 2/28/06, DDude N. removed_email_address@domain.invalid wrote:

Report.find(:first, :conditions => “date = '” <<

As I said, the above just returns the first field of a recordset for the
given date range – not a sum of the entire week. I could iterate
through that record set and create a running total, but that doesn’t
seem like the right way to do it.

Whoa there, cowboy. First of all, you might want to use sql params
for safe escaping:

Report.find(:first, :conditions => [‘date = ? and user_id = ?’,
current_date.to_date, @user.id])

Rails 1.1 will (and edge rails has right now) a new calculations
feature.

i dont know what field you’re summing, so ‘points’ it is

report = Report.sum(:points, :group => :user, :conditions => [‘date =
?’, current_date.to_date])

Now you can access report like this:
report[@user]
=> 55

Or, loop through the report.

report.each do |user, points|
puts “#{user.login} scored #{points}”
end

If you just want the total for one user one one day, do this:

Report.sum(:points, :conditions => [‘date = ? and user_id = ?’,
current_date.to_date, @user.id])
=> 55


Rick O.
http://techno-weenie.net


#4

Sounds useful, Rick. Feel free to send it to removed_email_address@domain.invalid
when you have time. Thanks.


#5

Try my calculations plugin. Oh wait, my host is down at the moment.
If you like I can zip it up and send it to you.

It’s the same thing that went into 1.1, only you’d call
Report.calculate :sum, :points… instead of Report.sum :points…

On 2/28/06, DDude N. removed_email_address@domain.invalid wrote:


Posted via http://www.ruby-forum.com/.


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails


Rick O.
http://techno-weenie.net


#6

Excellent. Thanks very much for your help.


#7

On 2/28/06, DDudde removed_email_address@domain.invalid wrote:

Sounds useful, Rick. Feel free to send it to removed_email_address@domain.invalid
when you have time. Thanks.

no need!

script/plugin install
http://techno-weenie.net/svn/projects/plugins/calculations/

Sorry, it’s pretty sparse on the documentation.

Here’s the test cases from the plugin:
http://techno-weenie.net/svn/projects/plugins/calculations/test/order_test.rb

Here’s the documentation for rails 1.1:
http://dev.rubyonrails.org/browser/trunk/activerecord/lib/active_record/calculations.rb


Rick O.
http://techno-weenie.net