やりたいこと
MySQL において varchar で定義されたカラムを数値型としてソートしたい。
どういうこと?
varchar のカラムを order by すると、辞書順にソートが行われる。
例えば 100
, 2
, 99
, 11
というデータがあり、これを昇順に並べるたいとする。
通常は 2
, 11
, 99
, 100
と並ぶことを期待するが、 varchar の場合は 100
, 11
, 2
, 99
という順番になってしまう。
なので、これを期待する順序に並ぶようにしたい。
考えうる解決策
- カラム数値型に変える
- ゼロパディングする (例:
100
,002
,099
,011
) - 対象のデータを数値型にキャストする
本稿では 3 を取り扱う。
前提条件等
環境
- PHP 7.3.4
- Laravel Framework 5.8.24
- mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64)
column-sortable
Laravel のソートを扱う拡張として column-sortable
が既に導入してあるものとしてすすめる。
が、考え方と基本的なメソッドは Laravel のものなので、この拡張がなくても使える。
column-sortable
のインストール方法や使い方はここでは扱わない。
ソートしたいテーブル
カラム名: amount
型: varcher
実装
モデルにソートのためのメソッドを追加する
public function amountNumericSortable($query, $direction)
{
return $query->orderByRaw('CAST(amount AS DECIMAL) ' . $direction);
}
メソッド名は接尾辞の Sortable
が必須で、キャメルケースで命名する。
このルールを守ればなんでも良いが、既存のカラムと重複しないようにする。
column-sortable
を使っていない人は orderByRaw('CAST(amount AS DECIMAL) ' . $direction)
の部分を既存のクエリにくっつける。
$direction
のところは ASC
/DESC
を指定する
モデルにソートできるカラムとして追加する
public $sortable = [
'amount_numeric',
];
$sortable
の変数が既にある場合は、その配列に amount_numeric
を追加する。
命名は先に追加したメソッドの Sortable
を取り除いた部分をスネークケースにする。
column-sortable
を使っていない人は不要。
View 側でソートの設定を変える
変更前
@sortablelink('amount', __('Amount'))
変更後
@sortablelink('amount_numeric', __('Amount'))
おまけ
null の対応
null を許可しているテーブルを昇順でソートすると、 null が先頭にくるので、これをこないようにしたい。
そういうときは orderByRaw('amount IS NULL ASC')
を使用する。
組み合わせると以下のようになる。
public function amountNumericSortable($query, $direction)
{
return $query
->orderByRaw('amount IS NULL ASC')
->orderByRaw('CAST(amount AS DECIMAL) ' . $direction);
}
0 の対応
値が 0 の場合に後ろにしたいという、謎要件のとき。
orderByRaw('amount = 0 ASC')
を使用する。
どうして動く?
amount IS NULL
は amount が null
の時に 1
になり、文字がある
時に 0
になる(true = 1, false = 0 として扱われる)。
なのでこれを昇順に並べると、文字がある
時の方が null
の時より小さいので、 文字がある
行が先に来る。
amount = 0
も同様の原理。