CODEWITHSUNDEEP
mysqlsqlruby-on-railsrubyactiverecord
wiseland
wiseland

Reputation: 1050

Create rails SUM query object

In DB there ara many accounts, every account has lots of transactions with price attribute. It's needed to count account balance by summarize all transaction prices.

class AccountsController < ApplicationController
  def index
    @accounts = Account.all
  end
end

Account model:

class Account < ActiveRecord::Base
  has_many :transactions, dependent: :destroy
  includes :transactions

  def balance
    transactions.sum(:price) || 0
  end

  def balance_ruby_sum
    sum = 0
    transactions.each { |t| sum+= t.price }
    sum
  end
end

Transaction model:

class Transaction < ActiveRecord::Base
  belongs_to :account
end

account index.html.erb:

<table>
    <th>name</th>
    <th>balance</th>
    <% @accounts.each do |acc| %>
        <td><%= acc.name %></td>
        <td><%= acc.balance %></td>
    <% end %>
</table>

In index view I have account's name and its balance. It's needed to fire N+1 queries to DB to do this and I want to avoid N+1 problem. I tried to use balance_ruby_sum action instead of balance action (only 2 queries are needed), but it's not a good solution (performance issue).

I don't want to retrieve all full transaction, only their sum of prices. Is it possible to count balance by sum prices using SQL and do only 2 queries?

I found an article there in 4 item described how to use a query object. But I don't understand how to create a one in my case.

Upvotes: 0

Views: 2365

Answers (1)

Marc Lainez
Marc Lainez

Reputation: 3080

Try to play with select like this and you should have what you want in a single query.

@accounts = Account.all.joins(:transactions).
  select("accounts.*, SUM(transactions.price) AS balance").
  group("accounts.id")

The only problem with this query is that you will only get the accounts that actually have transactions. Those without transactions will be left out.

What's happening

The first part Account.all.joins(:transactions) joins the accounts and transactions table together. ActiveRecord is clever enough to know that we're using account_id on the transactions to join the tables.

Next, what we want on this join table is the account information, but we'd like an "extra field" that is the sum of all that accounts transactions prices. We basically want:

+-------------+-----+-----+---------+
|  account id | ... | ... | balance |
+-------------+-----+-----+---------+

That's why we need the select statement select("accounts.*, SUM(transactions.price) AS balance"). We're telling ActiveRecord to give us, in this join table, all the account attributes and also to build a special one, balance, that is the sum of all transaction prices associated to this account.

Finally, we need to give ActiveRecord a clue on how to organize the results and we tell it to group the records by account id. Which in our case means one account per line.

balance_ruby_sum

If you want to keep your balance_ruby_sum method, you might wanna consider using inject like this:

def balance_ruby_sum
  transactions.inject(0) { |sum, t| sum + t.price }
end

You won't gain performance but it's more rubyesque :)

Edit return 0 when no transaction as requested in comments:

To make sure we get one line per Account even if there is no transaction attached. We need to force a LEFT OUTER JOIN instead of an INNER JOIN.

Now SUM, when there is no transaction, will return NULL as balance and we want it to return 0. The way to return 0 when there are no transactions is to use the COALESCE keyword around SUM. COALESCE is used to return a default value in case the first parameter is NULL. Your request now looks like this:

@accounts = Account.all.
  joins("LEFT OUTER JOIN transactions ON accounts.id = transactions.account_id").
  select("accounts.*, COALESCE(SUM(transactions.price), 0) as balance").
  group("accounts.id")

You can read more about INNER JOIN and OUTER JOIN here: What is the difference between "INNER JOIN" and "OUTER JOIN"?

Upvotes: 4

Related Questions