Spatie Translatable Listing column searchable exception in sql query when searching

[dev3k]

Package

filament/filament

Package Version

v3.0.95

Laravel Version

v10.31.0

Livewire Version

v3.1.0

PHP Version

PHP 8.2.9

Problem description

When you search a translatable column I get the following error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'json_extract(title, "$.en")' in 'where clause'

SELECT count(*) AS aggregate FROM `posts` WHERE (`json_extract(title, "$`.`en")` LIKE %AA%)

Expected behavior

it should be like

SELECT count(*) AS aggregate FROM `posts` WHERE json_extract(`title`, "$.en") LIKE '%AA%'

Steps to reproduce

just visit http://json-column.test/admin/posts?tableSearch=AA

Reproduction repository

https://github.com/dev3k/json-column

Relevant log output

No response

[dev3k]

similar to #7537

[dev3k]

I did some debugging and found this https://github.com/filamentphp/filament/commit/c89f86bad00dc42dca8fa467b21d48e2c66687ea PR https://github.com/filamentphp/filament/pull/8784

search_collation always return null on mysql

$databaseConnection->getConfig('search_collation')

to make it work it should've been 'collation' instead of 'search_collation'

$databaseConnection->getConfig('collation')

I dont know why it's 'search_collation' maybe postgres ? I never used it before 😄

[Ahmant]

Hello, Same issue here.

But to address the issue (until it is resolved by the package), I performed the search query independently, as follows:

public static function table(Table $table): Table
{
    $activeLocale = $table->getLivewire()->activeLocale ?? 'en'; // 👈 Here

    return $table
        ->columns([
            TextColumn::make('title')
                // 👇 Here
                ->searchable(query: fn (Builder $query, string $search) => $query->whereRaw("JSON_EXTRACT(LOWER(title), '$.$activeLocale') LIKE LOWER('%$search%')")) // 👈 Here
        ])
}

[zumbidoweb]

+1

[inalto]

please look @ my pull request https://github.com/filamentphp/filament/pull/9732

this fixed the issue for me