2つのテーブルを使用した検索

ruby sql rails

やや複雑な検索×ページネーションの実装で悩んでいたとき、find_by_sqlなるメソッドの存在とテーブル結合のことを知り、いたく感動したので以下覚書き。
・・・だいぶ前の話ですが（笑）

やりたいこと

  • テーブルとカラム
    • カテゴリ（親テーブル）：：タイトル / 表示順 / ステータス（ON：1　OFF：0）
    • コンテンツ（子テーブル）：：所属カテゴリのID / タイトル / 説明文 / 表示順 / ステータス（ON：1　OFF：0）
  • 検索方法
    • カテゴリ：：タイトルに検索ワードが含まれるとき、そのカテゴリに所属するコンテンツをすべて取得。
    • コンテンツ：：タイトルor本文に検索ワードが含まれ、ステータスがONのとき、そのコンテンツを取得。
    • 複数の検索ワードでも検索可能。
    • 空白文字or空の場合、何も取得しない。
  • ソート方法
    • 所属するカテゴリの表示順 ＞ コンテンツの表示順 （↓こんな感じ）
所属するカテゴリの表示順 コンテンツの表示順
1 1
　 2
　 3
2 1
　 2
： ：

　※ページネーションは端折ります。。
　※エスケープも端折ります。。

find_by_sql(query)メソッド

railsのfindメソッドはとても便利にできているけれど、今回の条件を一発で解決するのは難しいようです。
そこで、SQLクエリをそのまま指定できる【find_by_sql】メソッドを利用します。

　@contents = Content.find_by_sql(query)

query の部分に、上記の条件を満たすクエリ[select * from ...]を渡せばよいわけです。
でも、2つのテーブルを使っている場合、どうやって書けば？？

join構文：テーブル結合

2つのテーブルを結合させ、同時に参照するには、以下のようにjoin句を使います。

　select * from contents join categories

さらに、on句で結合条件を指定することができます。
つまりon句の後ろに、冒頭で書いた条件を満たせるクエリをどんどん足していけばいいわけですね。


では初めに、検索対象とするデータの母体を指定してしまいましょう。
今回検索対象としたいデータは、

  • カテゴリに所属している（contents.category_id が categories.id と等しい）コンテンツ
  • ステータスがONである（contents.status が 1 である）コンテンツ

なので、以下のように条件を指定します。

　select * from contents join categories on contents.category_id = categories.id and contents.status = 1


次は、検索ワードを含むデータを探します。

like構文：キーワード検索

フィールドが文字列を含むかどうか調べるには、like句を使います。

　categories.title like "%hoge%"

検索ワードが複数ある場合や複数のカラムを検索するときは、and/or でlike文を繰り返します。
andは両方のlike文を満たすもの、orは少なくとも一方のlike文を満たすものを探します。

　categories.title like "%hoge%" and categories.title like "%fuga%"
　　⇒カテゴリのタイトルに "hoge"と"fuga"の両方を含む


　contents.title like "%piyo%" or contents.explanation like "%piyo%"
　　⇒コンテンツのタイトルまたは説明文に"piyo"を含む


　(contents.title like "%hoge%" or contents.explanation like "%hoge%") and (contents.title like "%piyo%" or contents.explanation like "%piyo%")
　　⇒コンテンツのタイトル・説明文のいずれかに"hoge"と"piyo"を含む

limit構文：データ数指定

上記のとおりlike構文でキーワード検索すると、検索ワードが空（""）のとき、すべてのデータが返されてしまいます。
ひとつもデータを返さないようにするには、limit句でデータの個数を制限します。

　limit 0

order構文：ソート

join句でテーブル結合してあるので、2種類のカラムによるソートも、カラム名を並べるだけでOKです。

　order by categories.order_number,contents.order_number

rubyコードでクエリ生成

使用する構文はすべて確認できました。
あとはrubyのコードに落とし込み、find_by_sql に渡すだけ！

以下が最終的なコードです。
イモコードですが、これで冒頭の条件はすべて網羅。めでたしめでたし。

  def search

    query = "select * from categories join contents on contents.category_id = categories.id and contents.status = 1"
    #=> テーブル結合。カテゴリに所属し、ステータスがONのコンテンツのみ検索対象とする。

    query_categories = ""

    query_contents = ""

    @keyword = (params[:keyword] ||= "")
    #=> 検索ワードがあれば取得し、なければ "" と見なす。

    kw_split = @keyword.gsub(/　/, ' ').gsub(/\s+/, ' ').split(/ /)
    #=> 複数のキーワードを分割して配列化。
    #   全角スペースを半角スペースに変換 ⇒ 連続する半角スペースは1個分に変換 ⇒ 半角スペースで区切る。

    if kw_split.empty?

      query += " limit 0"
      #=> キーワードの配列が空なら、データをひとつも返さない。
      
    else

      kw_split.each_with_index do |k, idx|
      #=> 分割したキーワードを1つずつクエリに組み込む。

        kw = %Q{"%} + k + %Q{%"}
        #=> キーワードを "%キーワード%" に変換。

        query_categories += "categories.title like " + kw

        query_categories += " and " unless idx == (kw_split.size - 1)
        #=> 最後のキーワードになるまで、like文 and like文 and ... を繰り返す。

        query_contents += "(contents.title like " + kw + " or contents.explanation like " + kw + ")"

        query_contents += " and " unless idx == (kw_split.size - 1)

      end

      unless query_categories == "" && query_contents == ""

        query += " and ((" + query_categories + ") or (" + query_contents + ")) order by categories.order_number,contents.order_number"
        #=> 最初のクエリに追加代入。
        #   検索ワードは、所属カテゴリとコンテンツの両方に含まれている必要はないので、orで繋いでいる。

      end

    end
    
    @contents = Content.find_by_sql(query)

  end


でも、ほんと、イモコード・・・。