Natural ORDER in Laravel Eloquent ORM

Question

How can i get 'natural order' in 'Eloquent ORM'? In table I have column 'text' (string).
Normal order: Model::orderBy('text')

'value 1'
'value 12'
'value 23'  
'value 3'
'value 8'

I need this:

'value 1'
'value 3'
'value 8'
'value 12'
'value 23'  

Any ideas?

Heer

Answer 1

You could add a raw query and do something like this:

Model::orderBy(DB::raw('LENGTH(text), text'));

Or, in modern versions of Laravel:

Model::orderByRaw('LENGTH(text), text');

Bullnecked

Nice. what do the two text means? one references to the row name and the other one? – Outright

Answer 2

For Laravel this also works:

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

Unilateral

I didn't realize the sortBy accepted a second (or third) parameter. This helped fix my Laravel 5.2 Collection sorting. Laravel documentation doesn't show this as an option (laravel.com/docs/5.2/collections#method-sortby), although the sortBy function in the Collection.php file does show it uses SORT_REGULAR as the default second parameter (which made sense in hindsight). I ended up going with: Item::where($itemQueryArray)->with($modelName)->get()->sortBy($sortText, SORT_STRING | SORT_FLAG_CASE); – Monstrous

Answer 3

if you use PostgreSQL, you can do the following:

1. Your migration

    public function up()
    {
        DB::unprepared(
        'create or replace function naturalsort(text)
        returns bytea language sql immutable strict as $f$
        select string_agg(convert_to(coalesce(r[2], length(length(r[1])::text) || 
        length(r[1])::text || r[1]), \'SQL_ASCII\'),\'\x00\')
        from regexp_matches($1, \'0*([0-9]+)|([^0-9]+)\', \'g\') r;
        $f$;'); 
   }
   
   
   public function down()
    {
        DB::unprepared('DROP FUNCTION IF EXISTS naturalsort;');
    }
   

2. In your model, you can add these scopes:

   public function scopeOrderByNatural(Builder $builder, string $column, string $direction = 'asc')
    {
        if (! in_array($direction, ['asc', 'desc'], true)) {
            throw new InvalidArgumentException('Order direction must be "asc" or "desc".');
        }
   
        return $builder->orderByRaw('naturalsort(' . $column . ') ' . $direction);
    }
   
   public function scopeOrderByNaturalDesc(Builder $builder, string $column)
    {
        return $builder->orderByNatural($column, 'desc');
    }
   

3. And in your client code you can write:

Model::orderByNatural('text')->get();

or reverse sorting

Model::orderByNaturalDesc('text')->get();

Relations

Answer 4

Sort collection (SORT_NATURAL):

FROM

1 => "...\src\storage\avatars\10.jpg"
0 => "...\src\storage\avatars\1.jpg"
2 => "...\src\storage\avatars\100.jpg"
3 => "...\src\storage\avatars\1000.jpg"
4 => "...\src\storage\avatars\101.jpg"
5 => "...\src\storage\avatars\102.jpg"

TO

0 => "...\src\storage\avatars\1.jpg"
1 => "...\src\storage\avatars\10.jpg"
2 => "...\src\storage\avatars\100.jpg"
3 => "...\src\storage\avatars\101.jpg"
4 => "...\src\storage\avatars\102.jpg"
5 => "...\src\storage\avatars\1000.jpg"

$natsort_collection = $collection->sortBy(null, SORT_NATURAL)->values();

// If you work with arrays: 
sort(...array of your data here..., SORT_NATURAL);

Dearth

Answer 5

try this model::orderBy('text', 'asc')

Hanghangar

can you elaborate? – Periphrastic