我有一张桌子如下 –
现在我需要报告每小时,每周,每月和每年的总计数.它可能有0,但应该包含在结果中.例如,我需要如下结果 –
$hourlyResult = array(
'00:01' => '5','00:02' => '9','00:03' => '50','00:04' => '5',..............
..............
'00:55' => '95','00:56' => '0','00:57' => '20','00:58' => '33','00:59' => '5',);
$weeklyResult = array(
'SAT' => '500','SUN' => '300'
.............
.............
'FRI' => '700'
);
如何在cakePHP 3中构建查询?我得到以下链接,但不能到目前为止.
GROUP BY WEEK with SQL
我做了什么 –
$this->loadModel('Searches');
$searches = $this->Searches
->find('all')
->select(['created','count'])
->where('DATE(Searches.created) = DATE_SUB(CURDATE(),INTERVAL 1 DAY)')
->group(WEEK(date))
->hydrate(false)
->toArray();
pr($searches);
这是你如何做到的.
按年计算
$query = $this->Searches->find();
$query = $this->Searches
->find()
->select([
'total_count' => $query->func()->sum('count'),'year' => $query->func()->year(['created' => 'literal'])
])
->group(['year'])
->hydrate(false);
要么
$query = $this->Searches
->find()
->select(['total_count' => 'SUM(count)','year' => 'YEAR(created)'])
->group(['year'])
->hydrate(false);
按周计算
$query = $this->Searches->find();
$query = $this->Searches
->find()
->select([
'total_count' => $query->func()->sum('count'),'day_of_week' => $query->func()->dayOfWeek('created')
])
->group(['day_of_week'])
->hydrate(false);
要么
$query = $this->Searches
->find()
->select(['total_count' => 'SUM(count)','day_of_week' => 'DAYOFWEEK(created)'])
->group(['day_of_week'])
->hydrate(false);
以同样的方式获得按小时或月计的总和.
在这里你可以阅读约CakePHP > Using SQL Functions和date and time functions in MySQL.