Filament•2mo ago

Making a (readonly) Repeater with a custom query

So I am making a system for making schedules(=Schedules) for employees and manage their vacations (=Absences). Users are added to the schedules with a pivot table (schedule_user). Now I am trying to make something like the pictures and I am almost there! What I want is that if you select the user/start_Date/end_date that it automaticly looks up all the conflicting schedules and counts the hours. The overview I want to display in a simple TableRepeater. This is the code I now have:

TableRepeater::make('scheduleUsers')
    ->visible(fn (Get $get) => $get('start_date') && $get('end_date') && $get('subject_id'))
    ->addable(false)
    ->deletable(false)
    ->relationship('scheduleUsers',
       modifyQueryUsing: function (Builder $query, Get $get) {
         if($get('start_date') && $get('end_date') && $get('subject_id')) {
            $query
                ->where('schedule_user.user_id', $get('subject_id'))
                ->whereHas('schedule', function (Builder $query) use ($get) {
                   $query->where('start_date', '>=', $get('start_date'));
                   $query->where('end_date', '<=', $get('end_date'));
                        });
                    }
                })

TableRepeater::make('scheduleUsers')
    ->visible(fn (Get $get) => $get('start_date') && $get('end_date') && $get('subject_id'))
    ->addable(false)
    ->deletable(false)
    ->relationship('scheduleUsers',
       modifyQueryUsing: function (Builder $query, Get $get) {
         if($get('start_date') && $get('end_date') && $get('subject_id')) {
            $query
                ->where('schedule_user.user_id', $get('subject_id'))
                ->whereHas('schedule', function (Builder $query) use ($get) {
                   $query->where('start_date', '>=', $get('start_date'));
                   $query->where('end_date', '<=', $get('end_date'));
                        });
                    }
                })

This results in the following query that I intercepted with debugbar:

select * from `schedule_user` where `schedule_user`.`user_id` is null and `schedule_user`.`user_id` is not null and `schedule_user`.`user_id` = '1' and exists (select * from `schedules` where `schedule_user`.`schedule_id` = `schedules`.`id` and `start_date` >= '2024-05-06' and `end_date` <= '2024-05-18')

select * from `schedule_user` where `schedule_user`.`user_id` is null and `schedule_user`.`user_id` is not null and `schedule_user`.`user_id` = '1' and exists (select * from `schedules` where `schedule_user`.`schedule_id` = `schedules`.`id` and `start_date` >= '2024-05-06' and `end_date` <= '2024-05-18')

This query returns null because of this part: "schedule_user.user_id is null and schedule_user.user_id is not null and". If you would delete that part the query would work perfectly. My question is, why is this added to the query?

1 Reply

Sjoerd24•2mo ago

I guess it has sth to do with the relationship that is set up as follows:

public function scheduleUsers(): HasMany
    {
        return $this->hasMany(ScheduleUser::class, 'user_id', 'subject_id');
    }

public function scheduleUsers(): HasMany
    {
        return $this->hasMany(ScheduleUser::class, 'user_id', 'subject_id');
    }

But how can I "unset" the previous scope? Or is it possible to initate the Repeater form on a custom query? I read the docs 3 times over now, I just can't figure it out. Thanks a lot for reading this far.

Gaming

Programming

Making a (readonly) Repeater with a custom query