Laravel orderBy ignores numeric values in ordering data

I have a column called Class ID and I am trying to sort this column.
It seems to ignore the numbers. I assume because they are strings.

Currently, it looks like this:

NewSyn - 1 OBSSt
NewSyn - 1 OBSSt
NewSyn - 1 OBSSt
NewSyn - 11 SH-B
NewSyn - 11 SH-B
NewSyn - 15 SH-O
NewSyn - 16 SH-On
NewSyn - 18 SH-S
NewSyn - 18 SH-S
NewSyn - 18 SH-S
NewSyn - 19 SHS
NewSyn - 19 SHS
NewSyn - 2 OBSB
NewSyn - 2 OBSB
NewSyn - 2 OBSB
NewSyn - 2 OBSB

It Should be sorted Alphabetical and using the number so :

NewSyn - 1 OBSSt
NewSyn - 1 OBSSt
NewSyn - 1 OBSSt
NewSyn - 2 OBSB
NewSyn - 2 OBSB
NewSyn - 2 OBSB
NewSyn - 2 OBSB
NewSyn - 11 SH-B
NewSyn - 11 SH-B
NewSyn - 15 SH-O
NewSyn - 16 SH-On
NewSyn - 18 SH-S
NewSyn - 18 SH-S
NewSyn - 18 SH-S
NewSyn - 19 SHS
NewSyn - 19 SHS

I have tried:

->orderBy('case_id', 'ASC')

My Query

$cases = Classrooms::where('created_date', '>=' , $today)
    ->orderBy('case_id', 'ASC')
    ->get();

Laravel correctly follows your instructions, as you want to sort a text based on its default logic of character-by-character comparison. Therefore, the result, although not what you intended, is still correct!

If you want to set a custom sort order, the best solution in my opinion is to divide the column into three parts. (Assuming the syntax is: text - number text.)

// Example
/*
N... - 1 O...
N... - 11 O...
A... - 11 O...
N... - 2 S...
N... - 11 A...
*/

1.) Order By Text before the number

orderByRaw("SUBSTRING_INDEX(your_column_name, ' ', 1) ASC")

// Result from Example
/*
A... - 11 O... // changed (move to here)
N... - 1 O...
N... - 11 O...
               // changed (moved from here)
N... - 2 S...
N... - 11 A...
*/

SELECT SUBSTRING_INDEX('NewSyn - 2 SH-On', ' ', 1);

# Result: "NewSyn"

2.) Order By Number

orderByRaw("CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(your_column_name, ' ', -1), ' ', 1) AS UNSIGNED) ASC")

// Result from 1st orderBy
/*
A... - 11 O...
N... - 1 O...
N... - 2 S... // changed
N... - 11 O... // changed
N... - 11 A...
*/

SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX('NewSyn - 2 SH-On', ' - ', -1), ' ', 1) AS UNSIGNED);

# Result: 2

3.) Order By Text after the number

orderByRaw("SUBSTRING_INDEX(your_column_name, ' ', -1) ASC")

// Result from 2nd orderBy
/*
A... - 11 O...
N... - 1 O...
N... - 2 S...
N... - 11 A... // changed
N... - 11 O... // changed
*/

SELECT SUBSTRING_INDEX('NewSyn - 2 SH-On', ' ', -1);

# Result: "SH-On"

Summary

YourModel::orderByRaw("SUBSTRING_INDEX(case_id, ' ', 1) ASC")
     ->orderByRaw("CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(case_id, ' ', -1), ' ', 1) AS UNSIGNED) ASC")
     ->orderByRaw("SUBSTRING_INDEX(case_id, ' ', -1) ASC")
     ->get();

Laravel performs the sorting in the order you request, so if you only need to pay attention to the number and the text after it, you can achieve the task by using the 2nd and 3rd code snippets (omitting the 1st one). It depends on the requirements of your project and what you need.

The key point is that we have the ability to manipulate complex columns using SQL code. We can pass column manipulations with SQL code to functions known as "RAW" functions, such as selectRaw, whereRaw, orderByRaw, etc.

It is of utmost importance to handle user input in a secure manner to prevent direct passing of SQL code. That's why there is a separated approach to handle this. The simple select, where, orderBy, and other functions will not accept SQL code in any way, but the RAW functions will. In my solution, I used 3 consecutive RAW functions, splitting the text into 3 parts, allowing me to sort each part separately as if they were from 3 different columns.

What is "SUBSTRING_INDEX" function?

What is "CAST" function?

How to can use SQL functions in your Laravel query?

As you already figured out, you can't sort numbers the same way you sort text. 2 will always come after 1 in a lexical sort, even if that 1 is part of 104.

So just get() a Collection instance and pass sortBy() a function to do what you want. This one uses a regular expression to separate numbers and text, then uses the spaceship operator to compare the values.

$result = collect([
    "NewSyn - 1 OBSSt", "NewSyn - 1 OBSSt", "NewSyn - 1 OBSSt", "NewSyn - 11 SH-B",
    "NewSyn - 11 SH-B", "NewSyn - 15 SH-O", "NewSyn - 16 SH-On", "NewSyn - 18 SH-S",
    "NewSyn - 18 SH-S", "NewSyn - 18 SH-S", "NewSyn - 19 SHS", "NewSyn - 19 SHS", 
    "NewSyn - 2 OBSB", "NewSyn - 2 OBSB", "NewSyn - 2 OBSB", "NewSyn - 2 OBSB",
]);

$sorted = $result->sortBy([function ($a, $b) {
    // number will be in first match, text in second
    $pattern = "/NewSyn - ([0-9]+) (.*)/";
    if (preg_match($pattern, $a, $a_matches) && preg_match($pattern, $b, $b_matches)) {
        // if numbers are the same (comparison returns 0) sort by text instead
        return $a_matches[1] <=> $b_matches[1] ?: $a_matches[2] <=> $b_matches[2];
    }
    return $a <=> $b;
}]);
dump($sorted->values()->toArray());

You didn't say if all the number/text pairs would be the same, so in this example it will fall back to comparing the text when the numbers are the same. It also assumes the values will also start with "NewSyn - ".

Output:

array:16 [
0 => "NewSyn - 1 OBSSt"
1 => "NewSyn - 1 OBSSt"
2 => "NewSyn - 1 OBSSt"
3 => "NewSyn - 2 OBSB"
4 => "NewSyn - 2 OBSB"
5 => "NewSyn - 2 OBSB"
6 => "NewSyn - 2 OBSB"
7 => "NewSyn - 11 SH-B"
8 => "NewSyn - 11 SH-B"
9 => "NewSyn - 15 SH-O"
10 => "NewSyn - 16 SH-On"
11 => "NewSyn - 18 SH-S"
12 => "NewSyn - 18 SH-S"
13 => "NewSyn - 18 SH-S"
14 => "NewSyn - 19 SHS"
15 => "NewSyn - 19 SHS"
]