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

Question

我有一个小部件模型。小部件属于 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 模型字段？

Answer 1

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

假设您有一个如下查询：

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

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

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

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

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

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

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

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

Answer 2

PostgreSQL 版本 9.1（目前为测试版）可能会解决您的问题，但前提是存在对主键的功能依赖。

从发行说明来看：

允许非 GROUP BY 列
主键时查询目标列表
在 GROUP BY 子句中指定
（彼得·艾森特劳特）

已经有一些其他数据库系统了
允许这种行为，并且因为
主键，结果是
明确。

您可以运行测试，看看它是否可以解决您的问题。如果您可以等待生产版本，这可以在不更改代码的情况下解决问题。

Answer 3

首先，通过将所有日期存储在标准时区来简化您的生活。为了方便用户，更改日期和时区实际上应该在视图中完成。仅此一项就可以为您减轻很多痛苦。

如果您已经投入生产，请编写一个迁移来创建一个 normalized_date 列，只要它有帮助。

我建议这里的另一个问题是原始 SQL 的使用，rails 不会为你提供这些信息。为了避免这种情况，请尝试使用名为 Squeel 的 gem（又名 Metawhere 2） http://metautonomo.us/projects/squeel/

如果你使用这个，你应该能够删除硬编码的 SQL，让 Rails 重新发挥它的魔力。

例如：

.select("#{pg_print_date_group} as print_date, sum(amount) as total_amount")

变为（一旦您不再需要标准化日期）：

.select{sum(amount).as(total_amount)}

Answer 4

很抱歉回答我自己的问题，但我已经弄清楚了。

首先，让我向那些认为我可能遇到 SQL 或 Postgres 问题的人道歉，事实并非如此。问题出在 ActiveRecord 及其生成的 SQL 上。

答案是...使用.joins而不是.includes。所以我只是更改了顶部代码中的行，它按预期工作。

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

我猜测当使用 .includes 时，ActiveRecord 试图变得聪明并在 SQL 中使用 JOINS，但对于这种特殊情况它不够聪明，并且生成了丑陋的 SQL 来选择所有关联的列。

不过，所有的回复都教会了我很多我不知道的关于Postgres的知识，所以非常感谢。

Answer 5

在 mysql 中排序：

> ids = [11,31,29]
=> [11, 31, 29]
> Page.where(id: ids).order("field(id, #{ids.join(',')})")

在 postgres 中：

def self.order_by_ids(ids)
  order_by = ["case"]
  ids.each_with_index.map do |id, index|
    order_by << "WHEN id='#{id}' THEN #{index}"
  end
  order_by << "end"
  order(order_by.join(" "))
end

User.where(:id => [3,2,1]).order_by_ids([3,2,1]).map(&:id) 
#=> [3,2,1]