带有PostgreSQL的Rails 3.1：必须在汇总函数中使用GROUP BY [英] Rails 3.1 with PostgreSQL: GROUP BY must be used in an aggregate function

问题描述

我正在尝试加载由user_id分组并由created_at排序的最新10部艺术作品。可以在SqlLite和MySQL上正常工作，但是在我的新PostgreSQL数据库上却报错。

I am trying to load the latest 10 Arts grouped by the user_id and ordered by created_at. This works fine with SqlLite and MySQL, but gives an error on my new PostgreSQL database.

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

ActiveRecord错误：

ActiveRecord error:

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...

有什么想法吗？

推荐答案

表达式生成的sql不是有效的查询，您正在按 user_id 分组，以此为基础选择许多其他字段，但没有告诉数据库应该如何聚合其他文件。例如，如果您的数据如下所示：

The sql generated by the expression is not a valid query, you are grouping by user_id and selecting lot of other fields based on that but not telling the DB how it should aggregate the other fileds. For example, if your data looks like this:

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

现在，当您要求db按 a 并返回b，它不知道如何汇总值 1,2 。您需要确定是否需要选择最小值，最大值，平均值，总和或其他内容。就像我在写答案一样，有两个答案可能会更好地解释所有这些问题。

Now when you ask db to group by a and also return b, it doesn't know how to aggregate values 1,2. You need to tell if it needs to select min, max, average, sum or something else. Just as I was writing the answer there have been two answers which might explain all this better.

不过，在您的用例中，我认为您不希望出现这样的情况在数据库级别。由于只有10种艺术，您可以在应用程序中对它们进行分组。不过，请勿将这种方法用于数千种艺术中：

In your use case though, I think you don't want a group by on db level. As there are only 10 arts, you can group them in your application. Don't use this method with thousands of arts though:

 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，但每位用户只能使用一种艺术

Select latest_arts, but only one art per user

只是为了让您了解sql（没有测试过，因为我没有在我的系统上安装了RDBMS）

Just to give you the idea of sql(have not tested it as I don't have RDBMS installed on my system)

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可能会变得更加虚假。

This solution is based on the practical assumption, that no two arts for same user can have same highest created_at, but it may well be wrong if you are importing or programitically creating bulk of arts. If assumption doesn't hold true, the sql might get more contrieved.

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

Attempt to change the query to 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])

这篇关于带有PostgreSQL的Rails 3.1：必须在汇总函数中使用GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！