Filament•7mo ago

Can Custom Filter Form performs filter through joins?

I build a filament table with an aim to LEFT JOIN 2 tables: "Employees" and "Attendances". The SQL that I am trying to build is as follow:

select * from `employees` left join `attendances` on (`employees`.`id` = `attendances`.`employee_id` AND attendances.dt_attendance = '<filter dt value>') where `employees`.`deleted_at` is null order by `employees`.`id` asc;

select * from `employees` left join `attendances` on (`employees`.`id` = `attendances`.`employee_id` AND attendances.dt_attendance = '<filter dt value>') where `employees`.`deleted_at` is null order by `employees`.`id` asc;

This is the table code snippet that I have with the custom filter form:

$table
            ->query(Employee::query())
            ->columns([
                TextColumn::make('first_name'),
                TextColumn::make('result'),
            ])
            ->filters([
                Filter::make('dt_attendance')
                    ->form([
                        Forms\Components\DatePicker::make('dt')->default(date("Y-m-d"))->label('Tanggal')
                    ])
                    ->query(function (Builder $query, array $data): Builder {
                        return $query
                            ->when(
                                $data['dt'],
                                fn (Builder $query, $date): Builder => $query->leftJoin('attendances', function($join) use ($date)
                                    {
                                        $join->on('attendances.employee_id', '=', 'employees.id');
                                        $join->on('attendances.dt_attendance','>=',DB::raw("'".$date."'"));
                                    })
                            );
                    })
            ], layout: FiltersLayout::AboveContent)

$table
            ->query(Employee::query())
            ->columns([
                TextColumn::make('first_name'),
                TextColumn::make('result'),
            ])
            ->filters([
                Filter::make('dt_attendance')
                    ->form([
                        Forms\Components\DatePicker::make('dt')->default(date("Y-m-d"))->label('Tanggal')
                    ])
                    ->query(function (Builder $query, array $data): Builder {
                        return $query
                            ->when(
                                $data['dt'],
                                fn (Builder $query, $date): Builder => $query->leftJoin('attendances', function($join) use ($date)
                                    {
                                        $join->on('attendances.employee_id', '=', 'employees.id');
                                        $join->on('attendances.dt_attendance','>=',DB::raw("'".$date."'"));
                                    })
                            );
                    })
            ], layout: FiltersLayout::AboveContent)

When I checked on laravel debugbar, somehow the query log is not showing the joins when I put the joins in the "filter query". However, when I moved the joins, in the "table query", it is executing it correctly. Thus, is it possible to create joins in "filter query"?

1 Reply

Euu83•6mo ago

Hi, were you able to fix it?

Gaming

Programming

Can Custom Filter Form performs filter through joins?