Filament•11mo ago

Error when i search in table

I got this error when i search in the table i am using filament-spatie-translatable plugin By the way the "add - update " work good but when i search the error appear

 SQLSTATE[42S22]: Column not found: 1054 Unknown column 'json_extract(title, "$.ar")' in 'where clause'
SELECT
  count(*) AS aggregate
FROM
  `slider`
WHERE
  (
    `json_extract(title, "$`.`ar")` LIKE % copnay%
    OR `json_extract(description, "$`.`ar")` LIKE % copnay%
    OR `url` LIKE % شرك %
    OR `json_extract(button_action, "$`.`ar")` LIKE % copnay%
  )

 SQLSTATE[42S22]: Column not found: 1054 Unknown column 'json_extract(title, "$.ar")' in 'where clause'
SELECT
  count(*) AS aggregate
FROM
  `slider`
WHERE
  (
    `json_extract(title, "$`.`ar")` LIKE % copnay%
    OR `json_extract(description, "$`.`ar")` LIKE % copnay%
    OR `url` LIKE % شرك %
    OR `json_extract(button_action, "$`.`ar")` LIKE % copnay%
  )

i am using the package ----> https://filamentphp.com/plugins/filament-spatie-translatable

Filament

Spatie Translatable by Filament - Filament

Filament support for Spatie's Laravel Translatable package.

Solution:

The New Code ``` public function applySearchConstraintToQuery(Builder $query, string $column, string $search, string $whereClause, bool $isCaseInsensitivityForced = false): Builder {...

Jump to solution

15 Replies

Dennis Koch•11mo ago

What DB and version are you using?

Yacoub Al-haidariOP•11mo ago

Apache/2.4.56 (Win64) OpenSSL/1.1.1t PHP/8.2.4 Database client version: libmysql - mysqlnd 8.2.4 PHP extension: mysqli Documentation curl Documentation mbstring Documentation PHP version: 8.2.4

Dennis Koch•11mo ago

That doesn't answer the question 😅 MySQL, MariaDB, Postgres, SQLite?

Yacoub Al-haidariOP•11mo ago

mysql

Dennis Koch•11mo ago

Which version?

Yacoub Al-haidariOP•11mo ago

8.2.4

Dennis Koch•11mo ago

Can you share the code for the column?

Yacoub Al-haidariOP•11mo ago

okay ->columns([ ImageColumn::make('image') ->label('image') ->size(40) ->disk('public') ->visibility('private'), Tables\Columns\TextColumn::make('title') ->label('title') ->searchable(), Tables\Columns\TextColumn::make('description') ->label('description') ->searchable(), Tables\Columns\TextColumn::make('url') ->label('url') ->searchable(), Tables\Columns\TextColumn::make('button_action') ->label('button_action') ->searchable(), Tables\Columns\TextColumn::make('created_at') ->dateTime() ->sortable() ->toggleable(isToggledHiddenByDefault: true), Tables\Columns\TextColumn::make('updated_at') ->dateTime() ->sortable() ->toggleable(isToggledHiddenByDefault: true), ])

Dennis Koch•11mo ago

Sounds like an issue with the package. Can you reproduce this in a fresh project and open an issue on GitHub?

Yacoub Al-haidariOP•11mo ago

okay @Dennis Koch i have solved this Wow i edited the function applySearchConstraintToQuery in the following path project name \vendor\filament\spatie-laravel-translatable-plugin\src\SpatieLaravelTranslatableContentDriver.php the old code was

 

  public function applySearchConstraintToQuery(Builder $query, string $column, string $search, string $whereClause, bool $isCaseInsensitivityForced = false): Builder
    {
        /** @var Connection $databaseConnection */
        $databaseConnection = $query->getConnection();

        $column = match ($databaseConnection->getDriverName()) {
            'pgsql' => "{$column}->>'{$this->activeLocale}'",
            default => "json_extract({$column}, \"$.{$this->activeLocale}\")",
        };

        $caseAwareSearchColumn = $isCaseInsensitivityForced ?
            new Expression("lower({$column})") :
            $column;

        return $query->{$whereClause}(
            $caseAwareSearchColumn,
            'like',
            "%{$search}%",
        );
    }

 

  public function applySearchConstraintToQuery(Builder $query, string $column, string $search, string $whereClause, bool $isCaseInsensitivityForced = false): Builder
    {
        /** @var Connection $databaseConnection */
        $databaseConnection = $query->getConnection();

        $column = match ($databaseConnection->getDriverName()) {
            'pgsql' => "{$column}->>'{$this->activeLocale}'",
            default => "json_extract({$column}, \"$.{$this->activeLocale}\")",
        };

        $caseAwareSearchColumn = $isCaseInsensitivityForced ?
            new Expression("lower({$column})") :
            $column;

        return $query->{$whereClause}(
            $caseAwareSearchColumn,
            'like',
            "%{$search}%",
        );
    }

Solution

Yacoub Al-haidari•11mo ago

The New Code

 
public function applySearchConstraintToQuery(Builder $query, string $column, string $search, string $whereClause, bool $isCaseInsensitivityForced = false): Builder
    {
        /** @var Connection $databaseConnection */
        $databaseConnection = $query->getConnection();
    
        $columnAlias = match ($databaseConnection->getDriverName()) {
            'pgsql' => "{$column}->>'{$this->activeLocale}'",
            default => "JSON_UNQUOTE(JSON_EXTRACT({$column}, '$.{$this->activeLocale}'))",
        };
    
        $subquery = $query->selectRaw('1')
            ->whereRaw("$columnAlias LIKE ?", ["%{$search}%"]);
    
        $caseAwareSearchColumn = $isCaseInsensitivityForced ?
            new Expression("LOWER({$column})") :
            $column;
    
        return $query->{$whereClause}(function ($query) use ($subquery, $caseAwareSearchColumn, $search) {
            $query->whereExists($subquery)
                ->orWhere(function ($query) use ($caseAwareSearchColumn, $search) {
                    $query->where($caseAwareSearchColumn, 'LIKE', "%{$search}%");
                });
        });
    }

 
public function applySearchConstraintToQuery(Builder $query, string $column, string $search, string $whereClause, bool $isCaseInsensitivityForced = false): Builder
    {
        /** @var Connection $databaseConnection */
        $databaseConnection = $query->getConnection();
    
        $columnAlias = match ($databaseConnection->getDriverName()) {
            'pgsql' => "{$column}->>'{$this->activeLocale}'",
            default => "JSON_UNQUOTE(JSON_EXTRACT({$column}, '$.{$this->activeLocale}'))",
        };
    
        $subquery = $query->selectRaw('1')
            ->whereRaw("$columnAlias LIKE ?", ["%{$search}%"]);
    
        $caseAwareSearchColumn = $isCaseInsensitivityForced ?
            new Expression("LOWER({$column})") :
            $column;
    
        return $query->{$whereClause}(function ($query) use ($subquery, $caseAwareSearchColumn, $search) {
            $query->whereExists($subquery)
                ->orWhere(function ($query) use ($caseAwareSearchColumn, $search) {
                    $query->where($caseAwareSearchColumn, 'LIKE', "%{$search}%");
                });
        });
    }

this new code support multiple languages English and Arabic

Dennis Koch•11mo ago

Please open a PR then. Thanks!

Yacoub Al-haidariOP•11mo ago

sorry I didn't get you

Tieme•11mo ago

If you want this to be fixed in the core package you need to create a pull request : https://github.com/filamentphp/filament/pulls

Yacoub Al-haidariOP•11mo ago

I have created the pull request for this issue .

Gaming

Programming

Error when i search in table