Filament•3mo ago

Cannot search by table columns with same name but one is from relation

Hello I have a table with 2 columns First is the resource name, second is the related customer name. On both I have used ->searchable() . So If I search by resource name the filter works but it works only for the resource name. Even if you comment out first column name and try to search for customer it will continue search only for the resource name. SQL query does not change at all. Always search in the first column name of the resource. First column name of the resource is json but it think this is irrelevant for the issue. If I change customer.name to customer.phone search starts working as usual for both fields. Any ideas how to fix it?

 return $table
            ->columns([
                Tables\Columns\TextColumn::make('name')
                    ->searchable()
                    ->sortable(),
                Tables\Columns\TextColumn::make('customer.name')
                    ->searchable()
                    ->sortable(),

 return $table
            ->columns([
                Tables\Columns\TextColumn::make('name')
                    ->searchable()
                    ->sortable(),
                Tables\Columns\TextColumn::make('customer.name')
                    ->searchable()
                    ->sortable(),

Bonus Q: how you handle search in json fields, because it differs from normal fields (because of lower) and this can lead to confusion when working with the product.

2 Replies

Povilas K•3mo ago

Was weird to me that it doesn't work, so out of curiosity tried to reproduce the situation with simple User and Customer models and copy-pasted your code exactly as it is, it works well for me, searches in both columns. But in my case both columns are strings and not jsons, so probably your problem is about json, after all.

tuseto•2mo ago

Sorry, I see your response just now. It is very strange the result query on search is: select * from properties

 where (lower(json_extract(name, "$.en")) like '%saepe%' or lower(json_extract(name, "$.en")) like '%saepe%' or lower(json_extract(name, "$.en")) like '%saepe%') and

properties.deleted_at is null order by properties.id asc limit 10 offset 0 When I remove the translatable column it starts working again. This is a bit disappointing 😦

 Tables\Columns\TextColumn::make('name')
    ->searchable()
    ->sortable(),
Tables\Columns\TextColumn::make('customer.name')
    ->searchable(['customers.name'])
    ->sortable(),
Tables\Columns\TextColumn::make('propertyType.name')
    ->searchable(['property_types.name'])
    ->sortable(),

 Tables\Columns\TextColumn::make('name')
    ->searchable()
    ->sortable(),
Tables\Columns\TextColumn::make('customer.name')
    ->searchable(['customers.name'])
    ->sortable(),
Tables\Columns\TextColumn::make('propertyType.name')
    ->searchable(['property_types.name'])
    ->sortable(),

Set searchable condition to relation table and column name (customers.name) works and search starts working. But this way there is again a bit strange thing.

select count(*) as aggregate from "properties" where (lower(json_extract(name, "$.en")) like '%Misho%' or exists (select * from "customers" where "properties"."customer_id" = "customers"."id" and "customers"."name" like '%Misho%') or exists (select * from "property_types" where "properties"."property_type_id" = "property_types"."id" and "property_types"."name" like '%Misho%')) and "properties"."deleted_at" is null

There are 3 columns: - property name (which is json and is translatable) - customer name (which is string and is not translatable) - property type name (which is json and is translatable) In the result sql above properties name is searched like a json but property type name part of the query does not use the json way by filament. I don't know how I will unify the search everywhere easily because this different kind of queries will result in different type of search (uppercase, lowercase and etc) and will drive the users crazy.

Gaming

Programming

Cannot search by table columns with same name but one is from relation