CODEWITHSUNDEEP
phplaravelsortingeloquentlaravel-collection
Lajdák Marek
Lajdák Marek

Reputation: 3069

Natural ORDER in Laravel Eloquent ORM

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?

Upvotes: 10

Views: 7951

Answers (5)

Pallavi Rangadiya
Pallavi Rangadiya

Reputation: 31

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

Upvotes: 0

Сергей Жидков
Сергей Жидков

Reputation: 79

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

Upvotes: 2

Laurence
Laurence

Reputation: 60058

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

Upvotes: 13

Gromax
Gromax

Reputation: 1

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

Upvotes: 0

Erhnam
Erhnam

Reputation: 921

For Laravel this also works:

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

Upvotes: 6

Related Questions