0

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

Developer Netset
  • 3
  • 1
  • How is your current query looking alike? – Lajos Arpad Aug 02 '22 at 11:28
  • You would need a table of dates to LEFT JOIN and group on, even if it is a temporary table. Otherwise a `DatePeriod` of dates within the desired period can be used to iterate over and fill in the missing dates from the query results as `0`. – Will B. Aug 02 '22 at 12:22

2 Answers2

2

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);
Kevin
  • 1,152
  • 5
  • 13
  • Thanks for responding. can we not write any single query for it? – Developer Netset Aug 02 '22 at 12:11
  • You can, have to probably check this: https://stackoverflow.com/questions/12890967/show-all-dates-data-between-two-dates-if-no-row-exists-for-particular-date-then – Kevin Aug 02 '22 at 12:18
  • If there is more dates GAP like 6 months then how we can manage that one? – Developer Netset Aug 02 '22 at 12:21
  • 1
    You just need to define the dates in that case, either with sql or Carbon @DeveloperNetset – Kevin Aug 02 '22 at 12:32
  • Personally, I would inverse the operation to fill in the missing dates from the array of database results. 1) Create date period for desired date-range. 2) Query min/max of the date period from database table. 3) Iterate over each date within the date period with a conditional to check db query result dataset for the date and add a record to the array for the missing date and 0 if is not found within the db dataset. – Will B. Aug 02 '22 at 14:25
  • @WillB. Yes this could be a better solution as well, less queries is better – Kevin Aug 02 '22 at 14:27
0
->whereBetween('date',[ 2022-07-25, 2022-08-02])

I think The code above will work

Barjo_bswa
  • 36
  • 2