F
Filament•10mo ago
Sjoerd24

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?
Current setup
this im trying to recreate
1 Reply
Sjoerd24
Sjoerd24OP•10mo 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.

Did you find this page helpful?