sql - 不明确的 'id' 列错误 - Rails/Postgresql

Question

不是骗子。我在问题的底部添加了原因。

我有两个模型，Playlist 和 User，它们通过第三个表 PlaylistUser 拥有_many 并且彼此属于彼此。

对于某个播放列表@playlist，我想打印与@playlist 共享用户的所有其他播放列表的列表。理想情况下，此列表将按他们共享的用户数排序。

此查询旨在执行此操作，但会导致错误:

Playlist.joins(:users).where.not(id: @playlist.id).where(users: {id: @playlist.user_ids}).group(:id).order('count(*) desc')

型号

class Playlist < ActiveRecord::Base
  has_many :playlist_users
  has_many :users, :through => :playlist_users
end

class PlaylistUser < ActiveRecord::Base
  belongs_to :playlist
  belongs_to :user
end

class User < ActiveRecord::Base
  has_many :playlist_users
  has_many :playlists, :through => :playlist_users
end

我认为问题在于 Rails 不知道我指的是哪个 :id 列。

我该如何解决？

这是我在控制台中运行查询时遇到的错误:

> @playlist = Playlist.first
> Playlist.joins(:users).where.not(id: @playlist.id).where(users: {id: @playlist.user_ids}).group(:id).order('count(*) desc')

(415.0ms)  SELECT "users".id FROM "users" INNER JOIN "playlist_users" ON "users"."id" = "playlist_users"."user_id" WHERE "playlist_users"."playlist_id" = $1  [["playlist_id", 1]]
Playlist Load (74.2ms)  SELECT "playlists".* FROM "playlists" INNER JOIN "playlist_users" ON "playlist_users"."playlist_id" = "playlists"."id" INNER JOIN "users" ON "users"."id" = "playlist_users"."user_id" WHERE ("playlists"."id" != 1) AND "users"."id" IN (15, 73, 77, 128, 76, 93, 119, 94, 112, 120, 579, 567, 568, 569, 639, 640, 641, 642, 643, 609, 603) GROUP BY id ORDER BY count(*) desc
PG::AmbiguousColumn: ERROR:  column reference "id" is ambiguous
LINE 1: ...3, 609, 603) GROUP BY id  ORDER ...
                                                         ^
: SELECT "playlists".* FROM "playlists" INNER JOIN "playlist_users" ON "playlist_users"."playlist_id" = "playlists"."id" INNER JOIN "users" ON "users"."id" = "playlist_users"."user_id" WHERE ("playlists"."id" != 1) AND "users"."id" IN (15, 73, 77, 128, 76, 93, 119, 94, 112, 120, 579, 567, 568, 569, 639, 640, 641, 642, 643, 609, 603) GROUP BY id  ORDER BY count(*) desc
ActiveRecord::StatementInvalid: PG::AmbiguousColumn: ERROR:  column reference "id" is ambiguous
LINE 1: ...3, 609, 603) GROUP BY id  ORDER ...
                                                         ^
: SELECT "playlists".* FROM "playlists" INNER JOIN "playlist_users" ON "playlist_users"."playlist_id" = "playlists"."id" INNER JOIN "users" ON "users"."id" = "playlist_users"."user_id" WHERE ("playlists"."id" != 1) AND "users"."id" IN (15, 73, 77, 128, 76, 93, 119, 94, 112, 120, 579, 567, 568, 569, 639, 640, 641, 642, 643, 609, 603) GROUP BY id  ORDER BY count(*) desc
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/activerecord-4.0.10/lib/active_record/connection_adapters/postgresql_adapter.rb:798:in `async_exec'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/activerecord-4.0.10/lib/active_record/connection_adapters/postgresql_adapter.rb:798:in `exec_no_cache'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/activerecord-4.0.10/lib/active_record/connection_adapters/postgresql/database_statements.rb:138:in `block in exec_query'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/activerecord-4.0.10/lib/active_record/connection_adapters/abstract_adapter.rb:442:in `block in log'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/activesupport-4.0.10/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/activerecord-4.0.10/lib/active_record/connection_adapters/abstract_adapter.rb:437:in `log'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/activerecord-4.0.10/lib/active_record/connection_adapters/postgresql/database_statements.rb:137:in `exec_query'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/activerecord-4.0.10/lib/active_record/connection_adapters/postgresql_adapter.rb:915:in `select'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/activerecord-4.0.10/lib/active_record/connection_adapters/abstract/database_statements.rb:25:in `select_all'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/activerecord-4.0.10/lib/active_record/connection_adapters/abstract/query_cache.rb:64:in `select_all'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/activerecord-4.0.10/lib/active_record/querying.rb:36:in `find_by_sql'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/activerecord-4.0.10/lib/active_record/relation.rb:587:in `exec_queries'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/activerecord-4.0.10/lib/active_record/relation.rb:471:in `load'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/activerecord-4.0.10/lib/active_record/relation.rb:220:in `to_a'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/activerecord-4.0.10/lib/active_record/relation.rb:575:in `inspect'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/railties-4.0.10/lib/rails/commands/console.rb:90:in `start'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/railties-4.0.10/lib/rails/commands/console.rb:9:in `start'
from /home/user/.rvm/gems/ruby-2.0.0-p643@railstutorial_rails_4_0/gems/railties-4.0.10/lib/rails/commands.rb:62:in `<top (required)>'
from bin/rails:4:in `require'
from bin/rails:4:in `<main>'2.0.0-p643 :008 > 

---

更新

我尝试了 this question 的答案.我像这样更新了我的 model.rb 文件:

class Playlist < ActiveRecord::Base
  default_scope { order('playlists.id ASC') }
  has_many :playlist_users
  has_many :users, :through => :playlist_users
end

class PlaylistUser < ActiveRecord::Base
  default_scope { order('playlist_users.id ASC') }
  belongs_to :playlist
  belongs_to :user
end

class User < ActiveRecord::Base
  default_scope { order('users.id ASC') }
  has_many :playlist_users
  has_many :playlists, :through => :playlist_users
end

但我仍然得到同样的错误。

Answer 1

问题出在您的分组上。您要根据哪个 id 进行分组？由于您连接了两个表，每个表都有一个 id，postgres 不知道该选择哪个。

您可以尝试将 group(:id) 更改为 group('playlists.id') 或 group('users.id' ).无论您希望对哪个进行分组。