はじめに
カラムに対して複数のOR条件を指定したい時ってありますよね。RailsのSQLでOR条件といえばor
が真っ先に出てきますが、他に方法があったりしないのかなと思う機会があると思います。
先日、そんな事があったので、今回はor
を使わず、where
を使ってカラムに対して複数OR条件を指定する方法があるのか調べてみました。
RailsのSQLのwhereについて
そもそも、RailsのSQLのwhere
の引数には大まかにString型
、Array型
、Hash型
の3種類の型を入力することができます。
以下は例に使用するUserテーブルです。
user_id | user_name | user_age |
---|---|---|
1 | Sam | 20 |
2 | John | 50 |
3 | Alice | 27 |
また、動作環境はRails 6.1です。
whereの引数例
String型
User.where("user_name = 'Sam'")
# SELECT * FROM USER WHERE user_name = 'Sam';
# => user_nameがSamのカラムを取得できる
引数を全て文字列で入力した場合です。
公式ドキュメントが言及している通り、この方式はSQLインジェクションに晒される場合があるので、推奨されていません。
Array型
User.where(["user_name = ?", "Sam"])
# SELECT * FROM USER WHERE user_name = 'Sam';
# => user_nameがSamのカラムを取得できる
配列を入力した際は、第一引数をテンプレートとして、残りの引数をテンプレートのプレースホルダ部分(?
部分)に挿入してSQL文が生成されます。
Hash型
User.where(user_name: "Sam")
# SELECT * FROM users WHERE user_name = 'Sam'
# => user_nameがSamのカラムを取得できる
User.where(user_name: ["Sam"])
# SELECT * FROM users WHERE name = 'Sam'
# => user_nameがSamのカラムを取得できる
User.where(user_age: 10..27)
# SELECT * FROM users WHERE user_age BETWEEN 10 AND 27;
# => user_ageが20と27のカラム、つまりuser_nameがSamとAliceのカラムを取得できる
ハッシュを入力した際はキーを指定し、値には文字列、配列、範囲を指定することができます。
本題: カラムに対する複数のOR条件(A or B)
User
テーブルにあるuser_name
がSam
またはJohn
の人をOR条件で取得したい!
上記で紹介した3種類の引数で複数OR条件の絞り込みが可能かそれぞれ検証していきます。
検証を始める前に...
今回求めているのは「カラムに対して複数OR条件」ですが、SQLにはOR
とIN
という挙動が似た存在がいます。今回の場合、「複数OR条件」という通り前者のOR
を求めています。挙動の違いが分かりにくいので、本題の前にそれらの違いについて簡単に話します。
ORとINの違い
SELECT *
FROM User
WHERE (user_name = 'Sam' OR user_age = 50);
-- 同時に複数の項目を対象に条件付けできる
OR
は複数条件のいずれかに合致するカラムを抽出します。例えば、OR
であればuser_name
がSam
または、user_age
が50
の人を取得する。というように異なる項目を条件に設定することができます。
SELECT *
FROM User
WHERE user_name IN ('Sam', 'John')
-- 一度に一つの項目を対象にしか条件付けできない
一方でIN
は指定された値のいずれかに一致するカラムを抽出します。例えば、user_name
がSam
またはJohn
に当てはまる人を取得する事ができます。しかし、IN
ではOR
で可能だったuser_name
がSam
または、user_age
が50
の人を取得する。といったことをIN
単独ではできないです。
String型の場合
うまくいく例
User.where("(user_name = ?) OR (user_name = ?)", "Sam", "John")
# SELECT * FROM User WHERE ((user_name = 'Sam') OR (user_name = 'John'))
上記のコード例ではプレースホルダを用いることで、入力される値がエスケープされます。さらに、検索対象のカラムが固定化されているため、SQLインジェクションに晒される危険性は低いです。また、発行されているSQLを見るとしっかりとOR
条件で発行されているため、今回求めている条件に合致しています。
セキュリティ的に不安が残る例
User.where("(#{params[:column]} = ?) OR (#{params[:column]} = ?)", "#{params[:value]}", "#{params[:value]}")
# 検索対象のカラムが変数になったことで、検索されて欲しくない条件で検索することができるようになってしまう!
上記のようにプレースホルダを使わず、検索対象のカラムがユーザーの入力によって決められるようにすることも可能です。しかし、検索対象のカラムが定まっておらず、SQLインジェクションの危険性が高まることから、非推奨です。
User.where('(user_name = "#{params[:value]}") OR (user_name = "#{params[:value]}")')
# ActiveRecordによって入力された値がエスケープされない!
また、上記のように本来ならプレースホルダが入る位置に文字を入力することも可能です。しかし、この手法ではエスケープが行われず、セキュリティ上万全とは言えません。
Array型の場合
うまくいかない例
User.where(["user_name = ?", ["Sam", "John"]])
# こんな感じじゃダメなのかな...
上記のコードを見て、「あれ?Array型
の例にあったみたいにプレースホルダに対応する場所に配列を入力したら動かないのかな?」と思うかもしれませんが、これではuser_name
がSam
またはJohn
の条件で検索できていません。
上記のコードの場合、user_name
が["Sam", "John"]
という配列全体に等しい場合を検索しようとしています。仮に検索にヒットさせたい場合user_name
に["Sam", "John"]
という値が存在する必要が出てくることになってしまいます。
挙動的にはうまくいくが、ORではない例
User.where(["user_name IN (?)", ["Sam", "John"]])
# SELECT * FROM User WHERE (user_id IN ('Sam', 'John'))
Array型
で今回求めている挙動を満たすことはできましたが、発行されているSQLを見ると、求めていたOR
条件を使用したSQLになっていないようです。そのため、上記のコードは今回の要求を満たしていません。
Hash型の場合
挙動的にはうまくいくが、ORではない例
User.where(user_name: ["Sam", "John"])
# SELECT * FROM User WHERE user_name IN ('Sam', 'John')
Hash型
では今回求めている挙動を満たすことはできます。しかし、発行されるSQLを見るとわかるとおり、IN
が使用されているため、求めていたOR
を使用した動作をしていないことがわかります。
まとめ
# String型
User.where("(user_name = ?) OR (user_name = ?)", "Sam", "John")
今回の検証では、上記のString型
のコードが求めていた条件を満たしてくれるものだとわかりました。
また、Array型
やHash型
も挙動的には正しいですが、求めていたOR
ではなく、IN
を使って絞り込みを行っていたため、要求を完全に満たせるものではありませんでした。
参考記事
論理演算子-MySQL-Documentation
ANY、IN、または SOME を使用したサブクエリー MySQL-Documentation
Rails公式ドキュメント-QueryMethods
安全なウェブサイトの作り方 - 1.1 SQLインジェクション-IPA
「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典-プレースホルダ
「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典-エスケープ文字