Using Arel/ActiveRecord to execute a subquery, including a SUM.

select_from_subquery.sql

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

Transaction.where(:investor_id => 490).joins(:fund_products).sum(:allocation_amount, :group_by => FundProduct.arel_table[:name])

Hows that work?

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?