Generating a subquery with Arel to get an average of averages

Lets say I have a few of tables

orders = Arel::Table.new :orders
stores = Arel::Table.new :stores
managers = Arel::Table.new :managers

And a manager has many stores and a store has many orders.

One day I want to query for the average total across orders where a manager works. Oh, I want to group that by the store. So to be clear, I want to get the average order total for a manager, for each of the stores they work at.

And let's assume we've looked up our manager:

manager = Manager.find(some_id)


totals = orders.where(orders[:store_id].in开发者_运维问答(manager.store_ids)).group(orders.store_id).project(orders[:total].average)

puts totals.to_sql

"SELECT AVG(`orders`.`total`) AS avg_id FROM `orders` WHERE `orders`.`store_id` IN (1, 2, 3) GROUP BY `orders`.`store_id`"

Yup, that works great. But how can I get a query for the average of those averages?

What's the Arel to get this query?

"SELECT AVG(avg_id) FROM (SELECT AVG(`orders`.`total`) AS avg_id FROM `orders` WHERE `orders`.`store_id` IN (1, 2, 3) GROUP BY `orders`.`store_id`) as avg_id_alias;"

Anybody know?

You can get pretty close, but ActiveRecord doesn't support the execution of Arel structures directly, you have to convert them to SQL first.

Here's an example of something similar to what you're doing:

o = Orders.arel_table
o_avg = Orders.select(:avg[:store_id].as('avg_id')).
              where(:store_id => [1,2,3]).group(:store_id)

Orders.find_by_sql("select avg(avg_id) from (#{o_avg.to_sql})")

Be happy with this compromise. ActiveRecord is poorly suited to gather aggregate data about more than one model at a time, and if you wisely are leery of patching SQL together, then Arel can server as an adequate tool for it.

I saw your question up on Forrst - there are a couple of answers here, but they look like workarounds:

Nested queries in Arel

Building a subquery with ARel in Rails3

Isn't this a key failing of ORM? For anything above rudimentary SQL calls - either the database gets compromised to appease the ORM, or conversely we have to convert the SQL into AQL,BQL,CQL (insert proprietary ORM fluid interface here).