How to fill in missing dates from an array in laravel?

Question

I have this function here that pulls data and group by created_at in my table.

private function getSummaryData($seller)
{
    $query = json_decode(json_encode(DB::select(DB::raw("select store_id, 
        DATE_FORMAT(created_at, '%b %Y') ym,
        sum(IF(status='pending', 1, 0)) status_pending,
        sum(IF(status='success', 1, 0)) status_success
        FROM `case_summaries`
        WHERE store_id= " . $seller->store->store_id . "
        GROUP BY DATE_FORMAT(created_at, '%b %Y')
        ORDER BY created_at asc "))), true);

    dd($query);
}

When I try to use the dd($query); it pulls certain data and what I want is to fill in the missing dates and sets the values to 0 to those missing dates. How can I achieve this?

Results of dd($query);:

array:24 [
  0 => array:4 [
    "store_id" => 1
    "ym" => "Feb 2018"
    "status_pending" => "0"
    "status_success" => "32"
  ]
  1 => array:4 [
    "store_id" => 1
    "ym" => "Mar 2018"
    "status_pending" => "0"
    "status_success" => "1"
  ]
  2 => array:4 [
    "store_id" => 1
    "ym" => "Apr 2018"
    "status_pending" => "0"
    "status_success" => "5"
  ]
  3 => array:4 [
    "store_id" => 1
    "ym" => "May 2018"
    "status_pending" => "0"
    "status_success" => "6"
  ]
  4 => array:4 [
    "store_id" => 1
    "ym" => "Jun 2018"
    "status_pending" => "0"
    "status_success" => "7"
  ]
  5 => array:4 [
    "store_id" => 1
    "ym" => "Jul 2018"
    "status_pending" => "0"
    "status_success" => "4"
  ]
  6 => array:4 [
    "store_id" => 1
    "ym" => "Aug 2018"
    "status_pending" => "0"
    "status_success" => "6"
  ]
  7 => array:4 [
    "store_id" => 1
    "ym" => "Sep 2018"
    "status_pending" => "0"
    "status_success" => "5"
  ]
  8 => array:4 [
    "store_id" => 1
    "ym" => "Oct 2018"
    "status_pending" => "0"
    "status_success" => "2"
  ]
  9 => array:4 [
    "store_id" => 1
    "ym" => "Nov 2018"
    "status_pending" => "0"
    "status_success" => "12"
  ]
  10 => array:4 [
    "store_id" => 1
    "ym" => "Dec 2018"
    "status_pending" => "0"
    "status_success" => "7"
  ]
  11 => array:4 [
    "store_id" => 1
    "ym" => "Jan 2019"
    "status_pending" => "0"
    "status_success" => "4"
  ]
  12 => array:4 [
    "store_id" => 1
    "ym" => "Feb 2019"
    "status_pending" => "0"
    "status_success" => "6"
  ]
  13 => array:4 [
    "store_id" => 1
    "ym" => "Mar 2019"
    "status_pending" => "0"
    "status_success" => "2"
  ]
  14 => array:4 [
    "store_id" => 1
    "ym" => "Apr 2019"
    "status_pending" => "0"
    "status_success" => "1"
  ]
  15 => array:4 [
    "store_id" => 1
    "ym" => "May 2019"
    "status_pending" => "0"
    "status_success" => "0"
  ]
  16 => array:4 [
    "store_id" => 1
    "ym" => "Jul 2019"
    "status_pending" => "0"
    "status_success" => "1"
  ]
  17 => array:4 [
    "store_id" => 1
    "ym" => "Aug 2019"
    "status_pending" => "1"
    "status_success" => "3"
  ]
  18 => array:4 [
    "store_id" => 1
    "ym" => "Sep 2019"
    "status_pending" => "0"
    "status_success" => "6"
  ]
  19 => array:4 [
    "store_id" => 1
    "ym" => "Oct 2019"
    "status_pending" => "0"
    "status_success" => "3"
  ]
  20 => array:4 [
    "store_id" => 1
    "ym" => "Nov 2019"
    "status_pending" => "0"
    "status_success" => "7"
  ]
  21 => array:4 [
    "store_id" => 1
    "ym" => "Dec 2019"
    "status_pending" => "0"
    "status_success" => "0"
  ]
]

As you can see here that array(15) which is May 2019 jumps to July 2019. How can I add those missing dates in my array and sets the value for status_pending and status_success to zero?

Morteza Pouretemadi · Accepted Answer · 2021-03-02 14:36:26Z

1

I'm afraid there is no SQL way to do this and you must handle it with foreach. I have this in my mind and I hope it'd help you:

$longMonthSeconds = 31 * 24 * 3600;
$time = strtotime($query[0]['ym']);

$data = [];
foreach ($query as $item) {
    if (strtotime($item['ym']) > $time + $longMonthSeconds) {
        $excludedMonth = date('M Y', $time + $longMonthSeconds);
        $data[] = [
            "store_id" => null,
            "ym" => $excludedMonth,
            "status_pending" => "0",
            "status_success" => "0",
        ];
    }
    $data[] = $item;
    $time = strtotime($item['ym']);
}

edited Mar 2, 2021 at 14:36

answered Mar 2, 2021 at 10:40

Morteza Pouretemadi

6484 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

laurence keith albano Over a year ago

Thanks, I tried using the dd($data); after the foreach() function but when I check it the missing months are inserted but the following dates after those missing months (which contains data) are returned to null for the store_id and other data.

Morteza Pouretemadi Over a year ago

I edited the code and the problem is fixed

Collectives™ on Stack Overflow

How to fill in missing dates from an array in laravel?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related