sql - SQLite 与 Postgres 中的 Rails Active Record 组查询

Question

在使用 Active Record 的 SQLite 和 Postgres 中运行查询时出现错误。 Postgres 中有聚合函数错误，但 SQLite 中没有。我的问题是，SQLite 在这里做什么？是不是忽略了群体？请注意，GROUP BY“歌曲”不会返回错误。

SQLite 输出:

Playlist.where(user_id:user.id).group(:song)

Playlist Load (0.3ms)  SELECT "playlists".* FROM "playlists" WHERE 
"playlists"."user_id" = ? GROUP BY "song"  [["user_id", 1]]

=> #<ActiveRecord::Relation [#<Playlist id: 2, user_id: 1, song_id: 1, 
created_at: "2017-04-27 01:18:01", updated_at: "2017-04-27 01:18:01">]>

Playlist.where(user_id:user.id).group(:song_id)

 Playlist Load (0.4ms)  SELECT "playlists"."id", "playlists"."user_id",
 "playlists"."song_id" FROM "playlists" WHERE "playlists"."user_id" = ?
 GROUP BY "song"  [["user_id", 1]]

  => #<ActiveRecord::Relation [#<Playlist id: 2, user_id: 1, song_id: 
  1, created_at: "2017-04-27 01:18:01">]>

Playlist.where(user_id:user.id).group(:id,:song_id)

 Playlist Load (0.2ms)  SELECT "playlists".* FROM "playlists" WHERE 
 "playlists"."user_id" = ? GROUP BY "playlists"."id", 
 "playlists"."song_id"  [["user_id", 1]]

 => #<ActiveRecord::Relation [#<Playlist id: 1, user_id: 1, song_id: 1, 
 created_at: "2017-04-27 01:18:00", updated_at: "2017-04-27 01:18:00">, 
 #<Playlist id: 2, user_id: 1, song_id: 1, created_at: "2017-04-27 
 01:18:01", updated_at: "2017-04-27 01:18:01">]> 

Playlist.where(user_id:user.id).group(:song).count

 (0.2ms)  SELECT COUNT(*) AS count_all, "playlists"."song_id" AS      
 playlists_song_id FROM "playlists" WHERE "playlists"."user_id" = ? 
 GROUP BY "playlists"."song_id"  [["user_id", 1]]

 Song Load (2.1ms)  SELECT  "songs".* FROM "songs" 
 WHERE "songs"."id" = ? LIMIT 1  [["id", 1]] 

 => {#<Song id: 1, title: "A song", artist: "Artist", user_id: 1, 
 created_at: "2017-04-27 01:17:39">=>2}

Postgres:

Playlist.where(user_id:user.id).group(:song_id)

ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  column 
"playlists.id" must appear in the GROUP BY clause or be used in an 
aggregate function

SELECT "playlists".* FROM "playlists" WHERE "playlists"."user_id" = $1 
GROUP BY "playlists"."song_id"

Playlist.where(user_id:user.id).group(:id,:song_id)

 Playlist Load (0.6ms)  SELECT "playlists".* FROM "playlists" WHERE 
 "playlists"."user_id" = $1 GROUP BY "playlists"."id",      
 "playlists"."song_id"  [["user_id", 1]]

 => #<ActiveRecord::Relation [#<Playlist id: 1, user_id: 1, song_id: 1, 
 created_at: "2017-04-27 01:25:34", updated_at: "2017-04-27 01:25:34">, 
 #<Playlist id: 2, user_id: 1, song_id: 1, created_at: "2017-04-27 
 01:25:36", updated_at: "2017-04-27 01:25:36">]>

Playlist.where(user_id:user.id).group(:song).count

 (0.5ms)  SELECT COUNT(*) AS count_all, "playlists"."song_id" AS 
 playlists_song_id FROM "playlists" WHERE "playlists"."user_id" = $1 
 GROUP BY "playlists"."song_id"  [["user_id", 1]]

 Song Load (0.3ms)  SELECT  "songs".* FROM "songs" WHERE "songs"."id" = 
 $1 LIMIT 1  [["id", 1]]

 => {#<Song id: 1, title: "A song", artist: "Artist", user_id: 1, 
 created_at: "2017-04-27 01:25:26", updated_at: "2017-04-27           
 01:25:26">=>2}

Answer 1

group by 的通用规则:您只能选择 group by 中列出的列和聚合函数。这种方法受 sql 标准支持，适用于任何 sql 数据库。

如果您选择的列未在 group by 子句中列出，例如 select * … group by song_id，它可能在某些数据库中有效，但在某些数据库中无效其他。

如果您在代码中指定选定的列，它将起作用:

Playlist.
  where(user_id:user.id).
  select("song_id").
  group(:song_id)

您可以在 PostgreSQL 文档中阅读有关 group by 的详细信息:https://www.postgresql.org/docs/current/static/sql-select.html#SQL-GROUPBY