I’m using CakePHP 3.4
I have to get visitors count group by YEAR from LogProfileVisits table.
What I’m doing is
$profile_visit_logs = $this->Users->LogProfileVisits->find()
->where(['user_id' => $user->id]);
$profile_visit_logs->select([
'total_count' => $profile_visit_logs->func()->count('id'),
'unique_count' => $profile_visit_logs->func()->count('DISTINCT `ip_address`'),
'visit_date' => 'YEAR(created)'
])
->group('visit_date')
->limit(10);
But this is giving array of data according to date
object(App\Model\Entity\LogProfileVisit) {
'total_count' => (int) 5,
'unique_count' => (int) 2,
'visit_date' => object(Cake\I18n\FrozenDate) {
'time' => '2017-06-02T00:00:00+00:00',
'timezone' => 'UTC',
'fixedNowTime' => false
}
}
object(App\Model\Entity\LogProfileVisit) {
'total_count' => (int) 7,
'unique_count' => (int) 3,
'visit_date' => object(Cake\I18n\FrozenDate) {
'time' => '2017-06-02T00:00:00+00:00',
'timezone' => 'UTC',
'fixedNowTime' => false
}
}
Here, both visit_date
is same ie., 2017-06-02
but count are different.
The counts should be total visits in a year
and total unique ip addresses in a year
.