CreateCollationNonDeterministic.php

<?php
declare(strict_types=1);

use Migrations\AbstractMigration;

class CreateCollationNonDeterministic extends AbstractMigration
{
    const COLLATION_NAME = 'nd_unaccent_isensitive';
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * https://book.cakephp.org/phinx/0/en/migrations.html#the-change-method
     * @return void
     */
    public function change()
    {
        $query = sprintf(
            'CREATE COLLATION IF NOT EXISTS %s (provider = icu, locale = \'und-u-ks-level1\', deterministic = false);',
            self::COLLATION_NAME
        );

        $this->query($query);
    }

    static public function alterFieldsToCollateNd(
        array $fieldsList,
        string $tableName,
        AbstractMigration $baseMigration,
        string $schema = 'public'
    )
    {
        $adapter = $baseMigration->getAdapter();
        $columns = $adapter->getColumns($tableName);
        $alterCollateQuery = 'ALTER TABLE "%s"."%s" ALTER COLUMN %s %s COLLATE %s';
        $schemaColumnQuery = 'SELECT * FROM information_schema.columns WHERE table_schema = \'%s\' AND table_name = \'%s\' AND column_name = \'%s\';';
        foreach ($columns as $column) {
            if (!in_array($column->getName(), $fieldsList)) {
                continue;
            }

            if ($column->getType() !== 'string') {
                continue;
            }

            $queryColumnSchema = sprintf(
                $schemaColumnQuery,
                $schema,
                $tableName,
                $column->getName()
            );

            $columnDef = $adapter->fetchRow($queryColumnSchema);

            $typeColumn = sprintf(
                'TYPE %s(%d)',
                $columnDef['data_type'],
                $columnDef['character_maximum_length']
            );

            $query = sprintf(
                $alterCollateQuery,
                $schema,
                $tableName,
                $column->getName(),
                $typeColumn,
                self::COLLATION_NAME
            );

            $baseMigration->getOutput()->write(print_r($query, true));

            $adapter->query($query);
        }
    }
}

CreatePeoples.php

<?php
declare(strict_types=1);

use Migrations\AbstractMigration;

class CreatePeoples extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * https://book.cakephp.org/phinx/0/en/migrations.html#the-change-method
     * @return void
     */
    public function change()
    {
        $table = $this->table('peoples');

        $table->addColumn('name', 'string');

        $table->addColumn('last_name', 'string');

        $table->addColumn('sub_name', 'string');

        $table->create();

        CreateCollationNonDeterministic::alterFieldsToCollateNd(
            ['name', 'last_name', 'sub_name'],
            $table->getName(),
            $this
        );
    }
}