Sort varcher type as number in Laravel (column-sortable)

Aug 29, 2020 PHP MySQL Laravel

Thing you want to do

I want to sort the column defined by varchar in MySQL as a numeric type.

What do you mean?

If you order by varchar column, it will be sorted in dictionary order. For example, suppose you have data 100, 2, 99, 11, and you want to arrange them in ascending order. Normally, it is expected to be lined up with 2, 11, 99, 100, but in the case of varchar, the order is 100, 11, 2, 99 .. So I want to arrange them in the order I expect.

Possible solutions

Change to column number type
Zero padding (example: 100, 002, 099, 011)
Cast the target data to a numeric type

This paper deals with 3.

Assumptions

environment

PHP 7.3.4
Laravel Framework 5.8.24
mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64)

column-sortable

It is recommended that column-sortable is already introduced as an extension for handling Laravel sort. However, the idea and basic methods are for Laravel, so you can use them without this extension.

How to install and use column-sortable is not covered here.

Table you want to sort

Column name: amount Type: varcher

Implementation

add sort method to model

public function amountNumericSortable($query, $direction)
{
    return $query->orderByRaw('CAST(amount AS DECIMAL)'. $direction);
}

The method name must have the suffix Sortable and be named in camel case. You can follow this rule, but don’t duplicate existing columns.

If you don’t use column-sortable, attach orderByRaw('CAST(amount AS DECIMAL)'. $direction) to existing query. Specify ASC/DESC in place of $direction

Add as a sortable column to the model

public $sortable = [
    'amount_numeric',
];

If the $sortable variable already exists, add amount_numeric to the array. Naming is done by removing the Sortable of the method added earlier as a snake case.

Not needed for those who are not using column-sortable.

Change sort settings on View side

Change before

@sortablelink('amount', __('Amount'))

After change

@sortablelink('amount_numeric', __('Amount'))

bonus

null support

If you sort a table that allows nulls in ascending order, nulls come first, so you want to avoid this. In that case, use orderByRaw('amount IS NULL ASC'). The combination is as follows.

public function amountNumericSortable($query, $direction)
{
    return $query
        ->orderByRaw('amount IS NULL ASC')
        ->orderByRaw('CAST(amount AS DECIMAL)'. $direction);
}

0 support

A mystery requirement that you want to be behind when the value is 0. Use orderByRaw('amount = 0 ASC').

Why does it work?

amount IS NULL becomes 1 when amount is null and becomes 0 when character is present (treated as true = 1, false = 0). So if you put them in ascending order, the characters line is smaller than the null lines, so the characters line comes first. amount = 0 has the same principle.