Forum: Ruby on Rails Calculating row COUNTs

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.
Jordan I. (Guest)
on 2006-06-03 22:25
hi everyone,

I'm trying to do a simple calculation in my rails project.  I would like
to find all rows in a table that match the given id of another table
(ie. comment_id) and has the value = 1 ("value" is a field in the
table), then add those values up.  Next find the same rows with the
value = -1 and add those values up also.  Finally, I would like to find
the total.  Therefore, if there were 5 rows with value = 1, this would
be 5 and 3 rows with value = -1, this would be -3.  Then when I add them
together, it would be totalvalue = 2.

Would somebody be able to help me with this? Or is there a better way to
do this?  I'm not exactly sure where and how to setup this (do I do this
in the model or controller?).

Thanks!
seth b. (Guest)
on 2006-06-04 01:21
(Received via mailing list)
Add this as a method on the model...
Vince Nibler (Guest)
on 2006-06-04 03:36
(Received via mailing list)
You can use activerecord calculations -

Vote.sum(:value, :group => :value, :conditions => "comment_id = ...

To get the sum of the sums, you could call Vote.sum again without the
:group
option, or iterate and sum the result from the first calculation,
avoiding
another pass at the database.

The method might make most sense on the Comment model.  The Comment
could
set a scope (with_scope) on the Vote model, and then call sum without
conditions.  You might be more interested in calling the count and
average
calculations instead of the sum calculation, if I am guessing correctly
that
you are trying to report the total number of votes, and the average
value of
the vote.
Jordan I. (Guest)
on 2006-06-04 09:50
Thank you so much for your reply!  This helped me out greatly.  Now I am
almost there! Does anyone know how to call the ID within the conditions?

Here is what I have:

def show
    @blah = Blah.find(params[:id])
    @totalpoints = Blah.find(params[:id]).blah_points.sum('value',
:conditions => 'blah_id = ????')
end

I would like 'blah_id' equal to the params[:id].

BTW: For archieve reference, I ended up using the sum() in activeRecord
to do this calculations:
http://api.rubyonrails.org/classes/ActiveRecord/Ca...

Thanks again!
Jordan


Vince Nibler wrote:
> You can use activerecord calculations -
>
> Vote.sum(:value, :group => :value, :conditions => "comment_id = ...
>
> To get the sum of the sums, you could call Vote.sum again without the
> :group
> option, or iterate and sum the result from the first calculation,
> avoiding
> another pass at the database.
>
> The method might make most sense on the Comment model.  The Comment
> could
> set a scope (with_scope) on the Vote model, and then call sum without
> conditions.  You might be more interested in calling the count and
> average
> calculations instead of the sum calculation, if I am guessing correctly
> that
> you are trying to report the total number of votes, and the average
> value of
> the vote.
Roman LE NEGRATE (Guest)
on 2006-06-04 10:03
(Received via mailing list)
> def show
>     @blah = Blah.find(params[:id])
>     @totalpoints = Blah.find(params[:id]).blah_points.sum('value',
> :conditions => 'blah_id = ????')
> end
>
> I would like 'blah_id' equal to the params[:id].

With ActiveRecord you can insert custom values in conditions -- that
will be automatically quoted for you -- by using an Array where the
first element is the condition string with question marks as values,
and other elements as the values to replace those question marks. In
your case, that would be :

@totalpoints = Blah.find(params[:id]).blah_points.sum
('value',  :conditions => ['blah_id = ?', params[:id]])


Roman
Jordan I. (Guest)
on 2006-06-04 10:15
ahh! It worked perfectly! That is great information.  I always wondered
what the '?' meant.

Thanks to all whom contributed!

Roman LE NEGRATE wrote:
>> def show
>>     @blah = Blah.find(params[:id])
>>     @totalpoints = Blah.find(params[:id]).blah_points.sum('value',
>> :conditions => 'blah_id = ????')
>> end
>>
>> I would like 'blah_id' equal to the params[:id].
>
> With ActiveRecord you can insert custom values in conditions -- that
> will be automatically quoted for you -- by using an Array where the
> first element is the condition string with question marks as values,
> and other elements as the values to replace those question marks. In
> your case, that would be :
>
> @totalpoints = Blah.find(params[:id]).blah_points.sum
> ('value',  :conditions => ['blah_id = ?', params[:id]])
>
>
> Roman
Jordan I. (Guest)
on 2006-06-04 10:31
Oops, one more question: is it possible at all to display these points
in my index view (ie. no id supplied).  Since this calculation is not
stored in the database, I wouldn't be able to access it in the view
right?  or is it possible to access this value in the loop (for blah in
@blahs) somehow??  Or for this, do I have to create a new model? - I
think the shopping cart example does this in the book.

Thanks again!


Jordan I. wrote:
> ahh! It worked perfectly! That is great information.  I always wondered
> what the '?' meant.
>
> Thanks to all whom contributed!
>
> Roman LE NEGRATE wrote:
>>> def show
>>>     @blah = Blah.find(params[:id])
>>>     @totalpoints = Blah.find(params[:id]).blah_points.sum('value',
>>> :conditions => 'blah_id = ????')
>>> end
>>>
>>> I would like 'blah_id' equal to the params[:id].
>>
>> With ActiveRecord you can insert custom values in conditions -- that
>> will be automatically quoted for you -- by using an Array where the
>> first element is the condition string with question marks as values,
>> and other elements as the values to replace those question marks. In
>> your case, that would be :
>>
>> @totalpoints = Blah.find(params[:id]).blah_points.sum
>> ('value',  :conditions => ['blah_id = ?', params[:id]])
>>
>>
>> Roman
This topic is locked and can not be replied to.