FilamentF
Filament•2y ago
ddoddsr

widget query filter on related field

I followed the instructions and have a summarizing widget table for Campaign Status.
It has filters on the Dashboard: Client and Service Manager.
It filters fine on client_id, a field in the campaign table.

Dashboard:
public function filtersForm(Form $form): Form
...
   Select::make('client_id')
     ->relationship(name:  'client', titleAttribute: 'name')
     ->options(function (?Model $record) {
         return Client::query()->pluck('name', 'id');
     }),
   Select::make('csm_id')->label('Customer Support Manager')
      ->options(User::where('customer_support_manager', true)->pluck('name', 'id')),

The other filter returns the correct id to the widget because I can log the values in the widget.
But I don't understand how to filter by related data.

Campaign BelongsTo a Client, a Client BelongsTo a Customer Support Manager
Here is my Widget table
$client_id = (int) $this->filters['client_id'] ?? null;
$csm_id = (int) $this->filters['csm_id'] ?? null;
return $table
->query(
    $data = Campaign::query()
    ->with(['client'])
    ->where(function($query) use ($client_id){
        if ($client_id) {
            $query->where('client_id',  $client_id);
        }
    })
    ->where(function($query) use ($csm_id){
        if ($csm_id) {
            logger('CSM ' .$csm_id );
            // Tried several things here
            $query->where('customerSupportManager()', $csm_id);  // belongsToThrough
        }
    })
)
->columns([
    Tables\Columns\TextColumn::make('Status')
        ->summarize(Summarizer::make()
        ->using(fn (\Illuminate\Database\Query\Builder $query): string => $query->count('status')))
])
->groups([Group::make('status')])
->defaultGroup('status')->groupsOnly();


Error: 
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'customerSupportManager()' in 'where clause'
image.png
Solution
Yeah, customSupportManager() is very likely not a column in your DB. You can't use Laravel relation on a DB directly.

$query->where('customerSupportManager()', $csm_id); // belongsToThrough

I guess you need some left join leftJoin() to get the needed columns
Jump to solution
Was this page helpful?