ruby-on-rails - Rails 3.1 与 PostgreSQL : GROUP BY must be used in an aggregate function

Question

我正在尝试加载按 user_id 分组并按 created_at 排序的最新 10 个艺术。这适用于 SqlLite 和 MySQL，但在我的新 PostgreSQL 数据库上出错。

Art.all(:order => "created_at desc", :limit => 10, :group => "user_id")

ActiveRecord 错误:

Art Load (18.4ms)  SELECT "arts".* FROM "arts" GROUP BY user_id ORDER BY created_at desc LIMIT 10
ActiveRecord::StatementInvalid: PGError: ERROR:  column "arts.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT  "arts".* FROM "arts"  GROUP BY user_id ORDER BY crea...

有什么想法吗？

Answer 1

表达式生成的 sql 不是有效查询，您按 user_id 分组并基于此选择许多其他字段，但没有告诉 DB 它应该如何聚合其他字段。例如，如果您的数据如下所示:

a  | b
---|---
1  | 1
1  | 2
2  | 3

现在，当您要求 db 按 a 分组并返回 b 时，它不知道如何聚合值 1,2。你需要告诉它是否需要选择最小值、最大值、平均值、总和或其他东西。就在我写答案时，有两个答案可能会更好地解释这一切。

不过，在您的用例中，我认为您不希望在数据库级别进行分组。由于只有 10 种艺术，您可以将它们分组在您的应用程序中。不要对成千上万的艺术使用这种方法:

 arts = Art.all(:order => "created_at desc", :limit => 10)
 grouped_arts = arts.group_by {|art| art.user_id}
 # now you have a hash with following structure in grouped_arts
 # { 
 #    user_id1 => [art1, art4],
 #    user_id2 => [art3],
 #    user_id3 => [art5],
 #    ....
 # }

编辑:选择 latest_arts，但每个用户只能选择一种艺术

只是为了让您了解 sql(还没有测试它，因为我的系统上没有安装 RDBMS)

SELECT arts.* FROM arts
WHERE (arts.user_id, arts.created_at) IN 
  (SELECT user_id, MAX(created_at) FROM arts
     GROUP BY user_id
     ORDER BY MAX(created_at) DESC
     LIMIT 10)
ORDER BY created_at DESC
LIMIT 10

此解决方案基于实际假设，即同一用户的两个艺术不能具有相同的最高 created_at，但如果您正在导入或以编程方式创建大量艺术，这很可能是错误的。如果假设不成立，则 sql 可能会变得更加人为。

编辑:尝试将查询更改为 Arel:

Art.where("(arts.user_id, arts.created_at) IN 
             (SELECT user_id, MAX(created_at) FROM arts
                GROUP BY user_id
                ORDER BY MAX(created_at) DESC
                LIMIT 10)").
    order("created_at DESC").
    page(params[:page]).
    per(params[:per])