Toby Mellor Toby Mellor - 1 year ago 145
SQL Question

Laravel/ PHP: Order By Alphabetical with numbers in order

When ordering things by Alphabetical Order, I'm left with this:

S1 Episode 1
S1 Episode 11
S1 Episode 12
S1 Episode 2
S1 Episode 3

S2 Episode 1
S2 Episode 11

Example Code:

DB::table('test')->orderby('title', 'ASC')->get();

Etc. I need these to be ordered properly. Any solutions?


Answer Source

You are being posed with the problem of sorting items alphanumerically, or in computer science terms, natural sorting.

There are many ways to achieve a natural sort with straight MySQL but you could also take the results from your Laravel helper into array format and implement PHP's natsort function instead.

From the methods I found above, I derived the best way that would likely solve your problem with the example code:

DB::table('test')->orderBy('LENGTH(title)', 'ASC')
    ->orderBy('title', 'ASC')

however I'm not sure if the helper will complain about receiving a MySQL function instead of a straight column name into the orderBy function. I'm only transcribing from the references I used in combination with your example too - I cannot guarantee the efficacy.