指定の順番でソートするORDER BY FIELD()が便利だった
最近初めて使ってみて割と便利だったクエリを紹介。
ActiveRecordのクエリにおいて、whereの引数に数値の配列を渡すとIN句が発行されますが、このとき配列の中身の順序に何かしらの意味があるとき、その通りの並びで結果が返ってきて欲しいな〜と思いませんか(僕は思いました)。
そんなときはORDER BY と FIELD関数を組み合わせと実現できます。(FIELD関数の説明はこちら↓)
ユーザーとスコアだけのシンプルなテーブルを使って説明します。
+------+--------+-------+
| id | name | score |
+------+--------+-------+
| 1 | foo | 70 |
| 2 | bar | 75 |
| 3 | baz | 95 |
| 4 | foobar | 40 |
| 5 | foobaz | 65 |
| 6 | hoge | 80 |
| 7 | fuga | 40 |
| 8 | piyo | 70 |
+------+--------+-------+まずは普通にwhereで配列を渡してみると、idの降順で並びました。
- ActiveRecordのクエリ
ids = [2, 1, 5, 6, 3]
User.where(id: ids)
- 発行されるSQLのクエリ
SELECT `users`.*
FROM `users`
WHERE `users`.`id` IN (2, 1, 5, 6, 3)
LIMIT 11;
- 結果
+------+--------+-------+
| id | name | score |
+------+--------+-------+
| 1 | foo | 70 |
| 2 | bar | 75 |
| 3 | baz | 95 |
| 5 | foobaz | 65 |
| 6 | hoge | 80 |
+------+--------+-------+ただ先程の[2, 1, 5, 6, 3]という配列の順番に意味があって、どうしてもそれ通りに並べたい場合はORDER BYとFIELD関数を組み合わせることで指定の順番に並べることができます。
ids = [2, 1, 5, 6, 3]
User.where(id: ids).order("field(id, #{ids.join(',')})")
- 発行されるSQLのクエリ
SELECT `users`.*
FROM `users`
WHERE `users`.`id` IN (2, 1, 5, 6, 3)
ORDER BY field(id, 2,1,5,6,3)
LIMIT 11;
- 結果
+------+--------+-------+
| id | name | score |
+------+--------+-------+
| 2 | bar | 75 |
| 1 | foo | 70 |
| 5 | foobaz | 65 |
| 6 | hoge | 80 |
| 3 | baz | 95 |
+------+--------+-------+他の例を出すと、様々な条件で抽出してソートした2つの結果セットを連結したいケースにも使えます。
ある条件で絞り込んで、scoreの降順に並べた結果セットA
- ActiveRecordのクエリ
User
.where(<何かしらの条件>)
.order(score: :desc)
- 結果
+------+----------+-------+
| id | name | score |
+------+----------+-------+
| 3 | baz | 95 |
| 1 | foo | 70 |
| 4 | foobar | 40 |
+------+----------+-------+
ある条件で絞り込んで、idの昇順で並べた結果セットB
- ActiveRecordのクエリ
User
.where(<何かしらの条件>)
.order(id: :asc)
- 結果
+------+----------+-------+
| id | name | score |
+------+----------+-------+
| 2 | bar | 75 |
| 5 | foobaz | 65 |
| 6 | hoge | 80 |
| 7 | fuga | 40 |
+------+----------+-------+
↓これらを連結して以下のような結果を取得したい
+------+----------+-------+
| id | name | score |
+------+----------+-------+
| 3 | baz | 95 |
| 1 | foo | 70 |
| 4 | foobar | 40 | <- ここまでが結果セットA
| 2 | bar | 75 |
| 5 | foobaz | 65 |
| 6 | hoge | 80 |
| 7 | fuga | 40 | <- ここまでが結果セットB
+------+----------+-------+ソート済の結果セットをただ連結してしまうと、それぞれソートしていた順番は無視され、何も指定しなければやはりidの昇順で並んでしまいます。
そこで先程のORDER BYとFILED関数を組み合わせてどうにか実現してみましょう。
かなり力技ですが、こんな感じで実現できます。
users_a = User
.where(<何かしらの条件>)
.order(score: :desc)
.pluck(:id)
users_b = User
.where(<何かしらの条件>)
.order(id: :asc)
.pluck(:id)
# idの配列を結合(結果セットA -> 結果セットB)という順番に並ぶ
ids = users_a + users_b
results = User.where(id: ids).order("field(id, #{ids.join(',')})")
- 結果
+------+----------+-------+
| id | name | score |
+------+----------+-------+
| 3 | baz | 95 |
| 1 | foo | 70 |
| 4 | foobar | 40 | <- ここまでが結果セットA
| 2 | bar | 75 |
| 5 | foobaz | 65 |
| 6 | hoge | 80 |
| 7 | fuga | 40 | <- ここまでが結果セットB
+------+----------+-------+ただクエリのパフォーマンスには要注意です。
というのもFIELD関数の結果でORDER BYするのはfilesortという方法でソートするからです。実行計画を見てみるとExtraに`Using filesort`と出ていますね。
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 50.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+filesortについての解説はこの記事が詳しかったです。
ある程度の件数まではこれでいいのかもしれませんが、数千件とかになるとかなり遅くなりそうです。その時は別の方法考えるか〜。
この記事が気に入ったらサポートをしてみませんか？