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 ha

Solution 1:

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.

Solution 2:

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).