[Rails][SQL] RailsでLEFT OUTER JOINする際のメモ

RailsでLEFT OUTER JOINをしてレコードを検索する必要がありました。
ただクエリ文はすぐにイメージがついたのですが、Railsでどう表現するかを少し戸惑ったのでメモを残します。
テーブル例はかなり適当に書いたので、テーブルのアソシエーションからJOINする箇所が参考になるかと思います。

環境

• Rails 7.0.3

テーブル

Railsのschema例ですがTaskテーブル、SubTaskテーブルにアソシエーションがあります。
そしてSubTaskテーブルはtypeカラムがあり、SubTaskテーブルのレコードにタイプが複数あります。

  # Tasksテーブル
  create_table "tasks", force: :cascade do |t|
    t.string "title", null: false
    t.timestamps
  end

  # SubTasksテーブル
  create_table "sub_tasks", force: :cascade do |t|
    t.string "title", null: false
    t.string "task_id", null: false
    t.string "type", null: false, default: "TypeA"
    t.string "type_id", null: false
    t.timestamps
  end

  # Type_aテーブル
  create_table "type_as", force: :cascade do |t|
    t.string "title", null: false
    t.timestamps
  end

  # Type_bテーブル
  create_table "type_bs", force: :cascade do |t|
    t.string "title", null: false
    t.timestamps
  end

モデル

テーブル定義をモデルに反映しています。
そしてselected_type_a_categoryを定義しています。
selected_type_a_categoryはSubTaskモデルにアソシエーションとして追加しています。
内容としてはTypeAモデルとアソシエーションがあるSubTaskのみを取得する意図です。

class Task < ApplicationRecord
  belongs_to :sub_task
end

class SubTask < ApplicationRecord
  has_many :tasks, dependent: :destroy
  belongs_to :type_a, polymorphic: true
  belongs_to :selected_type_a_sub_task, -> {
    where(sub_tasks: { type: 'TypeA' })
  }, class_name: 'TypeA', foreign_key: 'type_id'
end

class TypeA < ApplicationRecord
  has_many :sub_tasks, as: :type_a
end

class TypeB < ApplicationRecord
  has_many :sub_task, as: :type_a
end

LEFT OUTER JOINでデータ取得

SubTaskからTypeAとタイトル名で一致するレコードを取得したい場合に、以下のように取得します。

Task
  .joins(:sub_tasks)
  .merge(
    SubTask
      .left_outer_joins(:selected_type_a_category)
      .where(type: 'TypeA') # .where(type: 'TypeA', type_a: { type_aテーブルで追加したい条件があれば追加 })
      .or(SubTask.where(title: 'hoge_title'))
  )

具体的には以下のようなSQLが発行されます。

SELECT tasks.*
FROM tasks
INNER JOIN sub_tasks ON sub_tasks.id = tasks.sub_task_id
LEFT OUTER JOIN type_as AS selected_type_a_categories 
  ON selected_type_a_categories.type_id = sub_tasks.type_id
  AND selected_type_a_categories.type = 'TypeA'
WHERE (sub_tasks.type = 'TypeA' OR sub_tasks.title = 'hoge_title');

INNER JOINでデータ取得

SubTaskからTypeAかつタイトル名が一致するレコードを取得したい場合に、以下のように取得します。

Task
  .joins(sub_tasks: :selected_type_a_category)
  .merge(
    SubTask.where(type: 'TypeA', title: 'hoge_title')
  )

具体的には以下のようなSQLが発行されます。

SELECT tasks.*
FROM tasks
INNER JOIN sub_tasks ON sub_tasks.id = tasks.sub_task_id
INNER JOIN type_as AS selected_type_a_categories
  ON selected_type_a_categories.type_id = sub_tasks.type_id
  AND selected_type_a_categories.type = 'TypeA'
WHERE sub_tasks.title = 'hoge_title';

まとめ

RailsでLEFT OUTER JOINを表現するとこのようになります。
今回試している中で思ったのが、
RailsはJOINする条件に絞り込み条件がもう少し書けたら効率的なクエリ（WHERE句の条件が減らせる）になるなと思いました。