Can I push this rails calculation into the database?

Question

I'm trying to increase my app's efficiency by doing work in the database rather than in the app layer, and I'm wondering if I can move this calculation into the database.

Models:

class Offer < ActiveRecord::Base
  has_many :lines
  has_many :items, :through => :lines
end

class Line < ActiveRecord::Base
  belongs_to :offer
  belongs_to :item
  # also has a 'quantity' attribute (integer)
end

class Item < ActiveRecord::Base
  has_many :lines
  has_many :offers, :through => :lines
  # also has a 'price' attribute (decimal)
end

What I want to do is calculate the price of an offer. Currently I have a price method in the Offer class:

def price
  self.lines.inject(0) do |total, line|
    total + line.quantity * line.item.price
  end
end

I suspect it may be possible to do a Offer.sum calculation instead that would get the answer directly from the DB rather than looping through the records, but the Calculations section of the ActiveRecord query guide doesn't have enough detail to help me out. Anybody?

Thanks!

Answer 1

You're correct that you can do this with sum. Something like this:

class Offer < ActiveRecord::Base
  # ...

  def price
    self.lines.sum 'lines.quantity * items.price', :joins => :item
  end
end

When you call e.g. Offer.find( some_id ).price the above will construct a query something like this:

SELECT SUM( lines.quantity * items.price ) AS total
  FROM lines
  INNER JOIN items ON items.id = lines.item_id
  WHERE lines.offer_id = <some_id>
;

Answer 2

Sometimes you're better off with SQL.

SELECT SUM( lines.quantity * items.price ) AS total
  FROM offers
  INNER JOIN lines ON offers.id = lines.offer_id
  INNER JOIN items ON items.id = lines.item_id
  WHERE offers.id = 1
;