---
title: Refactoring Ruby Iteration Patterns to the Database
teaser: 'Calculations across objects are ripe for using a map and an inject but frequently,
  with ActiveRecord, the solution is more elegant and faster if we can have SQL do
  the work instead.

  '
tags: web,rails,sql
author: Simon Taranto
published_on: 2014-04-15
---

Frequently on projects we need to run a calculation through an ActiveRecord
association. For example, we might want to get a user's all time purchases, a
company's total amount of products sold, or in our case, the total amount of
loans made to a campaign. This sort of calculation is ripe for using a map and
an inject but frequently the solution is more elegant and faster if we can have
<abbr title="Structured Query Language">SQL</abbr> do the work instead.

Here is what our domain looks like.

    class Campaign < ActiveRecord::Base
      has_many :loans
    end

    class Loan < ActiveRecord::Base
      belongs_to :campaign
    end

### The goal

We want to be able to ask a campaign how much money it has raised. An initial
implementation might look like this.

    class Campaign < ActiveRecord::Base
      # ...
      def funds_raised
        loans.map(&:amount).inject(:+)
      end
    end

We are using the association to get the campaign's loans and then summing up the
amount of each loan. If we look at our logs we'll see that the <abbr
title="Structured Query Language">SQL</abbr> generated is grabbing all of the
loans for our campaign and pulling them into memory.

    SELECT "loans".* FROM "loans" WHERE "loans"."campaign_id" = <target_campaign_id>

Then in Ruby (at the application layer), we run the map to get the amounts and
the inject to get the sum. Done. Not that bad but we can do better.

### Using SQL sum

We want to avoid pulling all this data into memory just to get a single number.
Let's have the database do the heavy lifting.

    class Campaign < ActiveRecord::Base
      # ...
      def funds_raised
        loans.sum(:amount)
      end
    end

With this implementation we have the database do the calculation. We get access
to `sum` from ActiveRecord. Check out the [docs](http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-sum)
for more details.

Looking at the logs we can see the following<abbr title="Structured Query Language">SQL</abbr>.

    SELECT SUM("loans"."amount") AS sum_id FROM "loans" WHERE "loans"."campaign_id" = <target_campaign_id>

This <abbr title="Structured Query Language">SQL</abbr> is going to give us the
same answer but in a single step. Through this refactoring we have code that is
easier to read, we use less application memory, and we see a performance boost
because we're pushing the calculation down to the database. Based on
[benchmarks](https://gist.github.com/srt32/10687302) where each campaign has
1000 loans, the `sum` approach can be more than 20x faster than using `map /
inject`.

Next time we reach for a map and inject on associated ActiveRecord objects,
we'll check to see if there is a way to have the database do the work instead.

### What's next

If you would like some more background on additional <abbr title="Structured
Query Language">SQL</abbr> aggregate functions check out the following articles:

* [PostgreSQL's documentation on aggregate functions](http://www.postgresql.org/docs/9.3/static/tutorial-agg.html)
* [PostgreSQL's available aggregate functions](http://www.postgresql.org/docs/9.3/static/functions-aggregate.html)