How to get sum of total counts with most recent hourly / daily / weekly / yearly interval in cakephp 3?

Question

I've a table as following-

Now I need to make report of total number of counts in every hour, week, month and year. It may have sum 0 but should be include on the result. For example I need the result as follows-

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

How can I build the query in cakephp 3? I got the following link but can't go so far.

GROUP BY WEEK with SQL

What I've done-

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

I want to know the cakephp way. Though, I don't know the raw query also. — biplob, Aug 09 '16 at 16:19
Okay. Then you have to wait until a `cake php` guy drops by. — 1000111, Aug 09 '16 at 16:21
If you don't mind can you please tell me the raw query way as cakephp supports raw query also. If I not found any cakephp guy then I'll implement on raw way. — biplob, Aug 09 '16 at 16:23
Then I need more info: what does your expected output look like? By `weekly` did you mean week/ last 7 days? In fact your expected output will answer so many questions. — 1000111, Aug 09 '16 at 16:28
Got answer of one of the questions. Now, it's turn for **expected output** — 1000111, Aug 09 '16 at 16:30

score 1 · Accepted Answer · answered Aug 18 '16 at 05:17

Here is how you can do it.

Sum By Year

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

Or

    $query = $this->Searches
        ->find()
        ->select(['total_count' => 'SUM(count)', 'year' => 'YEAR(created)'])
        ->group(['year'])
        ->hydrate(false);

Sum By Day Of Week

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

Or

    $query = $this->Searches
        ->find()
        ->select(['total_count' => 'SUM(count)', 'day_of_week' => 'DAYOFWEEK(created)'])
        ->group(['day_of_week'])
        ->hydrate(false);

The same way you can get total sum by hour or month. Here you can read about CakePHP > Using SQL Functions and date and time functions in MySQL.

How to get sum of total counts with most recent hourly / daily / weekly / yearly interval in cakephp 3?

1 Answers1