F
Filament•3mo ago
Aivirth

Table widget custom filter does not update table result

I've been asked by my client to have an overview table with the most popular coupons on our site through a list with filters. Therefore i proceeded to create the following table widget 
class MostVisitedCouponsTableWidget extends BaseWidget
{
    public function getColumnSpan(): int|string|array
    {
        return ['default' => 'full'];
    }
    public function table(Table $table): Table
    {
        return $table
            ->paginated(false)
            ->query(
                Coupon::query()
                    ->select(["id", "title",  "store_id", "status"])
                    ->scopes(['published'])
                    ->withCount([
                        'impressions'
                    ])
                    ->having('impressions_count', '>', 0)
                    ->orderByDesc('impressions_count')
                    ->limit(10)
            )
            ->columns([
                TextColumn::make('id'),
                TextColumn::make('title'),
                TextColumn::make('store.business_name'),
                TextColumn::make('impressions_count')->badge(),
            ]);
    }
}
class MostVisitedCouponsTableWidget extends BaseWidget
{
    public function getColumnSpan(): int|string|array
    {
        return ['default' => 'full'];
    }
    public function table(Table $table): Table
    {
        return $table
            ->paginated(false)
            ->query(
                Coupon::query()
                    ->select(["id", "title",  "store_id", "status"])
                    ->scopes(['published'])
                    ->withCount([
                        'impressions'
                    ])
                    ->having('impressions_count', '>', 0)
                    ->orderByDesc('impressions_count')
                    ->limit(10)
            )
            ->columns([
                TextColumn::make('id'),
                TextColumn::make('title'),
                TextColumn::make('store.business_name'),
                TextColumn::make('impressions_count')->badge(),
            ]);
    }
}
6 Replies
Aivirth
AivirthOP•3mo ago
however when i try to add this custom filter with a date range the table does not update itself , i can confirm the query is correct because i logged the last return query with a toRawSql() and pasted into my db gui and the results are correct 
    ->filters([
        SelectFilter::make('store_id')
            ->label(StoreResource::getModelLabel())
            ->relationship('store', 'business_name'),
        Filter::make('impressions_in_period')
            ->form([
                DateRangePicker::make('created_at')
            ])
            ->query(function (Builder $query, array $data): Builder {
                $parsed = $this->parseDateRangePickerValues($data['created_at']);
                if (empty($parsed)) {
                    return $query->withCount('impressions');
                }

                return $query->withCount([
                    'impressions' => function (Builder $query) use ($parsed) {
                        $query->whereBetween('created_at', [$parsed[0], $parsed[1]]);
                    }
                ]);
            })
    ]);
    ->filters([
        SelectFilter::make('store_id')
            ->label(StoreResource::getModelLabel())
            ->relationship('store', 'business_name'),
        Filter::make('impressions_in_period')
            ->form([
                DateRangePicker::make('created_at')
            ])
            ->query(function (Builder $query, array $data): Builder {
                $parsed = $this->parseDateRangePickerValues($data['created_at']);
                if (empty($parsed)) {
                    return $query->withCount('impressions');
                }

                return $query->withCount([
                    'impressions' => function (Builder $query) use ($parsed) {
                        $query->whereBetween('created_at', [$parsed[0], $parsed[1]]);
                    }
                ]);
            })
    ]);
anyone ?
ChesterS
ChesterS•3mo ago
Does the store_id filter work as expected?
Aivirth
AivirthOP•3mo ago
yes, actually i added it to double check if the filters in general were working properly
ChesterS
ChesterS•3mo ago
Hmm this is weird. I tried a simplified version of your setup and it seems to work as expected. 
->filters([
    Filter::make('date')
        ->form([
            DatePicker::make('from'),
            DatePicker::make('to'),
        ])
        ->query(function ( $query, array $data) {
            if (empty($data['from']) || empty($data['to']))  {
                return $query;
            }
            return $query->whereBetween('created_at', [$data['from'], $data['to']]);
        })
])
->filters([
    Filter::make('date')
        ->form([
            DatePicker::make('from'),
            DatePicker::make('to'),
        ])
        ->query(function ( $query, array $data) {
            if (empty($data['from']) || empty($data['to']))  {
                return $query;
            }
            return $query->whereBetween('created_at', [$data['from'], $data['to']]);
        })
])
There's nothing obviously wrong with this so I guess follow general debugging steps? Do you have debugbar enabled? Can you check the request that is sent. Ideally, check wtf is going on using Xdebug.
Aivirth
AivirthOP•3mo ago
oddly when i log the query it's correct but when it's applied to the table it's completely ignored, maybe there's something i'm missing on how the withCount is handled internally by filament 
select `id`, `title`, `store_id`, `status`, (select count(*) from `coupon_visits` where `coupons`.`id` = `coupon_visits`.`coupon_id`) as `impressions_count` from `coupons` where `status` = 'published' and `coupons`.`deleted_at` is null order by `impressions_count` desc limit 10;
select `id`, `title`, `store_id`, `status`, (select count(*) from `coupon_visits` where `coupons`.`id` = `coupon_visits`.`coupon_id`) as `impressions_count` from `coupons` where `status` = 'published' and `coupons`.`deleted_at` is null order by `impressions_count` desc limit 10;
ChesterS
ChesterS•3mo ago
try using ->modifyBaseQueryUsing instead of ->query so 
    ->filters([
        Filter::make('impressions_in_period')
            ->form([ ...  ])
            ->modifyBaseQueryUsing(function (Builder $query, array $data): Builder {
                ...
                ]);
            })
    ]);
    ->filters([
        Filter::make('impressions_in_period')
            ->form([ ...  ])
            ->modifyBaseQueryUsing(function (Builder $query, array $data): Builder {
                ...
                ]);
            })
    ]);

Did you find this page helpful?