使用Arel生成子查询以获取平均值的平均值 [英] Generating a subquery with Arel to get an average of averages

问题描述

让我们说我有几张桌子

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;"

有人知道吗？

推荐答案

您可以很接近，但是ActiveRecord不直接支持Arel结构的执行，您必须先将它们转换为SQL。

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})")

对此妥协感到满意。 ActiveRecord不太适合一次收集多个模型的汇总数据，如果您明智地不愿将SQL修补在一起，那么Arel可以作为一种适当的工具进行服务器。

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.

这篇关于使用Arel生成子查询以获取平均值的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！