如何在"Eloquent ORM"中获得"自然顺序"?表中有列"text"(字符串)
正常订单:型号::orderBy('text')
'value 1'
'value 12'
'value 23'
'value 3'
'value 8'
我需要这个:
'value 1'
'value 3'
'value 8'
'value 12'
'value 23'
有什么想法吗?
您可以添加一个原始查询并执行以下操作:
Model::orderBy(DB::raw('LENGTH(text), text'));
或者,在Laravel的现代版本中:
Model::orderByRaw('LENGTH(text), text');
对于Laravel来说,这也适用:
$collection = $collection->sortBy('order', SORT_NATURAL, true);
如果使用PostgreSQL,可以执行以下操作:
-
您的迁移
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;'); }
-
在您的模型中,您可以添加以下范围:
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'); }
-
在你的客户端代码中,你可以写:
Model::orderByNatural('text')->get();
或反向分拣
Model::orderByNaturalDesc('text')->get();
排序集合(Sort_NATURAL) :
来自
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"
至
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);
试试这个model::orderBy('text','asc')