我有一个employees表和一个连接到另一个表,其中有加入日期和离职日期,默认值为空,所以当我选择一个日期时,比方说今天,我想检索所有选择的日期介于其加入日期和离职日期之间的员工,但很多员工的离职默认值将为空。因此查询不起作用。下面是我当前的代码
\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
->whereDate('caregiver_professional_details.resignation_date','>=',$date);
})
因此,当我选择小于或等于2018年12月20日的任何日期时,将同时显示1和2。如果我选择21或更高,则只显示2
您的查询应该如下所示:
\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');
});
})