Created
May 28, 2024 02:58
-
-
Save muath-ye/87675de6c388026f34f33f4565af3e6b to your computer and use it in GitHub Desktop.
A Laravel migration to add neutral_sort function to MySQL and Postgres DB
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
use Illuminate\Database\Migrations\Migration; | |
use Illuminate\Database\Schema\Blueprint; | |
use Illuminate\Support\Facades\DB; | |
use Illuminate\Support\Facades\Schema; | |
return new class extends Migration | |
{ | |
/** | |
* Run the migrations. | |
*/ | |
public function up(): void | |
{ | |
// big thanks to https://github.com/reinink for his amazing work in https://eloquent-course.reinink.ca/ | |
if (config('database.default') === 'mysql') { | |
DB::unprepared(" | |
drop function if exists natural_sort; | |
create function natural_sort(s varchar(255)) returns varchar(255) | |
no sql | |
deterministic | |
begin | |
declare orig varchar(255) default s; | |
declare ret varchar(255) default ''; | |
if s is null then | |
return null; | |
elseif not s regexp '[0-9]' then | |
set ret = s; | |
else | |
set s = replace(replace(replace(replace(replace(s, '0', '#'), '1', '#'), '2', '#'), '3', '#'), '4', '#'); | |
set s = replace(replace(replace(replace(replace(s, '5', '#'), '6', '#'), '7', '#'), '8', '#'), '9', '#'); | |
set s = replace(s, '.#', '##'); | |
set s = replace(s, '#,#', '###'); | |
begin | |
declare numpos int; | |
declare numlen int; | |
declare numstr varchar(255); | |
lp1: loop | |
set numpos = locate('#', s); | |
if numpos = 0 then | |
set ret = concat(ret, s); | |
leave lp1; | |
end if; | |
set ret = concat(ret, substring(s, 1, numpos - 1)); | |
set s = substring(s, numpos); | |
set orig = substring(orig, numpos); | |
set numlen = char_length(s) - char_length(trim(leading '#' from s)); | |
set numstr = cast(replace(substring(orig,1,numlen), ',', '') as decimal(13,3)); | |
set numstr = lpad(numstr, 15, '0'); | |
set ret = concat(ret, '[', numstr, ']'); | |
set s = substring(s, numlen+1); | |
set orig = substring(orig, numlen+1); | |
end loop; | |
end; | |
end if; | |
set ret = replace(replace(replace(replace(replace(replace(replace(ret, ' ', ''), ',', ''), ':', ''), '.', ''), ';', ''), '(', ''), ')', ''); | |
return ret; | |
end; | |
"); | |
} | |
if (config('database.default') === 'pgsql') { | |
DB::unprepared(' | |
create or replace function natural_sort(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$; | |
'); | |
} | |
} | |
/** | |
* Reverse the migrations. | |
*/ | |
public function down(): void | |
{ | |
if (config('database.default') === 'mysql') { | |
DB::unprepared('drop function if exists natural_sort'); | |
} | |
if (config('database.default') === 'pgsql') { | |
DB::unprepared('drop function if exists natural_sort'); | |
} | |
} | |
}; |
Author
muath-ye
commented
May 28, 2024
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment