Forum: Ruby on Rails Activerecord equivalent of the SQL "SUM()" function.

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Ddude N. (Guest)
on 2006-02-28 21:24
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.
Rick O. (Guest)
on 2006-02-28 21:36
(Received via mailing list)
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
Ddude N. (Guest)
on 2006-02-28 22:06
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.
Rick O. (Guest)
on 2006-02-28 22:30
(Received via mailing list)
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
DDudde (Guest)
on 2006-03-01 01:30
Sounds useful, Rick. Feel free to send it to 
removed_email_address@domain.invalid
when you have time. Thanks.
Rick O. (Guest)
on 2006-03-01 01:43
(Received via mailing list)
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/calc...

Here's the documentation for rails 1.1:
http://dev.rubyonrails.org/browser/trunk/activerec...

--
Rick O.
http://techno-weenie.net
DDudde (Guest)
on 2006-03-01 03:16
Excellent. Thanks very much for your help.
This topic is locked and can not be replied to.