Rails Many to Many with Extra Column

Question

So I have these tables:

create_table :users do |t|
  t.string :username
  t.string :email
  t.string :password_digest

  t.timestamps
end

create_table :rooms do |t|
  t.string :name
  t.string :password
  t.integer :size
  t.integer :current_size

  t.timestamps
end

create_table :rooms_users do |t|
  t.belongs_to :user, index: true
  t.belongs_to :room, index: true

  t.boolean :is_admin

  t.timestamps
end

I made it so, when I call Room.find(1).users I get a list of all the users in the room. However, I also want to be able to call something like Room.find(1).admins and get a list of users that are admins (where is_admin in rooms_users is true). How would I do that?

Thank you for your time!

Answer 1

You want to use has_many through: instead of has_and_belongs_to_many. Both define many to many associations but has_many through: uses a model for the join rows.

The lack of a model makes has_and_belongs_to_many very limited. You cannot query the join table directly or add additional columns since the rows are created indirectly.

class User < ApplicationRecord
  has_many :user_rooms
  has_many :rooms, through: :user_rooms
end

class Room < ApplicationRecord
  has_many :user_rooms
  has_many :users, through: :user_rooms
end

class UserRoom < ApplicationRecord
  belongs_to :user
  belongs_to :room
end

You can use your existing schema but you need to rename the table users_rooms to user_rooms with a migration - otherwise rails will deride the class name as Rooms::User.

class RenameUsersRooms < ActiveRecord::Migration[5.0]
  def change
    rename_table(:users_rooms, :user_rooms)
  end
end

Also if the table doesn't have a primary key column it's a good idea to add it.

However, I also want to be able to call something like Room.find(1).admins and get a list of users that are admins (where is_admin in rooms_users is true). How would I do that?

You want to use a left inner join:

User.joins(:user_rooms)
    .where(user_rooms: { room_id: 1, is_admin: true })

To roll that into the class you can setup an association with a scope applied:

class Room < ApplicationRecord
  has_many :user_rooms
  has_many :users, through: :user_rooms
  has_many :user_room_admins, class_name: 'UserRoom', ->{ where(is_admin: true) } 
  has_many :user_room_admins, through: :user_rooms, 
    class_name: 'User',
    source: :user
end