Laravel/ PHP: Order By Alphabetical with numbers in order

Question

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?

Thanks.

Answer 1

It might be late but for others it might help.

Based on above link I found below, I derived the best way that would likely solve your problem with the example code: https://www.electrictoolbox.com/mysql-order-string-as-int/

Query

SELECT * FROM <table> ORDER BY CAST(<column> AS unsigned)

Example for laravel

DB::table('test')
    ->orderByRaw("CAST(title as UNSIGNED) ASC")
    ->get();

Answer 2

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')
    ->get();

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.

Answer 3

For Laravel this also works:

$collection = $collection->sortBy('order', SORT_REGULAR, true);

Answer 4

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