Is there a way to search by an aggregated column?
I've made a TextColumn in my datatable:
TextColumn::make('fullname')
->searchable( isIndividual: true)
However the search gives an error because customers.fullname column doesn't exist- it is an aggregated attribute in the Customer model.
I know I could make it work by using concatenating the actual database columns together:
TextColumn::make('fullname')
->searchable(query: function (Builder $query, string $search): Builder {
return $query->where(\DB::raw('CONCAT_WS( ' ', customers.firstname, customers.lastname)'), 'ILIKE', "%{$search}%");
}, isIndividual: true)
....but I'd really prefer to keep the database name and columns out of this and just rely on the aggregated attribute if it's possible. (There are some more complicated scenarios round the corner and they could get very messy very quick.)
3 Replies
Solution
You can use a virtual column on database level
Searching happens on DB level so there is no way to search by PHP/Laravel attributes
Ok thanks