Eloquent natural sorting ignored

I need to sort the data in natural sorting way and I'm having problems. This is the example data in database:

id | number | ...
1 | REP-1
2 | REP-2
3 | REP-3
...
10 | REP-10
11 | REP-11
...
20 | REP-20
...

Note that the data here appears sequential, but it's not, so I can't use ID to sort. This is the SQL query generated by Eloquent: select * from table_nameorder by LENGTH(number) asc,number asc. I generated it with the following code: Model::orderByRaw('LENGTH(?) asc', ['number'])->orderBy(number) The results are in this order:

1 | REP-1
10 | REP-10
11 | REP-11
...
2 | REP-2
20 | REP-20
...

But when copying the query generated by Eloquent and using phpMyAdmin (or equivalent), this is the result set I'm getting back:

1 | REP-1
2 | REP-2
...
10 | REP-10
11 | REP-11
...
20 | REP-20

I'm 100% sure there's no other code being executed that would alter the order of the collection, the data is being queried and then feed into the Collection/Resource and returned as JSON response. Even if I dd(Model::orderByRaw('LENGTH(?) asc', ['number'])->orderBy(number)), the order of collection is still wrong!

Why is that and how can I fix it?

EDIT: After Lary responded, I noticed I forgot to add an important detail. The number column on which I want to order the collection, is dynamic! It can be any of the available columns for that model and that's why I wanted to attach it through binding. After Lary responded, I updated the code and I am filtering the sort column request received through a whitelist of available columns for that model and rejecting all the rest, since I am creating a rather unsafe query: ->orderByRaw('LENGTH(' . $request->sort . ') ' . $direction). If anyone else has another recommendation, I'm happy to hear it and try it.

LaryAI

2 years ago

Level 58

The issue you're facing is due to the fact that the orderByRaw method in Eloquent treats the ? placeholder as a string literal, rather than a column name. To fix this, you can use the orderByRaw method with a raw expression instead.

Here's an example of how you can modify your code to achieve natural sorting:

Model::orderByRaw('CAST(SUBSTRING_INDEX(number, "-", -1) AS UNSIGNED) asc')
    ->orderBy('number')
    ->get();

In this example, we're using the SUBSTRING_INDEX function to extract the numeric part of the number column, and then casting it to an unsigned integer using CAST. This will ensure that the sorting is done in a natural way.

Please note that this solution assumes that the numeric part of the number column is always separated by a hyphen ("-"). If your data has a different separator, you'll need to adjust the code accordingly.

I hope this helps! Let me know if you have any further questions.

1 like

Please or to participate in this conversation.