时间戳

首页 > 解决方案 > 如何从laravel中的数组中填写缺失的日期？

arrays - 如何从laravel中的数组中填写缺失的日期？

问题描述

我这里有这个功能，可以created_at在我的表中提取数据和分组。

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

当我尝试使用dd($query);它时，它会提取某些数据，我想要的是填写缺失的日期并将值设置0为那些缺失的日期。我怎样才能做到这一点？

结果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"
  ]
]

正如您在此处看到的那样array(15)，May 2019跳转到July 2019. 如何在我的数组中添加那些缺失的日期并将值设置status_pending为零status_success？

标签： arrayslaravelmultidimensional-arraylaravel-controller

解决方案

恐怕没有 SQL 方法可以做到这一点，你必须用 foreach 来处理它。我有这个想法，希望对您有所帮助：

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

推荐阅读