How to fetch users who does not have reports for 6 consecutive months using laravel?

Question

I'm developing a report system, and I want to fetch users who did not report for six (6) consecutive months. How do I achieve this?

I've tried the code below but I'm not getting the desired output. There is also a problem. Let's say the date interval is 12 months. How can I determine if there is no report for 6 consecutive months?

$dateStart = '2018-10-31';
$dateEnd = '2019-03-31';

$intervals = Carbon::parse($dateStart)->diffInMonths($dateEnd);

$users = $users->whereDoesntHave('reports', function($query) use($intervals) {

    for ($i = 5; $i >= 0; $i--) { 

        $firstMonth = Carbon::parse($dateEnd)->subMonthsNoOverflow($intervals);

        $query->where('date', '>=', $firstMonth->format('Y-m-d'))->where('date', '<=', $dateEnd);
    }

});

Answer 1

What I will do is that I will create a loop per month based on the start and end date, then check if he did not have a report for that month. If it doesn't have a report for that month, I will increment a counter, and if that counter reaches 6 counts, exit the loop and the condition was satisfied.

Below is the basic idea:

$dateStart = '2018-10-31';
$dateEnd = '2019-10-31';

$count = 0;
$no_report_for_6_consecutive_months = 0 ;

startloop
   $have_report = Model::whereMonth('date_column', $date_of_loop->format('m'))->get();

   if($have_report->count()){
      $count = 0;
   }
   else{
      $count++;
   }

   if($count==6){
     $no_report_for_6_consecutive_months = 1 ;
     break;
   }
endloop