带有PostgreSQL的Rails 3.1：GROUP BY必须在聚合函数中使用

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.

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

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

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

Any ideas?

4 个解决方案

#1

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:

表达式生成的sql不是有效查询,您按user_id进行分组,并根据该字段选择许多其他字段,但不告诉DB如何聚合其他文件。例如,如果您的数据如下所示:

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

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.

现在当你要求db按a分组并且还返回b时,它不知道如何聚合值1,2。你需要告诉它是否需要选择min,max,average,sum或其他东西。就在我写答案的时候,有两个答案可以更好地解释这一切。

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:

但是在你的用例中,我认为你不希望在db级别上有一个组。由于只有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],
 #    ....
 # }

EDIT: Select latest_arts, but only one art per user

编辑:选择latest_arts,但每个用户只有一个艺术品

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

只是为了给你一个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

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.

此解决方案基于实际假设,即同一用户的两种艺术不能具有相同的最高create_at,但如果您正在导入或以编程方式创建大量艺术,则可能是错误的。如果假设不成立,那么sql可能会更加有条理。

EDIT: Attempt to change the query to Arel:

编辑:尝试将查询更改为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])

#2

You need to select the specific columns you need

您需要选择所需的特定列

Art.select(:user_id).group(:user_id).limit(10)

It will raise error when you try to select title in the query, for example

例如,当您尝试在查询中选择标题时,它会引发错误

Art.select(:user_id, :title).group(:user_id).limit(10)

column "arts.title" must appear in the GROUP BY clause or be used in an aggregate function

列“arts.title”必须出现在GROUP BY子句中或用于聚合函数

That is because when you try to group by user_id, the query has no idea how to handle the title in the group, because the group contains several titles.

这是因为当您尝试按user_id分组时,查询不知道如何处理组中的标题,因为该组包含多个标题。

so the exception already mention you need to appear in group by

所以异常已经提到你需要出现在group by中

Art.select(:user_id, :title).group(:user_id, :title).limit(10)

Art.select(:user_id,:title).group(:user_id,:title).limit(10)

or be used in an aggregate function

或用于聚合函数

Art.select("user_id, array_agg(title) as titles").group(:user_id).limit(10)

Art.select(“user_id,array_agg(title)as titles”)。group(:user_id).limit(10)

#3

Take a look at this post SQLite to Postgres (Heroku) GROUP BY

看看这篇帖子SQLite到Postgres(Heroku)GROUP BY

PostGres is actually following the SQL standard here whilst sqlite and mysql break from the standard.

PostGres实际上遵循SQL标准,而sqlite和mysql违反了标准。

#4

Have at look at this question - Converting MySQL select to PostgreSQL. Postgres won't allow a column to be listed in the select statement that isn't in the group by clause.

看看这个问题 - 将MySQL选择转换为PostgreSQL。 Postgres不允许在select语句中列出不在group by子句中的列。

#1