Get data between date from 2 field in Laravel-CodePudding

I have data like this:

id  | employee_id | absent_type   | do_date_start       | do_date_end 
1   | EMPLOYEE-1  | Absent        | 2022-02-14 00:00:00 | 2022-02-18 00:00:00

What I want to do is to get data between field do_date_start and do_date_end.

Case example using data above:

Start Date | End date   | Expected Result
2022-02-14 | 2022-02-18 | Data Found 
2022-02-13 | 2022-02-19 | Data Found 
2022-02-15 | 2022-02-17 | Data Found 
2022-02-13 | 2022-02-17 | Data Found 
2022-02-19 | 2022-02-20 | Data Not Found 
2022-02-12 | 2022-02-13 | Data Not Found

So as long as the date between do_date_start and do_date_end is between filter start date and end date it still getting the data, if not it won't getting the data.

I've tried like this:

DB::table('attendance_absent as absent')
    ->where(function($query) use ($start_date, $end_date){
      $query->where('absent.do_date_start', '<=', $start_date)
            ->where('absent.do_date_end', '>=', $end_date)
            ->orWhere(function ($q) use($start_date, $end_date) {
                 $q->where('absent.do_date_start', '>=', $start_date)
                   ->where('absent.do_date_end', '<=', $end_date)
                   ->orWhere(function ($k) use ($start_date, $end_date) {
                     $k->where('absent.do_date_start', '>=', $start_date)
                       ->where('absent.do_date_end', '>=', $end_date)
                       ->orWhere(function ($l) use ($start_date, $end_date) {
                         $l->where('absent.do_date_start', '<=', $start_date)
                           ->where('absent.do_date_end', '<=', $end_date);
                      });
                  });
              });
           })
     ->get();

When I tested my code using case example above, the result is like this:

Start Date | End date   | Expected Result  | Correct?
2022-02-14 | 2022-02-18 | Data Found       | Yes
2022-02-13 | 2022-02-19 | Data Found       | Yes
2022-02-15 | 2022-02-17 | Data Found       | Yes
2022-02-13 | 2022-02-17 | Data Found       | Yes
2022-02-19 | 2022-02-20 | Data Not Found   | No
2022-02-12 | 2022-02-13 | Data Not Found   | No

Somehow I still getting the data even though the data is outside date range, I don't know what's wrong with my code. Any help would be very helpful for me, thanks

CodePudding user response：

I came up with a code in Eloquent that I think works with your situation. Not sure how you can do this with DB facade. But here's the code:

  $period = ['2022-02-15','2022-02-16'];
  return Test::
  where(function($q) use ($period)  {
      return  $q->where('do_date_start', '>=', $period[0])->where('do_date_start','<=',$period[1]);
  })
  ->orWhere(function($q) use ($period)  {
      return  $q->where('do_date_end', '>=', $period[0])->where('do_date_end','<=',$period[1]);
  })
  ->orWhere(function($q) use ($period)  {
      return  $q->where('do_date_start', '<=', $period[0])->where('do_date_end','>=',$period[1]);
  })
  ->get();

If it worked for you please inform me so that I can provide more info on how I came up with this.