Filament•4mo ago•

1 reply

Postgres throws "undefined column" when using searchable in tables

✅ SolvedTable builder4️⃣v4

Using Postgres 17, Filament v4.2.0, Laravel v12.37.0, Livewire v3.6.4, PHP 8.4.11.

I'm getting an

Undefined column

Undefined column

error from Postgres when i use

searchable

searchable

on my table.

Here's a simplified version of the table;

return $table  
    ->columns([  
        TextColumn::make('name')  
            ->searchable(),  
    ])
    ->searchable([  
        'notes',  
    ]);

return $table  
    ->columns([  
        TextColumn::make('name')  
            ->searchable(),  
    ])
    ->searchable([  
        'notes',  
    ]);

When i search something (

john

john

in this case), this results in the following query:

select
  *
from
  "contacts"
where
  lower("name" :: text) :: text like '%john%'
  or """notes""::text" :: text like '%John%'

select
  *
from
  "contacts"
where
  lower("name" :: text) :: text like '%john%'
  or """notes""::text" :: text like '%John%'

And (understandably) this error

SQLSTATE[42703]: Undefined column: 7 ERROR: column ""notes"::text" does not exist LINE 1: ...null))) and (lower("name"::text)::text like $1 or """notes""... ^ (Connection: pgsql, SQL: select count(*) as aggregate from "contacts" where ((("contacts"."deleted_at" is null))) and (lower("name"::text)::text like %john% or """notes""::text"::text like %John%)) (View: /var/www/vendor/filament/tables/resources/views/index.blade.php) (View: /var/www/vendor/filament/tables/resources/views/index.blade.php) (View: /var/www/vendor/filament/tables/resources/views/index.blade.php)

SQLSTATE[42703]: Undefined column: 7 ERROR: column ""notes"::text" does not exist LINE 1: ...null))) and (lower("name"::text)::text like $1 or """notes""... ^ (Connection: pgsql, SQL: select count(*) as aggregate from "contacts" where ((("contacts"."deleted_at" is null))) and (lower("name"::text)::text like %john% or """notes""::text"::text like %John%)) (View: /var/www/vendor/filament/tables/resources/views/index.blade.php) (View: /var/www/vendor/filament/tables/resources/views/index.blade.php) (View: /var/www/vendor/filament/tables/resources/views/index.blade.php)

I found that adding a driver check in the

generate_search_column_expression

generate_search_column_expression

helper method fixes it for me. It makes sure that for postgres it always returns an expression, never a string. I'm not sure if this is a good solution tho.
https://github.com/filamentphp/filament/blob/4.x/packages/support/src/helpers.php#L294

if (
    $driverName === 'pgsql' ||
    str($column)->contains('(') || // This checks if the column name probably contains a raw expression like `lower()` or `json_extract()`.
    filled($collation)
) {
    return new Expression($column);
}

if (
    $driverName === 'pgsql' ||
    str($column)->contains('(') || // This checks if the column name probably contains a raw expression like `lower()` or `json_extract()`.
    filled($collation)
) {
    return new Expression($column);
}

Am i doing something wrong or is there another solution?

Postgres throws "undefined column" when using searchable in tables

✅ SolvedTable builder4️⃣v4

Using Postgres 17, Filament v4.2.0, Laravel v12.37.0, Livewire v3.6.4, PHP 8.4.11.

I'm getting an

Undefined column

Undefined column

error from Postgres when i use

searchable

searchable

on my table.

Here's a simplified version of the table;

return $table  
    ->columns([  
        TextColumn::make('name')  
            ->searchable(),  
    ])
    ->searchable([  
        'notes',  
    ]);

return $table  
    ->columns([  
        TextColumn::make('name')  
            ->searchable(),  
    ])
    ->searchable([  
        'notes',  
    ]);

When i search something (

john

john

in this case), this results in the following query:

select
  *
from
  "contacts"
where
  lower("name" :: text) :: text like '%john%'
  or """notes""::text" :: text like '%John%'

select
  *
from
  "contacts"
where
  lower("name" :: text) :: text like '%john%'
  or """notes""::text" :: text like '%John%'

And (understandably) this error

SQLSTATE[42703]: Undefined column: 7 ERROR: column ""notes"::text" does not exist LINE 1: ...null))) and (lower("name"::text)::text like $1 or """notes""... ^ (Connection: pgsql, SQL: select count(*) as aggregate from "contacts" where ((("contacts"."deleted_at" is null))) and (lower("name"::text)::text like %john% or """notes""::text"::text like %John%)) (View: /var/www/vendor/filament/tables/resources/views/index.blade.php) (View: /var/www/vendor/filament/tables/resources/views/index.blade.php) (View: /var/www/vendor/filament/tables/resources/views/index.blade.php)

SQLSTATE[42703]: Undefined column: 7 ERROR: column ""notes"::text" does not exist LINE 1: ...null))) and (lower("name"::text)::text like $1 or """notes""... ^ (Connection: pgsql, SQL: select count(*) as aggregate from "contacts" where ((("contacts"."deleted_at" is null))) and (lower("name"::text)::text like %john% or """notes""::text"::text like %John%)) (View: /var/www/vendor/filament/tables/resources/views/index.blade.php) (View: /var/www/vendor/filament/tables/resources/views/index.blade.php) (View: /var/www/vendor/filament/tables/resources/views/index.blade.php)

I found that adding a driver check in the

generate_search_column_expression

generate_search_column_expression

if (
    $driverName === 'pgsql' ||
    str($column)->contains('(') || // This checks if the column name probably contains a raw expression like `lower()` or `json_extract()`.
    filled($collation)
) {
    return new Expression($column);
}

if (
    $driverName === 'pgsql' ||
    str($column)->contains('(') || // This checks if the column name probably contains a raw expression like `lower()` or `json_extract()`.
    filled($collation)
) {
    return new Expression($column);
}

Am i doing something wrong or is there another solution?

Postgres throws "undefined column" when using searchable in tables

Postgres throws "undefined column" when using searchable in tables

Similar Threads

Similar Threads

Similar Threads