0埋めされていない数字文字列をソートしたい

どうも、にしむらです。今回はRails5.2 PostgreSQLの話です。

messages テーブル に 文字型 str_id に数字が0埋めなしで保存されている場合は以下のようにソートされます。

select * from messages order by str_id;
 id | str_id       
----+-------
 50 | 1000
 47 | 110
 46 | 12

この場合、lpadを使って0埋めの文字列に変換することで正しく並び替えることができます。

lpad(str_id, 5, '0')

str_id を 5桁の数字にして桁が足りない場合は左端から 0 で埋める

select id, str_id, lpad(str_id, 5, '0') as lpad_str_id from messages order by lpad(str_id, 5, '0');
 id | str_id | lpad_str_id
----+--------+-------------
 46 | 12     | 00012
 47 | 110    | 00110
 50 | 1000   | 01000

これで意図通りソートすることできます。

Rails だと

order('lpad(messages.str_id, 5, \'0\')')

だだし

order('lpad(messages.str_id, 5, \'0\')').last とすると、ActiveRecord::IrreversibleOrderError が発生するので注意が必要です。 order('lpad(messages.str_id, 5, \'0\') DESC').first だと問題なく動作します。

インゲージではエンジニアを募集しています!

ingage.co.jp