Calculating row COUNTs


#1

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!


#2

Add this as a method on the model…


#3

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.


#4

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


#5

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


#6

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/Calculations/ClassMethods.html#M000827

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.


#7

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