Rails - Feb 2006 - Activerecord equivalent of the SQL "SUM()" function.

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.

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

On 2/28/06, DDude None <mendake_ddude@yahoo.com>
wrote:
> 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.
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 Olson
http://techno-weenie.net

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.


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

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 None <mendake_ddude@yahoo.com>
wrote:
> 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.
>
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

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

Sounds useful, Rick. Feel free to send it to mendake_ddude@yahoo.com 
when you have time. Thanks.

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

On 2/28/06, DDudde <mendake_ddude@yahoo.com>
wrote:
> Sounds useful, Rick. Feel free to send it to mendake_ddude@yahoo.com
> 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 Olson
http://techno-weenie.net

Excellent. Thanks very much for your help.

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