Geek Mama

Customizing Your Query

August 10th, 2010

In the past, I avoided Performing Calculations on Models: episode 14 because I thought that under the hood rails was simply iterating through the array of active records returned from your sql query.

Turns out I was wrong.

There’s a much easier way than this to utilize the SQL functions and get the performance you want:

>> Task.find(:first,
             :select => 'SUM(priority)' 
             :conditions => 'complete=0')

In this episode, Ryan shows us the sql generated from his queries in the script/console and this:

>> Task.sum(:priority, 
            :conditions => 'complete=0')

creates the desirable sql query below:

          SELECT sum(priority) AS sum_priority 
          FROM tasks 
          WHERE (complete=0)

And of course this means that these methods are available through associations.  For example:

>> Project.find(:first).tasks.sum(:priority, 
                                  :conditions => 'complete=0')

is turned into this:

         SELECT sum(priority) AS sum_priority 
         FROM tasks 
         WHERE (tasks.project_id = 1) AND (complete=0)

What SQL functions are available?  Checkout the Calculations class in the api.

Entry Filed under: Professional,Railscasts Project,Ruby on Rails