ActiveRecord

【MYSQL | Rails】並び替えするカラムにNullが含まれている場合の対処法

  /

RailsのActiveRecordのorderメソッドを使ってNullが含まれているカラムを使って並び替えする際の注意点についてまとめていきたいと思います。

並び替えする方法

まず並び替えしない場合の挙動を見ていきます。

 # articles_controller.rb
  def index
    @articles = Article.all
  end

  # article.rb
  enum priority: {
    important: 10,
    normal: 20,
    light: 30
  }

  # index.html.erb
  # 中略
  <tbody>
    <% @articles.each do |article| %>
    <tr>
      <td><%= article.title %></td>
      <td><%= article.content %></td>
      <td><%= article.priority %></td>
    <tr>
  # 中略

IDが小さい順に表示されています。
(ここではIDの昇順に並んでいますが、SQLは言語の性質上取得の順序は保証しないため、確実にIDの昇順で表示したい場合はorderする必要があります。)

では、これらの記事を次のような要件に従って並び替えたいとします。

  1. 重要度の高い順に表示する
  2. 重要度が決まっていない記事に関しては最後に順不同で表示する

このようにしたい場合、真っ先にpriorityカラムの値でorderする方法が思いつくかと思います。

  def index
    @articles = Article.all.order(priority: :asc)
  end

しかし、これでは重要度(priority)がNullの記事が先頭に表示されており、②の要件を満たせていません。
MYSQLではNullの扱いを最小値にするという性質があるため、このような結果になっています。この性質はデータベースごとに異なっており、例えばPostgreSQLの場合、最大値として扱われるため最後に表示されます。

データベースごとのNullの扱いについては下記Qiita記事が分かりやすくまとまっているので載せておきます。
https://qiita.com/SVC34/items/c23341c79325a0a95979

では、どうするのでしょうか。
MYSQLではpriorityに対してIS NULL ASCを指定することで最後に持ってくることができます。

  def index
    @articles = Article.all.order(Arel.sql('priority IS NULL ASC'))
  end

これにより、重要度(priority)がNullの記事を最後に持っていくことができました。あとは、昇順に並び替えれば要件達成になります。

  def index
    @articles = Article.all.order(Arel.sql('priority IS NULL ASC, priority ASC'))
  end

他にもこんなやり方があります。

  def index
    @articles = Article.all.order(Arel.sql('ifnull(priority, 2147483647) ASC'))
  end

#  SELECT `articles`.* FROM `articles` ORDER BY ifnull(priority, 2147483647) ASC

ifnull関数を使って、Nullの場合int型の最大値を入れて昇順で並び替えた際に最後になるようにしています。

int型の最大値についてはこちらの記事が参考になりました。
https://qiita.com/masarufuruya/items/ccf51e917a5cbf8890a3

また、こんなやり方もあります。

  def index
    @articles = Article.all.order(Arel.sql('CASE WHEN priority IS NULL THEN 1 ELSE 0 END, priority ASC'))
  end

# SELECT `articles`.* FROM `articles` ORDER BY CASE WHEN priority IS NULL THEN 1 ELSE 0 END, priority ASC

ifnull関数のときと同じでpriorityがNullの場合は1を返し、それ以外の場合は0を返すようにして最後に持っていくようにしています。

また、Arel.sqlで囲わずに文字列をそのままorderの引数に入れるとエラーになります。

  def index
    @articles = Article.all.order('priority IS NULL ASC, priority ASC')
  end

Query method called with non-attribute argument(s): “priority IS NULL ASC, priority ASC”

これが出てきた場合は、Arel.sqlで囲う必要があります。ただ、何でもかんでもArel.sqlで囲えばいいかというとそういうわけではなく、SQLインジェクションに対する脆弱性を考える必要があります。これに関してはこの記事が詳しく解説しているので、載せておきます。
https://qiita.com/jnchito/items/5f2f00c93c0ba68e4d31

今回はユーザの入力値を受け付けておらずかつpriorityはInt型なので安全と考えArel.sqlで囲うこととします。

補足

MySQLの5.7ではエイリアスをつけたカラムで並び替えをしようとするとエラーになります。
例えば、次のようなSQLはMYSQL側でエラーになります。

SELECT parent_id, MIN(users.age) AS min_age FROM users GROUP BY users.parent_id ORDER BY min_age IS NULL ASC, age ASC 
# Reference ‘min_age’ not supported (reference to group function)

これをエイリアスをつけずに次のように並び替えのカラムを指定するとエラーにならず意図したとおりに並び替えられます。

SELECT parent_id, MIN(users.age) AS min_age FROM users GROUP BY users.parent_id ORDER BY MIN(users.age) IS NULL ASC, age ASC

少々なぞな仕様ではありますが、ハマりどころなので紹介しました。
自分で確認できている訳ではないですが、MYSQL8.0ではエイリアスでも並び替えできるようです。

まとめ

まとめると以下になるかと思います

Nullを含むカラムでそれを最後にして昇順で並び替えする際にはIS NULL ASCを指定する

ここまで読んでいただきありがとうございました。

MySQL MYSQL5.7 rails
ABOUT ME
sakai
東京在住の30歳。元々は車部品メーカーで働いていてましたが、プログラミングに興味を持ちスクールに通ってエンジニアになりました。 そこからベンチャー → メガベンチャー → 個人事業主になりました。 最近は生成 AI 関連の業務を中心にやっています。 ヒカルチャンネル(Youtube)とワンピースが大好きです！
BLOG：https://blog.egghead.co.jp
関連記事