ActiveRecord Calculations 1

Posted by Bob Silva Sat, 22 Apr 2006 14:59:00 GMT

Rick Olson's Calculations Plugin (which is now part of core) is great as long as you only need to perform a calculation on one column in your model. I had a need to sum two columns in a result set and after talking with Rick on IRC, started to modify his Calculations Plugin to accept a hash of {:calc => :column} pairs. Time was of the essence though so I found a different way to do it using AR. The examples below don't really have any wow factor, but they may be helpful to someone.


In the example below, it inner joins table (ModelB) and groups by a column (group_field) from that table, just to show how it might be done.

The magic is the :select option. It allows you to define what goes into the SQL statement (SELECT :select FROM ...). The :readonly attribute on the find call, is to prevent you from trying to modify the record since it is not a fully loaded AR record set. 

@model_a = ModelA.find(:all,
  :select => 'sum(column_a) as column_a, sum(column_b) as column_b, model_b.group_field', 
  :joins => 'inner join model_b on model_a.id = model_b.model_a_id', 
  :group => 'model_b.group_field', 
  :conditions => ['column_a=?', column_a_value],
  :readonly => true)


Now that you have your recordset, you can use your calculated columns like any other AR attribute. In this case, @model_a.column_a. If you build more advaned reporting or charts into your applications, you will find yourself using this method a lot.

Comments

Leave a response

  1. Doug 5 months later:
    Very intersted. I actually encoutered the problem when I don't want to use the rails relationship between models. Instead I want to define my own join statement. And this is helping me to do it. One quick question though, is there anyway I can call the find method without using a model (ModelA in your example)? Because I think the joined query most of the time returns attributes not in the model. And if you use ModelA to find, ModelA eventually will have to have many extra attributes in the returned object(s). Please let me know what you think thru my email Thanks
Comments