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.

score 22 · Accepted Answer · edited May 23 '17 at 10:31

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.

Worked for me but I had to use orderByRaw(LENGTH(field),'ASC'). Thanks a lot, very intuitive solution. — Noel Murphy, Aug 02 '17 at 03:49

score 10 · Answer 2 · answered Aug 22 '19 at 14:37

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

score 5 · Answer 3 · answered Apr 19 '16 at 11:17

5

For Laravel this also works:

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

answered Apr 19 '16 at 11:17

Erhnam

901
2
13
23

Note that SORT_REGULAR is a PHP constant, not a Laravel directive. Here is the PHP reference, plus other constants for sorting, such as SORT_NATURAL, SORT_NUMERIC, etc: https://www.php.net/manual/en/array.constants.php – Genki Oct 14 '19 at 09:49

score 4 · Answer 4 · answered Nov 11 '19 at 07:10

4

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

answered Nov 11 '19 at 07:10

Che Safwan

41
2

3

Please put your answer always in context instead of just pasting code. See [here](https://stackoverflow.com/help/how-to-answer) for more details. – gehbiszumeis Nov 11 '19 at 07:17
you are ingenious! – Daniyal Javani Aug 16 '20 at 11:28

Saroj Shrestha · Answer 5 · 2020-03-13T03:03:32.227

For Laravel Collection:

$collection = collect([
    ['sn' => '2'],
    ['sn' => 'B'],
    ['sn' => '1'],
    ['sn' => '10'],
    ['sn' => 'A'],
    ['sn' => '13'],
]);

$sorted = $collection->sortBy('sn');

//print_r($collection);

Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [2] => Array
                (
                    [sn] => 1
                )

            [0] => Array
                (
                    [sn] => 2
                )

            [3] => Array
                (
                    [sn] => 10
                )

            [5] => Array
                (
                    [sn] => 13
                )

            [4] => Array
                (
                    [sn] => A
                )

            [1] => Array
                (
                    [sn] => B
                )

        )

)

As you can see, this will sort it correctly. However, if you want to sort it and reindex it then,

$sorted = $collection->sortBy('sn')->values()->all();

//print_r($sorted)

Array
(
    [0] => Array
        (
            [sn] => 1
        )

    [1] => Array
        (
            [sn] => 2
        )

    [2] => Array
        (
            [sn] => 10
        )

    [3] => Array
        (
            [sn] => 13
        )

    [4] => Array
        (
            [sn] => A
        )

    [5] => Array
        (
            [sn] => B
        )

)

Furthermore, You can also pass your own callback to determine how to sort the collection values.

$sorted = $collection->sortBy(function ($item, $key) {
    //your logic
});

For more details: https://laravel.com/docs/5.8/collections#method-sortby

score 0 · Answer 6 · answered Apr 21 '22 at 12:57

0

Basically like the accepted answer but with the comma removed and orderByRaw. Otherwise I get an error about bindings

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

answered Apr 21 '22 at 12:57

MandyF

27
3

Hi would someone please confirm what is wrong with the above? (just so I don't make the same mistake again) Thanks – MandyF Apr 26 '22 at 10:18
not sure looks good to me – C.Astraea Jul 18 '22 at 11:42

score -1 · Answer 7 · answered Jun 09 '20 at 21:37

-1

Order the resulting collection

$unorderedThings = Thing::orderBy('id')->get();
$orderedThings=$unorderedThings->sort();

answered Jun 09 '20 at 21:37

Gerrlt

39
7

score -1 · Answer 8 · answered Aug 23 '21 at 16:45

-1

This work's for me using eloquent, is very simple:

Eloquent:

$tests = Test::all();
$tests = $tests->sortBy('title', SORT_REGULAR, false); // false=ascending, true=descending

Sort numbers as text in Laravel.

I hope this is very helpful

answered Aug 23 '21 at 16:45

Radames E. Hernandez

4,235
27
37

Laravel/ PHP: Order By Alphabetical with numbers in order

8 Answers8

Linked

Related