add_natural_sort_function.php

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