Rails 3.1 with PostgreSQL: GROUP BY should be used in aggregate function

Question

I am trying to load the last 10 Arts, grouped by user_id and sorted by create_at. This works fine with SqlLite and MySQL, but gives an error in my new PostgreSQL database.

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?

Answer 1

The sql generated by the expression is not a valid query, you group by user_id and select many other fields based on this, but do not tell the database how it should collect other files. For example, if your data looks like this:

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

Now, when you ask db to group by a , and also to return b, it does not know how to aggregate the values 1,2 . You need to specify whether to choose min, max, average, sum or something else. Just as I wrote the answer, there were two answers that could explain all this better.

In your use case, I think you do not want the group to be at the db level. Since there are only 10 arts, you can group them in your application. Do not 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], # .... # } 

EDIT: select latest_arts but only one art per user

Just to give you the idea of ​​sql (not tested it since 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 

This decision is based on the practical assumption that no two arts for the same user can have the same highest created_at value, but this may be wrong if you import or program the creation of a large number of art objects. If the assumption is not fulfilled, sql may gain more confidence.

EDIT: Attempting to change the request 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]) 

Answer 2

You need to select the columns you need.

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

This will cause an error when trying to select a header in the request, for example

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

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

This is because when you try to group by user_id, the request does not know how to handle the header in the group, because the group contains several names.

so the exception already mentions that you need to appear in the group with

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

or used in aggregate function

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

Answer 3

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

PostGres actually follows the SQL standard here, and sqlite and mysql are interrupted from the standard.

Answer 4

Take a look at this question - Convert MySQL to PostgreSQL . Postgres does not allow a column to be specified in a select statement that is not in the group by clause.