任意の順番でレコードを取得したいとき、MySQLのFIELD関数が便利だった!【Rails】

mysql> SELECT id FROM faqs;
+----+
| id |
+----+
| 10 |
|  5 |
|  1 |
|  6 |
|  7 |
|  9 |
|  2 |
|  3 |
|  4 |
| 11 |
+----+
10 rows in set (0.01 sec)

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)

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)

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)

> 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!'

> 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>]

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)