Railsを使っている中で、記事のタイトルのようなことがしたくなりました。

例えば、以下のようなことがしたくなりました。

applesとcolorsという2テーブルがある
applesテーブルには以下の3列があり、いずれもcolorsと関連を持たせたい
- 列について
  - 果実の色 (fruit_color)
  - 花の色 (flower_color)
  - 葉の色 (leaf_color)
- 関連について
  - fruit_color と flower_color は、どちらも colors.id に対して外部キー制約をつけたい
  - leaf_color について、列名は leaf_color_name 、参照先は colors.name にしたい
    - 無理に外部キー制約を付けなくても良い

　
そこで、マイグレーション・モデルにどのような設定をすればよいか調べたため、メモを残します。

環境
複数の列の外部キー制約を、同じテーブルに対して行う
外部キー制約の参照先を主キー以外の列にも指定できるか
SQLのJOINが発生するメソッド + select ('*') したときの挙動を確認
ソースコード

環境

Rails 7.1.3

複数の列の外部キー制約を、同じテーブルに対して行う

前述の例で言えば、「applesテーブルの fruit_color と flower_color の各列から、 colors.id に対して外部キー制約を付けたい」を行いたいときのマイグレーションとモデルの定義を確認します。

マイグレーションの定義

今回は一度に定義するのではなく、各ステップごとにマイグレーションを用意します。

まずは apples テーブルがあるとします。

class CreateApples < ActiveRecord::Migration[7.1]
  def change
    create_table :apples do |t|
      t.string :name

      t.timestamps
    end
  end
end

　
次に、 colors テーブルを用意します。

class CreateColors < ActiveRecord::Migration[7.1]
  def change
    create_table :colors do |t|
      t.string :name

      t.timestamps
    end
  end
end

　
続いて、 fruit_color から apples テーブルへの外部キー制約を追加します。

今回は add_reference を使って

applesに追加する列は fruit_color にしたい
- 他の列でも colors テーブルを参照するため、デフォルトで生成される *_id は使いたくない
- add_reference + foreign_key オプションの to_table を使って対応
- add_reference | ActiveRecord::ConnectionAdapters::SchemaStatements
- Railsの外部キー制約とreference型について #Rails - Qiita

な感じで定義します。

class AddColumnToApple < ActiveRecord::Migration[7.1]
  def change
    add_reference :apples, :fruit_color, foreign_key: { to_table: :colors }
  end
end

　
もう一つの列も同様に定義します。

class AddFlowerColorColumnToApple < ActiveRecord::Migration[7.1]
  def change
    add_reference :apples, :flower_color, foreign_key: { to_table: :colors }
  end
end

モデルの定義

続いて、モデルに関連付けを定義します。

今回は、関連付け名はデフォルトではなく、 fruit_color と flower_color にしたいことから、 belongs_to のオプション class_name と foreign_key を使って定義します。

class Apple < ApplicationRecord
  # 各リレーションを分かりやすくするため、belongs_to で別名を付けて、 class_name で関連先のモデル名を指定している
  belongs_to :fruit_color, class_name: 'Color', foreign_key: 'fruit_color_id'
  belongs_to :flower_color, class_name: 'Color', foreign_key: 'flower_color_id'
end

動作確認

以上でマイグレーションとモデルの定義ができました。

そこで、テストコード (model spec + factory_bot) を書いて動作確認します。

まずはテストデータです。

RSpec.describe Apple, type: :model do
  let!(:yellow_color) { create(:color, :yellow_color) }
  let!(:white_color) { create(:color, :white_color) }

  let!(:shinano_gold) { create(:apple,
                               name: 'シナノゴールド',
                               fruit_color: yellow_color,
                               flower_color: white_color)}
end

　
次に、 apples から colors をたどれるか確認します。今回は、 apples から colors.name を取得できるか確認します。

また、取得方法も

ドット(関連付け)で取得
eager_load で取得
joins + select で取得

のパターンをためせるテストコードを書きます。

describe 'fruit_color' do
  it 'ドットで取得できること' do
    p Apple.find_by(name: 'シナノゴールド').fruit_color.name
    actual = Apple.find_by(name: 'シナノゴールド').fruit_color.name
    expect(actual).to eq('黄')
  end

  it 'eager_load + ドットで取得できること' do
    actual = Apple.eager_load(:fruit_color).where(name: 'シナノゴールド' ).first.fruit_color.name
    expect(actual).to eq('黄')
  end

  it 'joins + select で取得できること' do
    actual = Apple.joins(:fruit_color).where(name: 'シナノゴールド').select('colors.name').first
    expect(actual.name).to eq('黄')
  end
end

describe 'flower_color' do
  it 'ドットで取得できること' do
    actual = Apple.find_by(name: 'シナノゴールド').flower_color.name
    expect(actual).to eq('白')
  end

  it 'eager_load + ドットで取得できること' do
    actual = Apple.eager_load(:flower_color).where(name: 'シナノゴールド' ).first.flower_color.name
    expect(actual).to eq('白')
  end

  it 'joins + select で取得できること' do
    actual = Apple.joins(:flower_color).where(name: 'シナノゴールド').select('colors.name').first
    expect(actual.name).to eq('白')
  end
end

　
テストコードを実行してみると、いずれのテストもパスしました。

外部キー制約の参照先を主キー以外の列にも指定できるか

ここまでで、複数の列で同じテーブルを参照するときの外部キー制約をためしてみました。

ただ、データベースによっては外部キー制約を主キー以外にも設定できます。

そこで、Railsの場合にはどのように設定するかを調べてみました。

調査

マイグレーションでは「生SQLを書く」以外の方法が分からず

Rails APIのドキュメントを見ながら、主キー以外の列を参照する外部キー制約が付けられるか試してみました。

　
しかし、主キーを参照する前提のようだったため、Rails API を使って記述することはできませんでした。

生SQLを書けばいけるかもしれませんが、レールを外れそうだったのと、データベースによってはうまく動作しないようでした。

例えばMySQLの場合、

UNIQUE でないキーを参照する FOREIGN KEY 制約は、標準 SQL ではなく InnoDB の拡張機能です。一方、NDB ストレージエンジンでは、外部キーとして参照される任意のカラムに明示的な一意キー (または主キー) が必要です。

　

一意でないキーまたは NULL 値を含むキーへの外部キー参照の処理は、UPDATE や DELETE CASCADE などの操作に対して適切に定義されていません。 UNIQUE (PRIMARY を含む) および NOT NULL キーのみを参照する外部キーを使用することをお勧めします。

　
https://dev.mysql.com/doc/refman/8.0/ja/ansi-diff-foreign-keys.html

との記載があります。

　
そのため、今回マイグレーションで外部キー制約を付与する、つまりデータベースレイヤでデータを保護するのは諦めました。

モデルで belongs_to を使って、外部キー制約なしの関連付けする

前述の通りデータベースレイヤでは諦めましたが、Railsレイヤで行えることがあるかもしれないと思い、調べてみました。

すると、モデルで belongs_to + class_name + foreign_key + primary_key を使えば関連付けができそうでした。

　
気になるのは、「primary_key に主キー以外の項目を設定してもよいのか」ですが、以下の記事やソースコードを見るとうまく動きそうな気がします。

実装

では実際にためしてみます。

まずはマイグレーションで、 apples テーブルに string 型の leaf_color_name 列を追加します。

class AddLeafColorColumnToApple < ActiveRecord::Migration[7.1]
  def change
    add_column :apples, :leaf_color_name, :string
  end
end

　
続いて、モデル Apple で、 belongs_to に

foreign_key に、Appleの属性である leaf_color_name を指定
primary_key に、参照先のColorの属性である name を指定

の各オプションを渡して関連付けを定義します。

class Apple < ApplicationRecord
  # name列同士の関連付けをもたせる
  belongs_to :leaf_color, class_name: 'Color', foreign_key: 'leaf_color_name', primary_key: 'name'
end

動作確認

では、先ほどの外部キー制約があるときと同様、テストコードを書いて動作を確認してみます。

以下のテストコードを書いて実行したところ、いずれもテストがパスしました。

describe 'leaf_color_name' do
  it 'ドットで取得できること' do
    actual = Apple.find_by(name: 'シナノゴールド').leaf_color.name
    expect(actual).to eq('緑')
  end

  it 'eager_load + ドットで取得できること' do
    actual = Apple.eager_load(:leaf_color).where(name: 'シナノゴールド' ).first.leaf_color.name
    expect(actual).to eq('緑')
  end

  it 'joins + select で取得できること' do
    actual = Apple.joins(:leaf_color).where(name: 'シナノゴールド').select('colors.name').first
    expect(actual.name).to eq('緑')
  end

  it 'joins + pluck で取得できること' do
    actual = Apple.joins(:leaf_color).where(name: 'シナノゴールド').pluck('colors.name').first
    expect(actual).to eq('緑')
  end
end

実際に発行されるSQLを確認

テストはパスしたものの、実際に発行されるSQLのJOINの条件が気になりました。

そこで、JOINが発生する

eager_load
joins

の各メソッドにて、実際に発行されるSQLを確認してみます。

eager_loadのときのSQL

LEFT OUTER JOIN の ON 句で "colors"."name" = "apples"."leaf_color_name" となっていました。

SELECT
  "apples"."id" AS t0_r0,
  "apples"."name" AS t0_r1,
  "apples"."created_at" AS t0_r2,
  "apples"."updated_at" AS t0_r3,
  "apples"."fruit_color_id" AS t0_r4,
  "apples"."flower_color_id" AS t0_r5,
  "apples"."leaf_color_name" AS t0_r6,
  "colors"."id" AS t1_r0,
  "colors"."name" AS t1_r1,
  "colors"."created_at" AS t1_r2,
  "colors"."updated_at" AS t1_r3
FROM
  "apples"
  LEFT OUTER JOIN "colors" ON "colors"."name" = "apples"."leaf_color_name"
WHERE
  "apples"."name" IN ("name", "シナノゴールド")
ORDER BY
  "apples"."id" ASC
LIMIT
  1

joinsのときのSQL

こちらも、INNER JOIN の ON 句で "colors"."name" = "apples"."leaf_color_name" となっていました。

SELECT
  "colors"."name"
FROM
  "apples"
  INNER JOIN "colors" ON "colors"."name" = "apples"."leaf_color_name"
WHERE
  "apples"."name" IN ("name", "シナノゴールド")
ORDER BY
  "apples"."id" ASC
LIMIT
  1

　
以上より、主キー以外の属性でも関連付けができました。

SQLのJOINが発生するメソッド + select ('*') したときの挙動を確認

ところで本題とはズレるのですが、join系メソッドを調べていた時に以下の記事とGithubへのリンクを見つけました。

【ActiveRecord】結合したテーブルに同名カラムがある場合 select('*') を使用すると違うモデルにマッピングされる
- リンクされていたissueやプルリク
  - Join table data assigned to Model · Issue #41151 · rails/rails
  - Warn on instantiating records w/ ambiguous columns by khasinski · Pull Request #44236 · rails/rails

　
そのissueやプルリクはまだcloseしていなかったため、Rails 7.1系ではどのような結果になるか試してみたくなりました。

そこでテストコードを書いてみたところ、いずれもパスしました。

describe "applesとcolorsでname列が重複しているときの join系 + select('*')の挙動" do
  context 'eager_loadの時' do
    it 'apple.nameを期待したいが、color.nameになっていること' do
      actual = Apple.eager_load(:fruit_color).where(name: 'シナノゴールド' ).select('*').first

      expect(actual.name).not_to eq('シナノゴールド')
      expect(actual.name).to eq('黄')
    end
  end

  context 'left_joinsの時' do
    it 'apple.nameを期待したいが、color.nameになっていること' do
      actual = Apple.left_joins(:fruit_color).where(name: 'シナノゴールド' ).select('*').first

      expect(actual.name).not_to eq('シナノゴールド')
      expect(actual.name).to eq('黄')
    end
  end

  context 'joinsの時' do
    it 'apple.nameを期待したいが、color.nameになっていること' do
      actual = Apple.joins(:fruit_color).where(name: 'シナノゴールド' ).select('*').first

      expect(actual.name).not_to eq('シナノゴールド')
      expect(actual.name).to eq('黄')
    end
  end
end