Created
January 21, 2013 20:44
Using Arel/ActiveRecord to execute a subquery, including a SUM.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select fund_sums.total, fund_products.name | |
from ( | |
select sum(allocation_amount) total, fund_product_id | |
from transactions | |
where transactions.investor_id = 490 | |
group by fund_product_id | |
) fund_sums | |
left join fund_products on fund_sums.fund_product_id = fund_products.id |
After reading the Arel source code and tests, this will work.
tt = Transaction.arel_table
fpt = FundProduct.arel_table
fund_sums = tt.project(tt[:allocation_amount].sum.as('total'), tt[:fund_product_id])
fund_sums.from(tt)
fund_sums.where(tt[:investor_id].eq(490))
fund_sums.group(tt[:fund_product_id])
fund_sums = fund_sums.as(Arel.sql('fund_sums'))
manager = Arel::SelectManager.new(tt.engine)
manager.project(fund_sums[:total], fpt[:name])
manager.from(fund_sums)
manager.join(fpt).on(fpt[:id].eq(fund_sums[:fund_product_id]))
ActiveRecord::Base.connection.execute(manager.to_sql) #=> An array-like object, full of hashes
#=> [
# { 'name' => 'some fund name', 'total' => 1000.00 }
# ...
# ]
@danshultz Close, but FundProduct#name
is not unique, hence the need to group by the #id
.
You can also do this if that causes problems with duplicate names, alternatively, doing a group by like this works but you get an array as your "key" in the ActiveSupport::OrderedHash
fund_product = FundProduct.arel_table
Transaction.where(:investor_id => 490).joins(:fund_products).sum(:allocation_amount, :group_by => [fund_product[:id], fund_product[:name]])
If you want to do it differently, you can do it with more arel but I think the above should cover you
You could cheat a little more with your example
tt = Transaction.arel_table
fpt = FundProduct.arel_table
fund_sums = Transaction.select(tt[:allocation_amount].sum.as('total'), tt[:fund_product_id])).where(:investor_id => 490).group(:fund_product_id).arel.as(Arel.sql("fund_sums")
Transaction.where(:investor_id => 490).joins(:fund_product).sum(:allocation_amount, :group => [Transaction.arel_table[:fund_product_id], fund_product[:name]])
@stevenharman Thanks for this gist. It helped a lot. What is the magic of this:
manager = Arel::SelectManager.new(tt.engine)
Why .engine
?
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hows that work?