任意の順番でレコードを取得したいとき、MySQLのFIELD関数が便利だった!【Rails】
SQLで 最初に任意の行を取得したい ときがよくあります。
そんなときは ORDER BY と MySQLの FIELD関数 を組み合わせることで実現できます。
SQL – FIELD関数
以下のようなテーブルがあります。
mysql> SELECT id FROM faqs;
+----+
| id |
+----+
| 10 |
| 5 |
| 1 |
| 6 |
| 7 |
| 9 |
| 2 |
| 3 |
| 4 |
| 11 |
+----+
10 rows in set (0.01 sec)ORDER BY FIELD でidを指定します。
mysql> SELECT id FROM faqs ORDER BY FIELD(id, 6, 9);
+----+
| id |
+----+
| 10 |
| 5 |
| 1 |
| 7 |
| 2 |
| 3 |
| 4 |
| 11 |
| 6 |
| 9 |
+----+
10 rows in set (0.00 sec)指定したid(6, 9)が下部に来てしまいます。
DESCを指定します。
mysql> SELECT id FROM faqs ORDER BY FIELD(id, 6, 9) DESC;
+----+
| id |
+----+
| 9 |
| 6 |
| 10 |
| 5 |
| 1 |
| 7 |
| 2 |
| 3 |
| 4 |
| 11 |
+----+
10 rows in set (0.00 sec)指定したidが上部にきました。指定順も逆になります。
指定以外はデフォルト順になっています。
さらにORDER BYの指定を追加すると、FIELDで指定以外もソートできます。
mysql> SELECT id FROM faqs ORDER BY FIELD(id, 6, 9) DESC, id ASC;
+----+
| id |
+----+
| 9 |
| 6 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 7 |
| 10 |
| 11 |
+----+
10 rows in set (0.00 sec)Rails
以下のように実行するとエラーになりました。
> Faq.select(:id).order("field(id, 4)")
ActiveRecord::UnknownAttributeReference: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "field(id, 4)".This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql().
from /Users/asa/.rbenv/versions/2.7.2/lib/ruby/gems/2.7.0/gems/activerecord-7.0.8.1/lib/active_record/sanitization.rb:146:in `disallow_raw_sql!'Arel.sql で囲むとよいようです。
> Faq.select(:id).order([Arel.sql('field(id, ?) desc'), [4,5,6]])
=> Faq Load (0.6ms) SELECT `faqs`.`id` FROM `faqs` ORDER BY field(id, '4','5','6') desc
[#<Faq:0x000000013fd70420 id: 6>,
#<Faq:0x000000013fd70358 id: 5>,
#<Faq:0x000000013fd70290 id: 4>,
#<Faq:0x000000013fd701c8 id: 10>,
#<Faq:0x000000013fd70100 id: 1>,
#<Faq:0x000000013fd70038 id: 7>,
#<Faq:0x000000013fd72798 id: 9>,
#<Faq:0x000000013fd73ee0 id: 2>,
#<Faq:0x000000013fd87f30 id: 3>,
#<Faq:0x000000013fd87e68 id: 11>]
> Faq.select(:id).order([Arel.sql('field(id, ?) desc'), [4,5,6]]).order(:id)
=> Faq Load (0.9ms) SELECT `faqs`.`id` FROM `faqs` ORDER BY field(id, '4','5','6') desc, `faqs`.`id` ASC
[#<Faq:0x0000000109e883e8 id: 6>,
#<Faq:0x0000000109e88320 id: 5>,
#<Faq:0x0000000109e88258 id: 4>,
#<Faq:0x0000000109e88190 id: 1>,
#<Faq:0x0000000109e880c8 id: 2>,
#<Faq:0x0000000109e89ea0 id: 3>,
#<Faq:0x0000000109e83fa0 id: 7>,
#<Faq:0x0000000109e83e88 id: 9>,
#<Faq:0x0000000109e83d98 id: 10>,
#<Faq:0x0000000109e83cd0 id: 11>]SQL – CASE式を使った場合
CASE式で、idが、6→9→その他 の順でソートするようにします。
id ASC でその他部分をソートしています。
SELECT
id
FROM
faqs
ORDER BY
CASE id
WHEN 6 THEN 1
WHEN 9 THEN 2
ELSE 3 -- その他は全て3に設定
END,
id ASC
;+----+
| id |
+----+
| 6 |
| 9 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 7 |
| 10 |
| 11 |
+----+
10 rows in set (0.01 sec)参考
- 指定の順番でソートするORDER BY FIELD()が便利だった
- MySQLで指定行のみ最初に並び替えて取得 – Qiita
- FIELD関数でソートする時の記法忘れるのでメモっておく – Qiita
- [SQL] 昇順・降順ではなく、任意の順番でソートしたい – Qiita
