Search code examples
sortingyii2

Yii2 Natural sorting Text+Number

I have a MySQL table with a field name that contains not only text, but also a number. I sort by name:

->orderBy(['name' => SORT_ASC])

...
ALTAMURA 7
ALTAMURA 8
ANSEDONIA 1
ANSEDONIA 10
ANSEDONIA 11
ANSEDONIA 12

ANSEDONIA 2
ANSEDONIA 3
...
ANSEDONIA 9

But I want the list to be sorted not only alphabetically but also by the numbers inside the name:

...
ALTAMURA 7
ALTAMURA 8
ANSEDONIA 1
ANSEDONIA 2
ANSEDONIA 3
...
ANSEDONIA 9
ANSEDONIA 10
ANSEDONIA 11
ANSEDONIA 12

If it's possible

Solution

  • Yes, it's possible. To do this, you'll need to use a quite special kind of sorting called "natural sorting". SQL does not include natural sorting inherently but we can simulate it. In MySQL for example, you can use CONVERT(column, UNSIGNED INTEGER) to sort your mixed string/integer field. This implicit conversion extracts the numeric part and sorts it as a number. Here's an example of how it might look in your case:

    ->orderBy([
   new \yii\db\Expression('CAST(SUBSTRING_INDEX(name, " ", -1) AS UNSIGNED)')
])

    For PostgreSQL, you can simulate natural sorting using regular expressions to separate the numeric part of the field. Here's how you might do it:

    ->orderBy([
    new \yii\db\Expression("substring(name, '^[a-zA-Z]+')"),
    new \yii\db\Expression("CAST(substring(name, '[0-9]+$') AS INTEGER)"),
])