Ambiguous 'id' column error - Rails/Postgresql

Question

Not a dupe. I added why at the bottom of the question.

I have two models, Playlist and User, which both have_many of and belong_to each other through a third table PlaylistUser.

For a certain Playlist @playlist, I want to print a list of all other Playlists that share Users with @playlist. Ideally, this list would be ordered by how many Users they share.

This query is meant to do that, but it results in an error:

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

Model

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

I think the problem is that Rails doesn't know which :id column I'm referring to.

How can I resolve this?

Here's the error I get when I run the query in the console:

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

---

UPDATE

I tried the answer to this question. I updated my model.rb files like so:

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

But I still get the same error.

Answer 1

The problem is with your grouping. Which id do you want to group by? Since you have joined two tables, each with an id, postgres does not know which one to pick.

You could try changing group(:id) to group('playlists.id') or group('users.id'). Whichever one you want the grouping to be done on.