【Rails】テーブルに別の同一テーブル2つを結合させて検索する方法

今回はActiveRecordを使って検索機能実装している際にハマったポイントに関して書いていこうと思います。

実装する検索機能
実装方法
アンチパターン
さいごに

実装する検索機能

今回例に出すサンプルのデータベースは以下のようになります。homeworksテーブルは生徒のIDと先生のIDをそれぞれ保持しています。

これを先生の名前と生徒の名前それぞれで検索ができるようにします。具体的にはクエリパラメーターとしてteacher_nameとstudent_nameが送られてきてそれを使って検索するようにします。
それでは実装してみます。

実装方法

まずはコントローラーから書いていきます。今回はindexメソッドの中で検索を行うことにします。

class HomeworksController < ApplicationController

  def index
    @homeworks = Homework.search(search_params)
  end

  # 略

  private

  def search_params
    permit.permit(:student_name, :teacher_name)
  end

コントローラーではindexメソッドで、searchメソッドを実行しています。では、searchメソッドの実装に入ります。

class Homework < ApplicationRecord
  belongs_to :student, class_name: 'User', optional: true
  belongs_to :teacher, class_name: 'User', optional: true

  scope :search, lambda { |search_params|
    teacher_name = search_params[:teacher_name]
    student_name = search_params[:student_name]

    joins(
      'LEFT OUTER JOIN users AS student_users ON student_users.id = homeworks.student_id
      LEFT OUTER JOIN users AS teacher_users ON teacher_users.id = homeworks.teacher_id
    ')
    .where('teacher_users.name LIKE(?) or teacher_users.name LIKE(?)', "%#{teacher_name}%", "%#{teacher_name}%")
    .where('student_users.name LIKE(?) or student_users.name LIKE(?)', "%#{student_name}%", "%#{student_name}%")

end

まずは、joinsメソッドで生のSQLを書くやり方です。生徒のテーブルと先生のテーブルをそれぞれ結合させています。生徒も先生も実際には結合させるテーブルはusersテーブルで同じなので、生徒のusersテーブルはstudent_users、先生のテーブルはteacher_usesというテーブル名をつけています。

こうすることで、where句の中でstudent_usersやteacher_usersというテーブル名を使って検索することができています。

実際に発行されるSQLは下記になります。

SELECT "homeworks".* FROM "homeworks" 
LEFT OUTER JOIN users AS student_users ON student_users.id = homeworks.student_id
LEFT OUTER JOIN users AS teacher_users ON teacher_users.id = homeworks.teacher_id 
WHERE (teacher_users.name LIKE('%teacher%') or teacher_users.name LIKE('%teacher%')) 
AND (student_users.name LIKE('%%') or student_users.name LIKE('%%'))

これで、実装自体は完了なのですが、僕がやってしまったアンチパターンを紹介しておこうと思います。

アンチパターン

僕がやってしまったアンチパターンはこちらです。

left_joins(:teacher).select('homeworks.*, users.name AS teacher_name')
      .left_joins(:student).select('users.name AS student_name')
      .where('teacher_name LIKE(?)', "%#{teacher_name}%")
      .where('student_name LIKE(?)', "%#{student_name}%")

一見するとこれでも検索できそうですが、2つ目のselectメソッドでのエイリアスの設定がうまくいっていません。それを証拠に生徒名で検索すると何も返ってきません。

これで発行されるSQLは以下です。

SELECT homeworks.*, users.name AS teacher_name, users.name AS student_name FROM "homeworks" 
LEFT OUTER JOIN "users" ON "users"."id" = "homeworks"."teacher_id" 
LEFT OUTER JOIN "users" "students_homeworks" ON "students_homeworks"."id" = "homeworks"."student_id" 
WHERE (teacher_name LIKE('%1%')) AND (student_name LIKE('%%'))

うまく検索できない理由としては生徒用のusersテーブルにはエイリアスが設定されず、ActiveRecordが動的にエイリアスを設定してしまっているためです。なので、動的に設定されているものを使うという方法で検索できるようにすることができます。

left_joins(:teacher).select('homeworks.*, users.name AS teacher_name')
      .left_joins(:student).select('students_homeworks.name AS student_name')
      .where('teacher_name LIKE(?)', "%#{teacher_name}%")
      .where('student_name LIKE(?)', "%#{student_name}%")

しかし、上の記述ができるのはデータベースがSQLite3のときのみです。通常のRDBMSでは、SELECT句よりWHERE句の方が早く評価されるので、SELECT句でエイリアスを設定しても意味がありません。そのため、MYSQLなどを使っている場合は書き直す必要があります。

left_joins(:teacher, :student)
      .where('users.name LIKE(?)', "%#{teacher_name}%")
      .where('students_homeworks.name LIKE(?)', "%#{student_name}%")

簡潔ですが、ActiveRecordが動的に設定するエイリアスを使っているので、Railsのバージョンが上がれば動かなくなる可能性がありますし、テーブル設計に変更があった場合にも同様に動かなくなる可能性があります。また、何より読みづらいので、基本的にはjoinsメソッドで生のSQLを書くのが一番いいやり方なのではないかと今の時点では思っています。