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?