Filament•3mo ago

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')),

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();

$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'

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'customerSupportManager()' in 'where clause'

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

3 Replies

ddoddsr•3mo ago

bump bump?

Solution

Dennis Koch•3mo ago

ddoddsr•3mo ago

Correct, not a column in database but a relationship on the Campaign Model. I'll try a leftJoin.

Gaming

Programming

widget query filter on related field