Zenn
🔽

Laravel で varcher 型を数値としてソートする (column-sortable)

2020/08/29に公開
Laravel
MySQL
PHP
Eloquent
tech

##　やりたいこと
MySQL において varchar で定義されたカラムを数値型としてソートしたい。

どういうこと？

varchar のカラムを order by すると、辞書順にソートが行われる。
例えば 100, 2, 99, 11 というデータがあり、これを昇順に並べるたいとする。
通常は 2, 11, 99, 100 と並ぶことを期待するが、 varchar の場合は 100, 11, 2, 99 という順番になってしまう。
なので、これを期待する順序に並ぶようにしたい。

考えうる解決策

  1. カラム数値型に変える
  2. ゼロパディングする (例: 100, 002, 099, 011)
  3. 対象のデータを数値型にキャストする

本稿では 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 も同様の原理。

Discussion