【Rails】PostgreSQLでORDER BY FIELD句を実現したい

本記事は弊社内アドベントカレンダーで書いたものです。

概要

MySQLの FIELD() 句が便利よね。PostgreSQLでも使いたいわね。
モデルはRailsを使用しているからいい感じに使えるようにしたいわ。

「「「DBでできることはDBでやりたいんや」」」って 人向け。
利点としては LIMIT 句を併用できること。←←まずは全件メモリ載せ！みたいなのがない故データ量が多い場合に節約になるねん

背景

クエリ実行時にソート順を指定するとき、MySQLでは ORDER BY FIELD() 句を使用して任意の順番(例えば「赤」→「青」→「黄」など)で結果を並び替えることが出来る。PostgreSQLではできない
https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_field
RailsアプリでMySQLのDBを使用している人はお世話になったことも多いことだろう。Rails関係ないけど。

しかしRailsで使用するDBはMySQLだけとは限らぬ。プロジェクトによってはPostgreSQLが採用されており、便利関数 FIELD() 句が使えない場合だって ある あった。
お仕事では"仕様"の都合でこれを実現したい！！なんてことも少なくないだろう。これはただの経験則である。

ずいぶん前に実装したものだけど使えそうなので記事にした

ポイント

メソッドチェーンで使える。Railsお得意のscopeやlimitとか
ActiveRecord_Relation#extending を使用する
ひとつ定義すれば全モデルで使える
Gem化するほどじゃない、つくったかんじ
むしろ最近はもっと良いヘルパーが生まれてそう。。。
階層区切られているモデルにも対応
ASC, DESC も指定できる

本題

完成したものがこちらになります

app/models/concerns/dml_sanitizable.rb

# @note Concern扱いではなく、#extendingとして使用
module DmlSanitizable
  # PostgreSQL使用時に、MySQLの `ORDER BY FIELD()` 関数と同じ挙動をさせる
  # @example
  #   models = Model.all.extending(DmlSanitizable)
  #   models.order_by_field(:color, ['緑', '紫', '赤', '青', '黄'], :desc)
  #   # => [#<Model color: '黄'>, #<Model color: '青'>, #<Model color: '赤'>, ...]
  # @param column [Symbol]
  # @param custom_list [Array] 並び替えたい値の順序
  # @param sort_order [Symbol(:asc), Symbol(:desc)] (:asc) 昇順or降順
  # @return [Model::ActiveRecord_Relation] Modelは#extending先のモデルクラス
  def order_by_field(column, custom_list, sort_order = :asc)
    return self if custom_list.blank?

    model = self.class.to_s.deconstantize.constantize
    num = custom_list.length
    dml = <<~DML.squish
      CASE #{column}
        #{(0...num).map { |i| "WHEN ? THEN #{i}" }.join("\n")}
        ELSE #{num}
      END
      #{sort_order.upcase}
    DML

    order(
      # FIXME: Rails4系だったのでsend使っているがそのままsanitize_sql_array()宣言で良い
      model.send(:sanitize_sql_array, [dml, *custom_list])
    )
  end
end

つかいかたはこう

例1.rb

Article
  .where(id: ids)
  .extending(DmlSanitizable)
  .order_by_field(:category, ['news', 'pop', 'cute', 'cool'], :asc)
  .limit(100)

解説

生成されるクエリを見ていただけると手品のタネが明らかになるが、つまりは FIELD() 句を CASE 文で実現しているだけである。
それをモデルの #order メソッドに文字列で渡しているだけ。

改善点

JOINとかの考慮もしておきたいね
ActiveRecord_Relation でないと対応できないね
リストが長くなると怖いわね
配置場所考えよう
すでに他のやり方を誰かが考えてそう
DmlSanitizableって大げさな名前つけたんだからもっと便利増やそうぜ

おまけRSpec

おまけですわ

spec/models/concerns/dml_sanitizable_spec.rb

describe DmlSanitizable, type: :model do
  let(:sanitized_model) { model.all.extending(DmlSanitizable) }

  describe '#order_by_field' do
    subject { sanitized_model.order_by_field(column, custom_list, sort_order) }

    let(:model) { Article }
    let(:column) { :title }

    let!(:article_r) { create(:article, title: '赤') }
    let!(:article_b) { create(:article, title: '青') }
    let!(:article_y) { create(:article, title: '黄') }

    context '空のリストが指定された場合' do
      let(:custom_list) { [] }
      let(:sort_order) { :asc }
      it '例外が発生しないこと' do
        expect { subject }.to_not raise_error
      end

      it '自身を返していること' do
        is_expected.to match(model.all)
      end
    end

    context '「青>黄>赤」の順で指定した場合' do
      let(:custom_list) { %w(青 黄 赤) }
      context '昇順の場合' do
        let(:sort_order) { :asc }
        it '「青>黄>赤」の順で取得できること' do
          is_expected.to eq([article_b, article_y, article_r])
        end
      end

      context '降順の場合' do
        let(:sort_order) { :desc }
        it '「黄>青>赤」の順で取得できること' do
          is_expected.to eq([article_r, article_y, article_b])
        end
      end

      context 'リストにないレコードがある場合' do
        let!(:article_g) { create(:article, title: '緑') }
        context '昇順の場合' do
          let(:sort_order) { :asc }
          it '「青>黄>赤>緑」の順で取得できること' do
            is_expected.to eq([article_b, article_y, article_r, article_g])
          end
        end

        context '降順の場合' do
          let(:sort_order) { :desc }
          it '「緑>赤>黄>青」の順で取得できること' do
            is_expected.to eq([article_g, article_r, article_y, article_b])
          end
        end
      end
    end

    context '「赤>青>黄」の順で指定した場合' do
      let(:custom_list) { %w(赤 青 黄) }
      context '昇順の場合' do
        let(:sort_order) { :asc }
        it '「赤>青>黄」の順で取得できること' do
          is_expected.to eq([article_r, article_b, article_y])
        end
      end

      context '降順の場合' do
        let(:sort_order) { :desc }
        it '「黄>青>赤」の順で取得できること' do
          is_expected.to eq([article_y, article_b, article_r])
        end
      end

      context 'リストにないレコードがある場合' do
        let!(:article_g) { create(:article, title: '緑') }
        context '昇順の場合' do
          let(:sort_order) { :asc }
          it '「赤>青>黄>緑」の順で取得できること' do
            is_expected.to eq([article_r, article_b, article_y, article_g])
          end
        end

        context '降順の場合' do
          let(:sort_order) { :desc }
          it '「緑>黄>青>赤」の順で取得できること' do
            is_expected.to eq([article_g, article_y, article_b, article_r])
          end
        end
      end
    end
  end
end