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

schedule_user

user_id

user_id

is null and

schedule_user

schedule_user

user_id

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?

Making a (readonly) Repeater with a custom query

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

schedule_user

user_id

user_id

is null and

schedule_user

schedule_user

user_id

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?

Making a (readonly) Repeater with a custom query

Making a (readonly) Repeater with a custom query

Similar Threads

Similar Threads

Similar Threads