laravel查询生成器不允许执行像mysql这样的BETWEEN查询

I want to check time overlapping using current time with db data. this I done for check shift time overlapping , here example of my database table

1 )date:1 shift:1 start_time 04:00 end_time 05:00
2)date:1  shift: 2  start_time 09:00 end_time 11:00

particular day has several shifts and when someone edit or add shift a shift for a day I want to check whther its overlapping

here is my code

$shiftData = Shifts::where('time_sheet_id', '=', $timesheet_id->id)
                        ->where('user_id', '=', $driver_id->id)
                        ->where('role_id', '=', 1)
                        ->whereBetween('shift_start_time', array($shift_start , $shift_end))
                        ->whereBetween('shift_end_time' , array($shift_start , $shift_end))
                        ->count();

$shift_start and $shift_end are user input values. with this code I'm check if count is 0 time is not overlapping.

so I tried to add time $shift_start :4:20 and $shift_end 4:40, but with this query its return as not overlapping. but it should overlap table currently have time shift to 4 to 5 (start_time 04:00 end_time 05:00)

please advice me how to proceed this query with above scenario

As you've found out, the whereBetween() method doesn't support checking if a value is between fields in the database, it only checks if a field in the database is between two values.

Because of this, the simplest option would be just to rewrite your where clauses to manually use the >= and <= operators.

Try something like:

$shiftData = Shifts::where('time_sheet_id', '=', $timesheet_id->id)
    ->where('user_id', '=', $driver_id->id)
    ->where('role_id', '=', 1)
    ->where(function($q1) use ($shift_start, $shift_end) {
        $q1
            ->where(function($q2) use ($shift_start) {
                $q2
                    ->where('shift_start_time', '<=', $shift_start)
                    ->where('shift_end_time', '>=', $shift_start);
            })
            ->orWhere(function($q2) use ($shift_end) {
                $q2
                    ->where('shift_start_time', '<=', $shift_end)
                    ->where('shift_end_time', '>=', $shift_end);
            });
    })
    ->count();