你应该使用 where() 带闭包+ orWhereNull() .
另外，我认为最好使用别名以提高可读性。

$caregivers = \DB::table('caregivers')
    ->join('caregiver_professional_details as details', function($query){
        $query
            ->on('caregivers.id', '=', 'details.caregiver_id')
            ->where('details.role', '=', 'Caregiver')
            ->whereDate('details.date_of_joining', '<=', $date)
            ->where(function($query){
                $query
                    ->whereDate('details.resignation_date', '>=', $date)
                    ->orWhereNull('details.resignation_date');
            });
    })
    ->get();

您的查询应如下所示：

\DB::table('caregivers')
  ->join('caregiver_professional_details',function($q){
        $q->on('caregivers.id', '=','caregiver_professional_details.caregiver_id')
  ->where('caregiver_professional_details.role','=','Caregiver')
  ->where(function($r) use ($date){
        $r->whereDate('caregiver_professional_details.date_of_joining','<=',$date)
          ->orWhereNull('caregiver_professional_details.date_of_joining');
   })
   ->where(function($r) use ($date){
        $r->whereDate('caregiver_professional_details.resignation_date','>=',$date)
          ->orWhereNull('caregiver_professional_details.resignation_date');
   });
})

我认为您的逻辑是，您希望匹配输入日期早于加入日期、辞职日期晚于输入日期或辞职日期为的员工 NULL 在这种情况下，我们可以假定他还没有辞职。也就是说，我建议你 WHERE 条款：

date_of_joining <= DATE AND
(resignation_date >= DATE OR resignation_date IS NULL)

要在您的laravel代码中形成此逻辑，我们可以使用匿名函数：

DB::table('caregivers')
    ->join('caregiver_professional_details', function($q) {
        $q->on('caregivers.id', '=',
            'caregiver_professional_details.caregiver_id')
    ->where('caregiver_professional_details.role','=','Caregiver')
     ->whereDate('caregiver_professional_details.date_of_joining', '<=', $date)
     ->where(function($query) {
            $query->whereDate('caregiver_professional_details.resignation_date', '>=', $date)
                  ->orWhereNull('caregiver_professional_details.resignation_date');
        })
});