Calculable Attrs
calculable_attrs gem allows you to add dynamically calculable attributes (like balance) to your models.
##Installation Just add recent version to your Gemfile:
gem 'calculable_attrs', '0.0.15'
##Usage
###Calculable attributes definition Let's say you have the following data model in your project:
User (one)->(many) Account (one) -> (many) Transaction
Account's balance is a sum all transactions assigned to the account. User's balance is a sum of all transactions assigned to this user's accounts.
calculable_attrs allows you to define dynamically calculable attributes balance and number of transactions as following:
class User < ActiveRecord::Base
has_many :accounts
calculable_attr(balance: 'SUM(amount)', number_of_transactions: 'COUNT(*)', foreign_key: 'accounts.user_id') do
Transaction.joins(:account)
end
end
class Account < ActiveRecord::Base
has_many :transactions
belongs_to :user
calculable_attr balance: 'SUM(amount)',
number_of_transactions: 'COUNT(*)',
from: -> { Transaction.joins(:account) }
end
class Transaction < ActiveRecord::Base
belongs_to :account
end
####NOTES
-
#calculable_attr
accepts a hash of calculable attributes (like{ attribute_name: 'formula' }
where formula is SQL aggregation function). - the block after calculable_attr method has to return relation. This relation will be used as basis for aggregation functions mentioned above
- you can use
calculable_attr ..., from: -> { ... }
instead ofcalculable_attr (...) { ... }
- the default value for calculable_attr is 0 but you can specify it like:
class Account < ActiveRecord::Base
has_many :transactions
belongs_to :user
calculable_attr(balance: ['SUM(amount)', '-'], number_of_transactions: ['COUNT(*)', nil]) { Transaction.joins(:account).all }
end
- the default value for aggregation key is
"#{ Model }.id"
, butyou can specify it with
foreign_key:` like in example below:
class Account < ActiveRecord::Base
has_many :transactions
belongs_to :user
calculable_attr(balance: 'SUM(amount)', foreign_key: 'account_id') { Transaction.all }
end
###Single record After calculable attributes defined you'll be able to use these fields like in the following examples:
b = User.first.balance
####NOTES
- calculate_attrs caches the value. So next call to
b.balance
will NOT run anothes SQL query
###Relation You will be able to include calculable attrs in the your queries like blow
@accounts = Account.includes_calculable_attrs(:balance)
...
<% @accounts.each do |acc|%>
Account Balance: <%= acc.balance %>
<% end %>
The code below will run 2 SQL queries.
SELECT * FROM balances;
SELECT SUM(amount) from transacitons WHERE account_id IN (1,2,3, /* ect */)
As you can see calculable_attrs does kind of eager loading. Just like standard ActiveRecord::Relation#includes(...)
work.
You also can call #includes_calculable_attrs
without parameters. In this case all calculable attributes will be included.
It's also possible to calculate attrs for subordinate queries.
@users = User.includes(:accounts).includes_calculable_attrs(accounts: :balance)
...
<% @users.each do |user|%>
<% user.accounts.each do |acc|%>
User Account Balance: <%= acc.balance %>
<% end %>
<% end %>
Eager loading will work for this case also.
Take a look of several more possible combinations of parameters below.
@users = User.includes(:accounts).includes_calculable_attrs(:balance, :number_of_transactions, accounts: [:balance, :number_of_transactions])
...
<% @users.each do |user|%>
User Balance: <%= user.balance %>
User Transactions: <%= user.number_of_transactions %>
<% user.accounts.each do |acc|%>
User Account Balance: <%= acc.balance %>
User Account Transactions: <%= acc.number_of_transactions %>
<% end %>
<% end %>
@users = User.includes(:accounts).includes_calculable_attrs(true, accounts: true)
...
<% @users.each do |user|%>
User Balance: <%= user.balance %>
User Transactions: <%= user.number_of_transactions %>
<% user.accounts.each do |acc|%>
User Account Balance: <%= acc.balance %>
User Account Transactions: <%= acc.number_of_transactions %>
<% end %>
<% end %>
####NOTES
-
#includes_calculable_attrs
solves n+1 queries problem -
#includes_calculable_attrs
takes an array of attributes to be pre-calculated -
#includes_calculable_attrs
undergrads associations (just likeincludes
orjoins
) -
#includes_calculable_attrs(true)
means "calculate all attributes" -
#includes_calculable_attrs
with no parameters acts just like calculate_attrs(true)
###Where clause
If you're using PostgreSQL or MySQL 5.1+ it's also possible to use calculable attributes in your where clauses.
To do this you have to use #joins_calcululable_attrs
Take a look on example below.
@users = Account.joins_calculable_attrs(:balance).where('accounts.balance > ?', 1000 )
You jast use accounts.balance > ?
condition like if balance
filed were a part of accounts
table.
It will generate the following SQL:
SELECT accounts.*, COALESCE(__calculated_attrs_0__.account_balance, 0) AS account_balance
FROM "accounts"
LEFT JOIN
(
SELECT SUM(amount) AS account_balance, accounts.id AS __calculable_id__
FROM "transactions"
INNER JOIN "accounts" ON "accounts"."id" = "transactions"."account_id"
GROUP BY accounts.id
)
AS __calculated_attrs_0__
ON __calculated_attrs_0__.__calculable_id__ = accounts.id
WHERE (COALESCE(__calculated_attrs_0__.account_balance, 0) > 1000)
As you can see a subquery is used here that's why it will for work for SQLite. But anyway it generates only ony SQL query.
Note that calculated attribute name in the resulting query will be #{ Model.name.underscore}_#{ attr_name }
,
but calculable_attrs will include it in resulting Account
record anyway.
You can also use hash style to specify where clause.
@accounts = Account.joins_calculable_attrs(:balance).where(accounts: { balance: [50..100] })
...
<% @users.each do |user|%>
<% user.accounts.each do |acc|%>
User Account Balance: <%= acc.balance %>
<% end %>
<% end %>
The balance
calculable attribute will be eager loaded so that there will be NO SQL queries in the loop.
One more example.
@accounts = Account.joins_calculable_attrs(:balance).where(balance: [50..100])
You will be able to combine #joins_calculable_attrs
with #includes_calculable_attrs
like in example below.
@users = User.includes(:accounts)
.joins_calculable_attrs(:balance)
.includes_calculable_attrs(:balance, :number_of_transactions, accounts: [:balance, :number_of_transactions])
.where('account_balance > ?', 1000 )
BUT you can NOT to this (yet :)
@users = Account.includes(:user)
.joins_calculable_attrs(user: :balance)
.where('account_balance > ?', 1000 )
~I'm going to implement this in the new version ~
####NOTES
-
#joins_calculable_attrs
allow usage calculable attributes in where clauses. -
#joins_calculable_attrs
will generate left-joins subquery (will NOT work in SQLite). -
#joins_calculable_attrs
undergrads associations (just likeincludes
orjoins
) - It's possible to combine
#joins_calculable_attrs
with#includes_calculable_attrs
. Calculable_attrs will minimize number of SQL queries in this case. -
#joins_calculable_attrs
for nested records is NOT implemented yet.
###Calulations Calulations are NOT impelemted for calculable atts yet. But you can user where clayses for in static fields calculations. See examples blow
c1 = Account.includes_calculable_attrs.where('accounts.balance > 1000').count(*)
c2 = Account.includes_calculable_attrs.where('accounts.balance > 1000').min(:id)