Rails的扩大与范围字段，PG不喜欢 [英] Rails expanding fields with scope, PG does not like it
问题描述
我的窗口小部件的模型。窗口小部件属于Store模式，它属于一个区域模型，这属于公司。在公司的模式，我需要找到所有相关部件。易：
I have a model of Widgets. Widgets belong to a Store model, which belongs to an Area model, which belongs to a Company. At the Company model, I need to find all associated widgets. Easy:
class Widget < ActiveRecord::Base
def self.in_company(company)
includes(:store => {:area => :company}).where(:companies => {:id => company.id})
end
end
这将产生这个美丽的查询：
Which will generate this beautiful query:
> 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子句或聚合函数。
But, I later need to use this scope in more complex scope. The problem is that AR is expanding the query by selecting individual fields, which fails in PG because selected fields must in the GROUP BY clause or the aggregate function.
下面是更复杂的范围
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：
And this is the select it is throwing at 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)
生成该错误：
Which generates this error:
PGError：错误：列 widgets.id必须出现在 GROUP BY子句中或使用 聚合函数LINE 1：SELECT 小部件，IDAS t0_r0， 小工具。user_ID的...
PGError: ERROR: column "widgets.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "widgets"."id" AS t0_r0, "widgets"."user_id...
我如何重写Widget.in_company范围，使AR不扩大选择查询，包括每一个小部件模型领域？
How do I rewrite the Widget.in_company scope so that AR does not expand the select query to include every Widget model field?
推荐答案
弗兰克解释道，PostgreSQL将拒绝任何不返回重复的一组行中的任何查询。
As Frank explained, PostgreSQL will reject any query which doesn't return a reproducible set of rows.
假设你喜欢的查询：
select a, b, agg(c)
from tbl
group by a
PostgreSQL将拒绝它，因为 B 是未指定的组由语句。运行中的MySQL，相比之下，并且将被接受。在后一种情况下，然而，火了几个插入，更新和删除，以及各行上的磁盘页的顺序结束不同
PostgreSQL will reject it because b is left unspecified in the group by statement. Run that in MySQL, by contrast, and it will be accepted. In the latter case, however, fire up a few inserts, updates and deletes, and the order of the rows on disk pages ends up different.
如果没记错，实现细节都让MySQL将实际排序由A，B，并返回第一个B组中的。但据SQL标准而言，该行为是不确定的 - 果然，PostgreSQL里面的没有的总排序之前运行聚合函数
If memory serves, implementation details are so that MySQL will actually sort by a, b and return the first b in the set. But as far as the SQL standard is concerned, the behavior is unspecified -- and sure enough, PostgreSQL does not always sort before running aggregate functions.
潜在的，这可能会导致 B 不同的价值观导致的PostgreSQL设置。因而，PostgreSQL中产生一个错误，除非你是更具体的：
Potentially, this might result in different values of b in result set in PostgreSQL. And thus, PostgreSQL yields an error unless you're more specific:
select a, b, agg(c)
from tbl
group by a, b
什么弗兰克强调的是，在PostgreSQL的9.1，如果 A 是主键，比你可以离开 B 未指定 - 策划者已被教导要忽略随后按字段时适用的主键意味着唯一的行
What Frank highlighted is that, in PostgreSQL 9.1, if a is the primary key, than you can leave b unspecified -- the planner has been taught to ignore subsequent group by fields when applicable primary keys imply a unique row.
有关特别是你的问题，你需要为你现在做的指定组的以及的每一个你在你的基础上汇总领域，即小工具 ID，小部件，USER_ID，[剪断] 而不是东西，如款项（金额），这是聚合函数呼叫。
For your problem in particular, you need to specify your group by as you currently do, plus every field that you're basing your aggregate onto, i.e. "widgets"."id", "widgets"."user_id", [snip] but not stuff like sum(amount), which are the aggregate function calls.
作为一个题外话侧面说明，我不知道如何使你的ORM /样板工程，但SQL它的产生不是最优的。其中许多左外联接看起来他们应该是内部连接。这将导致使规划师挑选合适的连接顺序适用。
As an off topic side note, I'm not sure how your ORM/model works but the SQL it's generating isn't optimal. Many of those left outer joins seem like they should be inner joins. This will result in allowing the planner to pick an appropriate join order where applicable.
这篇关于Rails的扩大与范围字段，PG不喜欢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
