Get data between two dates in laravel, if data does not exist it should return 0 as count-CodePudding

Here I have a situation to solve in laravel query, where I need to get the data between specific date from a table and return the data, But I need to return all the data including non-exist dates as well.

Here is my table structure--

id | date        | user_id
1    2022-08-02      1
2    2022-08-02      2
3    2022-08-01      2

Now if I get the data from database between 25-07-2022 to 02-08-2022, then the result set should be ---

  date      |   user_count
2022-08-02          2
2022-08-01          1
2022-07-31          0
2022-07-30          0
2022-07-29          0
2022-07-28          0
2022-07-27          0
2022-07-26          0
2022-07-25          0

this is the result how I need to get in the query. Please let me know which query I should write to fetch the data like above output. I have written the normal mysql query and laravel query, but not able to find the query solution for above output.. please suggest Thanks in advance

CodePudding user response：

Wrote something fast, should print the wanted result

  $period = CarbonPeriod::create('2018-06-14', '2018-06-20');
    foreach ($period as $date)
    {
       $arr[] = DB::table('table_name')->selectRaw('date, count(user_id)')->where('date', $date)->get()->toArray();
    }
    dd($arr);

CodePudding user response：

->whereBetween('date',[ 2022-07-25, 2022-08-02])

I think The code above will work