Jul 22, 2023

Level 2

FilamentPHP filter

Why is the Laravel FilamentPHP filter query not updating the records? Here is the code in the filament resource file

Filter::make('created_at')
    ->form([
        Forms\Components\DatePicker::make('created_from'),
        Forms\Components\DatePicker::make('created_until'),
    ])
    ->query(function (Builder $query, array $data): Builder {
        return $query->when(
            $data['created_from'] && empty($data['created_until']),
            fn(Builder $query): Builder => $query
                ->withCount([
                    'surgeries as surgeries_count' => function (Builder $builder) use ($data) {
                        $builder
                            ->where('surgeries.date', '>=', $data['created_from']);
                    }
                ])
        )
            ->when(
                empty($data['created_from']) && $data['created_until'],
                fn(Builder $query): Builder => $query
                    ->withCount([
                        'surgeries as surgeries_count' => function (Builder $builder) use ($data) {
                            $builder
                                ->where('surgeries.date', '<=', $data['created_until']);
                        }
                    ])
            )
            ->when(
                $data['created_from'] && $data['created_until'],
                fn(Builder $query): Builder => $query
                    ->withCount([
                        'surgeries as surgeries_count' => function (Builder $builder) use ($data) {
                            $builder
                                ->whereBetween('surgeries.date', [
                                    Carbon::parse($data['created_from'])->startOfDay(),
                                    Carbon::parse($data['created_until'])->endOfDay(),
                                ]);
                        }
                    ])
            );
    })

I think it's only updating if I apply the where clause in the query!. like this

Filter::make('created_at')
	....
	->withCount([
                                        'surgeries as surgeries_count' => function (Builder $builder) use ($data) {
                                            $builder
                                                ->whereBetween('surgeries.date', [
                                                    Carbon::parse($data['created_from'])->startOfDay(),
                                                    Carbon::parse($data['created_until'])->endOfDay(),
                                                ]);
                                        }
                                    ])
                                    ->whereNotNull('deleted_at')
                            );
                    })

the output query

select count(*) as aggregate from `users` where (`deleted_at` is not null) and `users`.`deleted_at` is null

kokoshneta

2 years ago

Level 27

Why is the Laravel FilamentPHP filter query not updating the records? I think it's only updating if I apply the where clause in the query

I don’t understand what you mean by this. Filters are meant to filter which rows you fetch from the database – what records were you expecting it to update?

Your filter looks unnecessarily complicated to me. Why so many repetitions? This should be enough:

Filter::make('created_at')
  ->form([
    Forms\Components\DatePicker::make('created_from'),
    Forms\Components\DatePicker::make('created_until'),
  ])
  ->query(function (Builder $query, array $data): Builder {
    return $query->withCount([
      'surgeries as surgeries_count' => function (Builder $builder) use ($data) {
        if ($from  = $date['created_from'])  $builder->where('surgeries.date', '>=', $from);
        if ($until = $date['created_until']) $builder->where('surgeries.date', '<=', $until);
        return $builder;
      }
    ]);
  })
;

Why would you add ->whereNotNull('deleted_at')? That will attempt to only fetch soft-deleted rows. But since you seem to have a global soft-deletes scope that filters out deleted records, it will mean that it never fetches any rows at all.

Umr Farooq

2 years ago

Level 2

Hey @kokoshneta, sorry for the convince. As in the demo code, I've to also apply the where between clause in the query to fetch the records given two dates. The issue is the filament resource should update the record in the table. but, it's not updating. please let me know if you have any questions. If you can't understand the problem I'll attach the full code in the chat. thanks

kokoshneta

2 years ago

Level 27

@Umr Farooq No, you don’t need ->whereBetween(). If you have ->where('surgeries.date', '>=', $from)->where('surgeries.date', '<=', $until), that does the exact same thing.

There is no difference between the following two statements – they do the exact same thing:

SELECT * FROM surgeries WHERE date BETWEEN '2023-07-20' AND '2023-07-23'

SELECT * FROM surgeries WHERE date >= '2023-07-20' AND date <= '2023-07-23'

———

The issue is the filament resource should update the record in the table. but, it's not updating.

So you mean you have something like TextInputColumns in the table, but when you change the values, the records are not updated?

I don’t think I’ve ever actually used a filter along with an editable table, so I’ve never come across this issue, and I’m not sure why it would happen.

If you can get it to work by adding just any where clause, you can always just add ->whereNotNull('id'), which should always be true.

Umr Farooq

2 years ago

Level 2

@kokoshneta here is full code after update

public static function table(Table $table): Table
    {
        return $table
            ->columns([
                Tables\Columns\TextColumn::make('name')
                    ->sortable()
                    ->searchable()
                    ->toggleable(),
                Tables\Columns\TextColumn::make('email')
                    ->sortable()
                    ->searchable()
                    ->toggleable(),
                Tables\Columns\TextColumn::make('hospital.name')
                    ->sortable()
                    ->toggleable()
                    ->searchable(),
                Tables\Columns\TextColumn::make('contact_number')
                    ->sortable()
                    ->toggleable()
                    ->toggledHiddenByDefault()
                    ->searchable(),
                Tables\Columns\TextColumn::make('alliedHealthProfessional.designation')
                    ->label('Designation')
                    ->enum(FilamentFormHelper::getDesignations())
                    ->sortable()
                    ->toggleable()
                    ->searchable(),
                Tables\Columns\TextColumn::make('surgeries_count')
                    ->label('Entries')
                    ->counts('surgeries')
                    ->sortable()
                    ->toggleable()
            ])
            ->filters([
                Filter::make('is_allied_health_professional')
                    ->label(__('Is Allied Health Professional'))
                    ->toggle()
                    ->query(fn(Builder $query): Builder => $query
                        ->whereNotNull('allied_health_professional_id')),
                SelectFilter::make('designation')
                    ->label('AHP Designation')
                    ->multiple()
                    ->options(FilamentFormHelper::getDesignations())
                    ->query(function (Builder $query, array $data): Builder {
                        return $query
                            ->when(
                                $data['values'],
                                fn(Builder $query, $date): Builder => $query->whereHas(
                                    'alliedHealthProfessional',
                                    fn(Builder $query) => $query->whereIn('designation', $data['values'])
                                ),
                            );
                    }),
                Filter::make('created_at')
                    ->form([
                        Forms\Components\DatePicker::make('created_from'),
                        Forms\Components\DatePicker::make('created_until'),
                    ])
                    ->query(function (Builder $query, array $data): Builder {
                        return $query->withCount([
                            'surgeries as surgeries_count' => function (Builder $builder) use ($data) {
                                if ($from = $data['created_from'])
                                    $builder->where('surgeries.date', '>=', $from);
                                if ($until = $data['created_until'])
                                    $builder->where('surgeries.date', '<=', $until);

                                return $builder;
                            }
                        ]);
                    })
            ])
            ->actions([
                Tables\Actions\ActionGroup::make([
                    Tables\Actions\ViewAction::make(),
                    Tables\Actions\EditAction::make(),
                    Tables\Actions\DeleteAction::make(),
                    Tables\Actions\ForceDeleteAction::make(),
                    Tables\Actions\RestoreAction::make(),
                ]),
            ])
            ->bulkActions([
                Tables\Actions\DeleteBulkAction::make(),
                Tables\Actions\ForceDeleteBulkAction::make(),
                Tables\Actions\RestoreBulkAction::make(),
                ExportBulkAction::make()->exports([
                    ExcelExport::make('table')
                        ->fromTable()
                        ->askForFilename()
                        ->askForWriterType(),
                    ExcelExport::make('form')->fromForm()
                        ->askForFilename()
                        ->askForWriterType(),
                ]),
            ]);
    }

Umr Farooq

2 years ago

Level 2

@kokoshneta

If you can get it to work by adding just any where clause, you can always just add ->whereNotNull('id'), which should always be true.

It's not solving my issue. I was saying the record only gets updated if I apply where in the query. BTW thanks

Umr Farooq

2 years ago

Level 2

@kokoshneta here is the demo. maybe that will help to understand the issue. thanks

kokoshneta

2 years ago

Level 27

Oh, now I understand what you mean. You mean that the filter isn’t being applied, and the page isn’t updating. Not that the records in the database aren’t being updated. Let me have a closer look at that.

kokoshneta

2 years ago

Level 27

@Umr Farooq What exactly is your filter supposed to do? Should it

only show users that have surgeries created between the selected dates?
or show all users, but only count surgeries created between the selected dates?

Umr Farooq

2 years ago

Level 2

@kokoshneta If you want I can also try to show the table records.

Umr Farooq

2 years ago

Level 2

@kokoshneta

or show all users, but only count surgeries created between the selected dates?

yes, I want to update the Entries count according to the selected dates.

Umr Farooq

2 years ago

Level 2

I add the ->whereNotNull('deleted_at') in the question to show that the filament only updates the record if I add where in the outer query builder. @kokoshneta

Umr Farooq

2 years ago

Best Answer

Level 2

https://discord.com/channels/883083792112300104/1133306830156734536. Solve my issue.

Please or to participate in this conversation.