Unique comparison is case sensitive

[ivanvermeyen]

It seems that the where clause in the unique_translation rule is case sensitive.

laravel-unique-translation/src/UniqueTranslationValidator.php

Lines 161 to 164 in 1eb3d56

	protected function findTranslation($connection, $table, $column, $locale, $value)
	{
	return DB::connection($connection)->table($table)->where("{$column}->{$locale}", '=', $value);
	}

According to my Google searches, MySQL is supposed to be case insensitive when using a collation that ends in _ci (I'm using utf8mb4_unicode_ci). But that's not what my tests are saying...

Is anyone else having this issue?

[Tahiaji]

yep

[ivanvermeyen]

Fixed by #22

[ivanvermeyen]

Just for later reference: using a LIKE query seems to be case insensitive too and improves compatibility with other database types than MySQL.

[eleftrik]

@ivanvermeyen

I got the same problem while using UniqueTranslationRule validation rule with Filament.
I have a mytable.name translatable field. Here the Filament Resource:

    public static function form(Form $form): Form
    {
        return $form
            ->schema([
                Forms\Components\TextInput::make('name')
                    ->rules([
                        fn (Forms\Get $get) => UniqueTranslationRule::for('mytable', 'name')
                            ->ignore($get('id')),
                    ])
                    ->required(),
            ]);

Validation works pretty well when respecting the case (I cannot use Foo twice), but seems to fail when using different case (I can save a record with name=Foo and another with name=foo using the same locale, but I shouldn't be allowed).

Database is MySQL with utf8mb4_unicode_ci collation.

Am I missing or doing anything wrong?

Thanks!

[eleftrik]

@ivanvermeyen Thanks for reopening the issue

[ivanvermeyen]

@eleftrik I'm a bit lost tho as why this is not working.
My tests claim that its working and when I manually try it in my own project it works too.
I'm not using Filament tho.
For the Filament compatibility I've been trusting people's contributions.

[MennoM]

@ivanvermeyen I've run into this issue today as well, I'm using MySQL.
Is it possible to use the converting of the JSON column in the where searches of the findTranslation like in the following answer?
https://stackoverflow.com/questions/46702159/mysql-like-query-in-json-field
Which would result in a ->whereRaw("CONVERT($column using 'utf8') LIKE '%\"{$locale}\": \"{$value}\"%'") (this can ofcourse be cleaned up a bit)