CODEWITHSUNDEEP
arrayslaravelmultidimensional-arraylaravel-controller
laurence keith albano
laurence keith albano

Reputation: 1482

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

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?

Upvotes: 0

Views: 280

Answers (1)

Morteza Pouretemadi
Morteza Pouretemadi

Reputation: 648

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']);
}

Upvotes: 1

Related Questions