背景

レシピ投稿サイトを作っていたときに、検索結果を「新着順」と「人気順（ランキング）」で表示させたいと思い、実装を進めましたが、途中人気順の表示でつまづいたので、どのように実装したかSQLの復習も兼ねて整理しておきたいと思います。

前提

ERD
検索にはgem ransackを使用
人気順＝いいねの数（Like）とし、いいねの多い順で検索結果を表示する
環境
ruby 2.6.3
rails 5.2.6
OS：Linux(CentOS)
IDE：Cloud9

実装

事前準備として、まず、ransackを導入します。

ransackはRailsアプリケーションで検索機能を簡単に作成できるライブラリです。
Gemfileに追加して、保存したらbundle installします。

Gemfile

gem 'ransack'

1. 検索の処理をコントローラー記述

今回、検索窓はヘッダーに配置するのでapplication_controller.rbに検索の処理を記述します。

ransackの仕組み

ransackではparams[:q]でユーザーが入力した検索ワードを受け取り、その検索ワードに該当するデータをインスタンス変数に格納させて、ビューで返します（ちなみに、:qはransack側のデフォルト値ですが、変更も可能だそうです）。

例えば「検索ワードに該当するレシピ投稿を、新着順で表示する」だけなら、このようにシンプルに実装できます👇

app/controllers/application_controller.rb

  def recipe_search
    @search = PostRecipe.ransack(params[:q])
    @latest_recipes = @search.result(distinct: true).order(created_at: "DESC")
  end

人気順（いいね順）を実現する

さて、今回は新着順に加えて、人気順（いいね数順）でも表示したいので、もう少し手を加える必要があります。

やりたいこととしては「PostRecipeに紐づいているLikeが最も多い順に、PostRecipeを取り出す」ということになるので、それぞれのレシピ投稿に紐づく「いいね」数の取得が必要になります。

そこで、LEFT OUTER JOIN(左外部結合）の中で副問い合わせ（サブクエリ）を使います。

統合/内部統合/外部統合とは

- 「結合」＝結合条件に従って複数のテーブルを一つのテーブルとして結合させる。テーブルを丸ごと繋ぐのではなく、結合条件が満たされた行を一つずつ繋いでくれる。結合元のテーブルを左表（例：post_recipesテーブル）、JOIN句で左表に結合するテーブルを右表（例：likesテーブル）と表現する。左表に繋ぐべき右表の行が複数あるときは、左表の行を複製して結合する。
- 「内部結合」＝ INNER JOINでは、結合すべき相手の行が見つからない場合に、結合結果から行が削除される。つまり、右表に結合相手の行がない場合や、左表の結合条件の列がNULLの場合、該当行は結合結果から消滅される。
- 「外部結合」＝ OUTER JOINは、結合相手が見つからない場合でも、左表もしくは右表の値を結合結果に出力したい場合に使用する。例えば、左外部結合（LEFT OUTER JOIN)であれば、右表に結合相手が見つからなかったり、左表の行がNULLであっても、左表の行が結合によって失われることがなくなる。

副問い合わせとは

- 入れ子になったSQL文における内側のSQL文のこと（＝他のSQL文の一部分として登場するSELECT文）。
- 内側にある副問い合わせのSELECT文がまず実行されて結果に化け、外側のSQL(主問い合わせ）が実行されることで、最終的な結果を得る。
- 副問い合わせは副照会、サブクエリともいう。主問い合わせはメインクエリとも呼ぶ。

app/controllers/application_controller.rb

  def recipe_search
    @search = PostRecipe.includes(:user).where(is_draft: false).joins(%|
      LEFT OUTER JOIN (
        SELECT
          `likes`.`post_recipe_id` AS post_recipe_id,
          COUNT(*) AS like_count
        FROM
          `likes`
        GROUP BY
          `likes`.`post_recipe_id`
      ) AS post_recipe_like_count
      ON post_recipes.id = post_recipe_like_count.post_recipe_id
    |).ransack(params[:q])
    @latest_recipes = @search.result(distinct: true).order(created_at: "DESC")
    @popular_recipes = @search.result(distinct: true).order(like_count: "DESC")
  end

一つずつ内容を整理していきます：

joinsメソッドは、「関連するテーブル同士を内部結合するメソッド」です。この中に、SQLを直書きしています。「ActiveRecordでサブクエリ（副問い合わせ）と内部結合」を参考にさせていただきました。（ActiveRecordのメソッドでも実装できるとは思うのですが、今回はSQLの勉強も兼ねて上記の記事にもあった直書きで実装してみました）
joinsメソッドの中で左外部結合（LEFT OUTER JOIN）を使用します。post_recipesテーブルのidが結合条件になりますが（ON post_recipes.id = post_recipe_like_count.post_recipe_id）、内部結合にしてしまうと、「いいね」が1件も付いていない投稿は検索結果（結合結果）に表示されない（結合条件に合うレコードがlikesテーブルに存在せず、結合結果から削除される）ため、左外部結合を使用する必要があります。
副問い合わせは、LEFT OUTER JOIN内のSELECT〜AS post_recipe_like_countまでの部分です。副問い合わせでは、likesテーブルのレコードをpost_recipe_idでグループ化（GROUP BY）し、そのpost_recipe_idごとのlikesテーブルのレコード件数を、COUNT関数で取得しています。この副問い合わせの取得結果をpost_recipe_like_countとし、post_recipesテーブルに左外部結合します。これによって、レシピ投稿に紐づくいいね件数を取得でき、orderメソッドで降順に並び替えることができます。
includes(:user)はN+1問題（ループ処理の中でSQLを都度発行してしまい、大量のSQLが発行されパフォーマンスが低下してしまう問題）への対処として、where(is_draft: false)は下書きではなく公開されたレシピのみを取得するための記述です。これらは主問い合わせの部分にあたります。

2. 検索窓をつくる

ヘッダーに検索窓を作ります。

app/views/layouts/_header.html.erb

 <%= search_form_for @search, url: post_recipes_searches_path do |f| %>
   <%= f.search_field :title_cont, placeholder: 'レシピを検索', class: 'input', size: '22' %>
   <%= f.submit "GO!", id:'search-btn' %>
 <% end %>

ransackのヘルパーメソッドsearch_from_forを使用します。
:title_contの:titleの部分は、検索対象となるカラム名を指定します。今回はレシピ名なのでtitleにしています。検索オプションは_cont以外にも色々公式のGithubに載っています。
どの画面からヘッダーの検索窓で検索をしても、searches/index.html.erb（検索結果のビュー）が返るよう、パスを明記します。

3. 検索結果を表示する

最後にインスタンス変数にそれぞれ格納した新着投稿（@latest_recipes）と、人気順投稿（@popular_recipes）を取り出してあげます。

タイトルの部分は、検索した値が表示されるようにしていますが、何も検索窓に入力せずに検索結果に飛んだ場合は、全ての投稿が表示されるようになっており、このとき「（空欄）のレシピ」と表示されてしまいやや不恰好です。このため、params[:q]に値が渡っているかをif文で調べ、値がない場合は違う文字列が表示されるようにしています。

app/views/searches/index.html.erb

  <!--検索結果のタイトル-->
  <% if params[:q].present? && params[:q][:title_cont].present? %>
    <h3>「<%= params[:q][:title_cont] %>」のレシピ</h3>
  <% else %>
    <h4>気になるレシピを検索</h4>
  <% end %>

  <!--新着順の検索結果-->
  <% @latest_recipes.each do |latest_recipe| %>
    <div>
      <%= link_to latest_recipe.title, post_recipe_path(latest_recipe), class:'card-text text-wrap mt-3 jp-font-pop' %>
      <p>by<%= link_to latest_recipe.user.name, user_path(latest_recipe.user) %></p>
    </div>
  <% end %>

 <!--人気順の検索結果-->
  <% if user_signed_in? && current_user.is_paid == true %>
    <% @popular_recipes.each do |popular_recipe| %>
      <%= link_to popular_recipe.title, post_recipe_path(popular_recipe), class:'card-text' %>
      <p>by<%= link_to popular_recipe.user.name, user_path(popular_recipe.user), class:'card-text text-wrap font-color-green' %></p>
    <% end %>
  <% else %>
    <p>有料会員の機能です。<%= link_to "こちら", premiums_payment_path, class:"font-color-pink" %>より有料会員登録をお願いいたします。</p>
  <% end %>

※実際はBootstrapでタブ切り替えをしていますが、上記のソースコードでは省略しています。
※人気順表示は有料会員の機能（is_paid）として実装したので、有料会員でない場合は表示されないよう、if文で分岐させています。

ちなみに検索に関係するルーティングはこちら👇

config/routes.rb

get 'post_recipes/searches' => 'searches#index'

以上です！

参考資料

『スッキリわかるSQL入門第２版』中山清喬・飯田理恵子
【Rails】ransackで検索機能を実装する(ヘッダーにも検索窓を設置するときの対応)
Rails における内部結合、外部結合まとめ
ActiveRecordでサブクエリ（副問い合わせ）と内部結合
【Rails】 joinsメソッドのテーブル結合からネストまでの解説書
ransack

【Rails x SQL】検索結果を人気順で表示する（左外部結合と副問い合わせ）

背景

前提

実装