Package
filament/filament
Package Version
v3.3
Laravel Version
v12.13
Livewire Version
v3.6
PHP Version
PHP 8.4.5
Problem description
My model need translations so I use a secondary table to store it.
In this example, I have Sector (table: hike_sectors) and SectorTranslation (table: hike_sector_translations).
I want in my table to search using default language name. I have created a service to custom automatically the searchable query. But there is an issue by using LEFT JOIN.
Here is my code :
class TranslatableTableService
{
public function sortable(Builder $query, string $direction, string $attribute = 'id', $languageId = null): Builder
{
$languageId ??= LanguageHelper::getDefaultId();
$model = $query->getModel();
$translationModel = new ($model->getTranslationModelClass());
$mainTable = $model->getTable();
$translationTable = $translationModel->getTable();
$alias = 't_' . $translationTable;
$foreignKey = $model->translations()->getForeignKeyName();
return $query
->leftJoin("{$translationTable} as {$alias}", function ($join) use ($alias, $foreignKey, $mainTable, $languageId) {
$join->on("{$alias}.{$foreignKey}", '=', "{$mainTable}.id")
->where("{$alias}.language_id", '=', $languageId);
})
->orderBy("{$alias}.{$attribute}", $direction)
->select("{$mainTable}.*");
}
public function searchable(Builder $query, string $search, string $attribute, $languageId = null): Builder
{
$languageId ??= LanguageHelper::getDefaultId();
$model = $query->getModel();
$translationModel = new ($model->getTranslationModelClass());
$mainTable = $model->getTable();
$translationTable = $translationModel->getTable();
$alias = 't_' . $translationTable;
$foreignKey = $model->translations()->getForeignKeyName();
return $query
->leftJoin("{$translationTable} as {$alias}", function ($join) use ($alias, $foreignKey, $mainTable, $languageId) {
$join->on("{$alias}.{$foreignKey}", '=', "{$mainTable}.id")
->where("{$alias}.language_id", '=', $languageId);
})
->where("{$alias}.{$attribute}", 'like', "%$search%")
->select("{$mainTable}.*");
}
}
public static function table(Table $table): Table
{
return $table
->columns([
Tables\Columns\TextColumn::make('name')
->label('Nom')
->state(function (Sector $record) {
return $record->translated('name');
})
->searchable(query: function (Builder $query, string $search, TranslatableTableService $service): Builder {
return $service->searchable($query, $search, 'name');
}),
Tables\Columns\TextColumn::make('slug')
->label('Url')
->state(function (Sector $record) {
return $record->translated('slug');
}),
])
->defaultSort('position')
->reorderable('position')
->filters([
//
])
->actions([
TranslatableEditAction::make(),
Tables\Actions\DeleteAction::make(),
])
->bulkActions([
//
]);
}
Here is the issue (don't pay any attention to the letter t at the beginning of the table name, it's an error handling of my service) :
SQLSTATE[42S22]: Column not found: 1054 Unknown column 't_hike_sector_translations.name' in 'where clause' (Connection: mysql, SQL: select count(*) as aggregate from hike_sectors where ((t_hike_sector_translations.name like %tour%)))
This error come from filament/tables/src/Concerns/CanPaginateRecords.php:35 file :
$total = $query->toBase()->getCountForPagination();
So I try to add a sortable to my column :
->sortable(query: function (Builder $query, string $direction, TranslatableTableService $service): Builder {
return $service->sortable($query, $direction, 'name');
})
Same issue.
BUT using ->defaultSort('name') instead of ->defaultSort('position') that work perfectly !!
I don't know why except that the LEFT JOIN is used when I make all this thing otherwise not. But to use a custom search using left join I have to use ->sortable to my column and ->defaultSort at my specific column to my table to make work the search.
Expected behavior
I just want to use ->searchable using custom query with LEFT JOIN independently at other query
Steps to reproduce
Try to use ->searchable in column of table using custom query with LEFT JOIN
Reproduction repository (issue will be closed if this is not valid)
https://github.com/Alpaweb/FilamentSearchableColumn
Relevant log output
