Search Issue with Special Characters in PostgreSQL and Laravel Filament

Hello,

I am developing a project using PostgreSQL as the database and Laravel Filament for the admin panel. Special characters (such as İ, ı, Ş, ş, Ğ, ğ, Ü, ü, etc.) are stored and queried correctly in the database. However, I am facing issues when searching with these special characters on the Filament front-end.

Issue Details: Database: PostgreSQL Character Set: UTF-8 Collation: tr_TR.UTF-8 (Supports special characters) Filament Version: 3.x

For example, in my branches table, I have the following records:

id name 1 Şeker When using Filament's Select or Searchable feature:

->searchable() If I type eker, the record Şeker is returned. However, if I type Şeker (with the capital Ş), no results are found. The same issue occurs with other special characters: Characters like Ş, Ü, Ğ, Ç, Ö cannot be used in searches.

If I am going to customize the query in each searchable section, it means hundreds of changes. Is there a way to fix this in general?

rmznatly

1 year ago

Level 1

When I take the resulting query and run it with PG Query Tool, the data is listed but there are no results. I don't understand what is being blocked.

->relationship(name: 'customerAccountTermsOfSaleAccounts', titleAttribute: 'title', modifyQueryUsing: function ($query, $search) {
    return $query->whereRaw("unaccent(title) ilike unaccent('%" . $search . "%')");
})

newbie360

1 year ago

Best Answer

Level 24

@rmznatly What you mean

the data is listed but there are no results

i don't know what is the language for Ş

i tested search Şeker and can list the record by the database table column is utf8mb4_unicode_ci

and by default Filament search is case-sensitive, you can see that in Laravel-Debugbar

Filament provide a method to search case-insensitive, you can check the query in Laravel-Debugbar, the side effect is might be effect performace

Tables\Columns\TextColumn::make('name')
    ->label(__('Name'))
    ->searchable()
    ->forceSearchCaseInsensitive()

some language such as CJK has problem in the database level for unique, sorting, case-sensitive, case-insensitive

for me, i will use collation *_bin, if the string column in the database will contain NON-English chars.

$table->string('name')->collation('utf8mb4_bin')->nullable();

1 like

rmznatly

1 year ago

Level 1

@newbie360

$table->string('title')->collation('en_TR.UTF-8')->change();

This solved the problem. Interesting situation, thank you. Is there a way to make this standard? I think I need to update all columns.

newbie360

1 year ago

Level 24

@rmznatly I don't know about PostgreSQL, in mysql can set it via https://dev.mysql.com/doc/refman/8.4/en/charset-database.html

1 like

Please or to participate in this conversation.