Tricky Rails migration and modeling involving a self-join ":users" table and a ":bands" table: simultaneous one-to-many and many-to-many relationships

Question

My :users table is successfully self-joined with all the necessary confusing (to this newbie) code and tables necessary to do that. :users's two groups are :teachers and :students.

I need to make the group :teachers join one-to-many with the :bands table (a band may have only one teacher) while at the same time joining :students many-to-many with the :bands table (a band may have many students and vice versa).

What's tripping me up is :students and :teachers are both :users. Therefore, if for a moment I pretend that there's only one kind of user and go for a one-to-many (teacher) relationship, then the Band model belongs_to :user and the User model has_many :bands

But if I go for the many-to-many (student) relationship instead, the Band model has_many :users, through :user_bands join table and the User model has_many :bands, through :user_bands join table. (UserBands model has belongs_to :user and belongs_to :band in this case)

But I need both relationships at the same time. I haven't actually tried putting has_many :bands in the User model while simultaneously having has_many :users (through join table) and belongs_to :users in the Bands model because, unless Rails is more magic than I give it credit for, it won't differentiate that teachers get the single-to-many while the students get the many-to-many.

I have not attempted my best guess (below) because I'm admittedly skittish: my database already has a sprawling number of many-to-many relations that are intact and functioning properly. The one time I attempted to make a complicated alteration to it earlier in the process, it thoroughly messed things up so badly that rolling back and undoing model alterations didn't get me back to where I'd started somehow, so I had to go back to rebuilding everything from scratch after pulling out enough hair for a tonsure. I do have github this time, so I should be able to revert the project if it blows up like before, but git is its own fussy minefield.

So if some folks could take a look at my guess first, I'd deeply appreciate it. Does it look right? Do I need to make changes before updating the database schema?:

1. In User model, add has_many :bands.
2. In Band model, add has_many :students, through :user_bands ; add belongs_to :teacher
3. In the create_bands migration, add t.belongs_to :teacher
4. In UserBands model, add belongs_to :teacher and add t.belongs_to :teacher in the create_user_bands migration.

Answer 1

The needed associations are not self-joining. Self-joins are primarily used to build tree like hierarchies from a single table - see the guides for a good example.

You just need multiple associations between two tables - the key thing here to remember is that you must use unique names for each association. If you declare multiple associations with the same name the later just overwrites the former without error.

Teachers

class AddTeacherIdToBands < ActiveRecord::Migration[5.2]
  def change
    add_reference :bands, :teacher, foreign_key: { to_table: :users }
  end
end

class User < ApplicationRecord
  has_many :bands_as_teacher, 
    class_name: 'Band',
    foreign_key: 'teacher_id'
end

class Band < ApplicationRecord
  belongs_to :teacher, 
    class_name: 'User'
end

We name the association bands_as_teacher to avoid conflict and confusion. This requires us to explicitly set the class_name and foreign_key options as they cannot be deduced from the name.

This is kind of where you tripped up and overcomplicated your solution. If the association is one to many you don't need to involve a join table.

Students

To create the association between a band and its members you need a m2m association through a join table:

class CreateBandMemberships < ActiveRecord::Migration[5.2]
  def change
    create_table :band_memberships do |t|
      t.references :band, foreign_key: true
      t.references :user, foreign_key: true
      t.timestamps
    end
  end
end

class Band < ApplicationRecord
  # ...
  has_many :band_memberships
  has_many :users, through: :band_memberships
end

class BandMembership < ApplicationRecord
  belongs_to :band
  belongs_to :user
end

class User < ApplicationRecord
  # ...
  has_many :band_memberships
  has_many :bands, through: :band_memberships
end

You can improve the naming by providing the source option which tells Rails which association to use on the model its joining through.

class Band < ApplicationRecord
  # ...
  has_many :band_memberships
  has_many :members, through: :band_memberships, source: :user
end

class User < ApplicationRecord
  # ...
  has_many :band_memberships
  has_many :bands_as_member, through: :band_memberships, source: :band
end