Many To Many (through Pivot / Junction)

Preparing your DB schema

For defining many_to_many(m2m) relations you need to define aPivot(junction) table and a foreign_key / unique constraint(index).

Naming of the Pivot table requires models to be ordered alphabetically and in singular form. For instance a Pivot table between the models Tag and user should be named tag_user.

• ⭕ tag_user

• ❌ user_tag -> Not ordered alphabetically

• ❌ tag_users -> Not in singular form

• ❌ tags_users -> Not in singular form

Let's define a User has_many Tags through "tag_user" pivot table (many_to_many) relation for example.

class AddUsers < ActiveRecord::Migration[7.0]
  def change
    create_table :users do |t|
      # "id" primary key column will also be added implicitly by Rails(ActiveRecord)
      t.string :email, null: false
    end
  end
end

class AddTags < ActiveRecord::Migration[7.0]
  def change
    create_table :tags do |t|
      t.string :label, null: false
    end

    # Use "label" as the primary key column
    add_index :tags, :label, unique: true
  end
end

class AddTagUser < ActiveRecord::Migration[7.0]
  def change
    create_table :tag_user do |t|
      t.string :tag_label, null: false
      t.bigint :user_id, null: false
    end

    # Foreign key to "Tag" table, 
    # references "tags.label" instead of default "tags.id" in this case
    add_foreign_key :tag_user, :tags, column: :tag_label, primary_key: :label
    
    # Foreign key to "User" table.
    add_foreign_key :tag_user, :users, column: :user_id

    # Pivot table need to have a "multiple unique constraint" for both tables' columns.
    add_index :tag_user, [:tag_label, :user_id], unique: true
  end
end

Resulting Model Relations

With the DB setup above, EzQL will generate the following Model Relations.

# Code generated by EzQL, DO NOT EDIT.
class User < ApplicationRecord
  has_many :tag_user, foreign_key: :user_id, dependent: :destroy
  has_many :tags, through: :tag_user
end

# Code generated by EzQL, DO NOT EDIT.
class TagUser < ApplicationRecord
  self.table_name = "tag_user"

  belongs_to :tag, foreign_key: :tag_label, primary_key: :label
  belongs_to :user, foreign_key: :user_id
end

# Code generated by EzQL, DO NOT EDIT.
class Tag < ApplicationRecord
  has_many :tag_user, foreign_key: :tag_label, primary_key: :label, dependent: :destroy
  has_many :users, through: :tag_user
end