Rails 用范围扩展领域，PG 不喜欢它

我有一个小部件模型。小部件属于 Store 模型，Store 模型属于 Area 模型，Area 模型属于 Company。在公司模型中，我需要找到所有关联的小部件。简单的：

class Widget < ActiveRecord::Base
  def self.in_company(company)
    includes(:store => {:area => :company}).where(:companies => {:id => company.id})
  end
end

这将生成这个漂亮的查询：

> Widget.in_company(Company.first).count

SQL (50.5ms)  SELECT COUNT(DISTINCT "widgets"."id") FROM "widgets" LEFT OUTER JOIN "stores" ON "stores"."id" = "widgets"."store_id" LEFT OUTER JOIN "areas" ON "areas"."id" = "stores"."area_id" LEFT OUTER JOIN "companies" ON "companies"."id" = "areas"."company_id" WHERE "companies"."id" = 1
 => 15088 

但是，我稍后需要在更复杂的范围中使用这个范围。问题在于 AR 通过选择单个字段来扩展查询，这在 PG 中失败，因为所选字段必须位于 GROUP BY 子句或聚合函数中。

这是更复杂的范围。

def self.sum_amount_chart_series(company, start_time)
  orders_by_day = Widget.in_company(company).archived.not_void.
                  where(:print_datetime => start_time.beginning_of_day..Time.zone.now.end_of_day).
                  group(pg_print_date_group).
                  select("#{pg_print_date_group} as print_date, sum(amount) as total_amount")

end

def self.pg_print_date_group
  "CAST((print_datetime + interval '#{tz_offset_hours} hours') AS date)"
end

这是它向 PG 抛出的选择：

> Widget.sum_amount_chart_series(Company.first, 1.day.ago)

SELECT "widgets"."id" AS t0_r0, "widgets"."user_id" AS t0_r1,<...BIG SNIP, YOU GET THE IDEA...> FROM "widgets" LEFT OUTER JOIN "stores" ON "stores"."id" = "widgets"."store_id" LEFT OUTER JOIN "areas" ON "areas"."id" = "stores"."area_id" LEFT OUTER JOIN "companies" ON "companies"."id" = "areas"."company_id" WHERE "companies"."id" = 1 AND "widgets"."archived" = 't' AND "widgets"."voided" = 'f' AND ("widgets"."print_datetime" BETWEEN '2011-04-24 00:00:00.000000' AND '2011-04-25 23:59:59.999999') GROUP BY CAST((print_datetime + interval '-7 hours') AS date)

这会产生此错误：

PGError：错误：列 “widgets.id”必须出现在 GROUP BY 子句或用于 聚合函数第 1 行：选择 “小部件”。“id”AS t0_r0， “小部件”。“user_id...

如何重写 Widget.in_company 范围，以便 AR 不会扩展选择查询以包含每个 Widget 模型字段？

正如 Frank 所解释的，PostgreSQL 将拒绝任何不返回可重现行集的查询。

假设您有如下查询：

select a, b, agg(c)
from tbl
group by a

PostgreSQL 将拒绝它，因为b中未指定group by陈述。相比之下，在 MySQL 中运行它，它将被接受。然而，在后一种情况下，启动一些插入、更新和删除，并且磁盘页面上的行的顺序最终会有所不同。

如果没记错的话，实现细节是 MySQL 实际上按 a、b 排序并返回集合中的第一个 b。但就 SQL 标准而言，行为是未指定的 —— 果然，PostgreSQL 确实如此not始终在运行聚合函数之前进行排序。

这可能会导致不同的值b在 PostgreSQL 的结果集中。因此，除非你更具体，否则 PostgreSQL 会产生错误：

select a, b, agg(c)
from tbl
group by a, b

Frank 强调的是，在 PostgreSQL 9.1 中，如果a是主键，你可以离开b未指定——当适用的主键暗示唯一行时，规划器被教导忽略后续的分组依据字段。

特别是对于您的问题，您需要像当前一样指定您的分组依据，plus您聚合的每个字段，即"widgets"."id", "widgets"."user_id", [snip]但不是类似的东西sum(amount)，这是聚合函数调用。

作为一个题外话，我不确定你的 ORM/模型是如何工作的，但它生成的 SQL 并不是最佳的。许多左外连接看起来应该是内连接。这将允许规划者在适用的情况下选择适当的连接顺序。