我正在尝试在cakeph3中按年份分组。我可以使用以下方式,但它仍然没有按年份分组(我想要的方式。)
$query = $this->Alerts->query();
$year = $query->func()->year([
'added_on' => 'literal'
]);
$month = $query->func()->monthname(['added_on' => 'literal']);
$monthAlertsCount = $query->func()->count($month);
$data = $query
->select([
'year' => $year,
'month' => $month,
'count' => $monthAlertsCount
])
->group($year)
->group($month);
$status = "success";
$this->set('response', $status);
$this->set('year', $data);
$this->set('_serialize', ['response','year']);
电流输出如下:
{
"response":"success",
"year":[
{
"year":"2013",
"month":"November",
"count":"1"
},
{
"year":"2014",
"month":"February",
"count":"2"
},
{
"year":"2014",
"month":"January",
"count":"3"
},
{
"year":"2015",
"month":"December",
"count":"6"
},
{
"year":"2015",
"month":"February",
"count":"3"
},
{
"year":"2015",
"month":"January",
"count":"4"
},
{
"year":"2016",
"month":"January",
"count":"83"
}
]
}
预期输出:
{
"response":"success",
"year":[
{
"2013":[
{
"month":"November",
"count":"1"
}
],
"2014":[
{
"month":"February",
"count":"2"
},
{
"month":"January",
"count":"3"
}
],
"2015":[
{
"month":"December",
"count":"6"
},
{
"month":"February",
"count":"3"
},
{
"month":"January",
"count":"4"
}
],
"2016":[
{
"month":"January",
"count":"83"
}
]
}
]
}
有人能帮我在cakepp3中得到预期的输出吗?
使用集合方法groupBy()
-记住,查询是集合对象(有点)!
$data = $query
->select([
'year' => $year,
'month' => $month,
'count' => $monthAlertsCount
])
->group($year)
->group($month)
->groupBy('year');
这将创建所需的分组结构,但不会从单个行中删除year
字段,如果您希望删除它们,请使用映射器,如
// ...
->groupBy('year')
->map(function ($rows) {
foreach ($rows as &$row) {
unset($row['year']);
}
return $rows;
});
另请参见
- 食谱>收藏>收藏::groupBy()
- 食谱>收藏>收藏::map()
- 食谱>数据库访问&ORM>查询生成器>查询是集合对象