CakePHP 4 Find, Sort & Count
Asyraf Wahi Anuar
This tutorial will show how to count registered records in your database using CakePHP 4 method. Basically, the process is to find the records based on the condition and count.
This tutorial may be outdated
It was last reviewed 4 years ago. Some steps may differ in newer versions.
Tech stack
- CakePHP 4.x
Need to count total records? Count total record based on a condition? This tutorial will show how to count registered records in your database using CakePHP 4 method. Basically, the process is to find the records based on the condition and count.
Find All and Order (Sort by) - Paginate
Find all (using paginator) and sort based on created date:
$this->set('reports', $this->paginate($this->Reports->find('all')
->where(['Reports.category' => 'Bug'])
));Find all (using paginator) with a condition (where category == Bug) and sort based on created DESC:
$this->set('reports', $this->paginate($this->Reports->find('all')
->where(['Reports.category' => 'Bug'])
->order(['Reports.created' => 'DESC'])
));Find all (using paginator) and sort based on the category priority. Let say we have the categories (Account, Bug, Others). The sort will be prioritized based on the order.
$this->set('contacts', $this->paginate($this->Contacts->find('all')
->order(['FIELD(Contacts.category, "Bug Report", "Others", "Account Registration")'])
));Find All and Order (Sort by)
Find all reports record with basic order based on the name:
$reports = $this->Reports->find('all', ['order' => 'name']);Find all report records and sort by created date descendingly (DESC) or ascending (ASC):
$reports = $this->Reports->find('all', ['order' => ['created' => 'DESC']]);Find all report records and sort them based on the category priority. Let say we have the categories (Account, Bug, Others). The sort will be prioritized based on the order.
$reports = $this->Reports->find('all', ['order' => 'FIELD(Reports.category, "Account", "Bug", "Others")']);if need to sort descendingly:
$reports = $this->Reports->find('all', ['order' => 'FIELD(Reports.category, "Account", "Bug", "Others")DESC']);Count Total Record
To count total record that has been registered into table reports:
$this->set('total_report', $this->Reports->find()->count());To print the result:
<?= $total_report; ?>Conditional Count
Count total report which belong to user_id == 14
$this->set('total_report2', $this->Reports->find()->where(['user_id' => '14'])->count());Count total report which belong to user_id == 14 and status == 1
$this->set('total_report2', $this->Reports->find()->where(['user_id' => '13', 'status' => '1'])->count());Count Related Record
Table reports are related to table users (user_id). The count is executed in table users to count the total reports made by the user. First, load the Reports model in users. The count the total record which is based on the user_id == $id.
$this->loadModel('Reports');
$this->set('total_report', $this->Reports->find()->where(['user_id' => $id])->count());If you have more conditions:
$this->loadModel('Reports');
$this->set('total_document_active', $this->Reports->find()
->where([
'Reports.user_id' => $id,
'Reports.status' => 1,
])->count());Count Generated Records Based on Current Month
To count the total created reports in the current month. 'n' will output the current month using an integer.
$this->set('total_current_month', $this->Reports->find()->where(['MONTH(Reports.created)' => date('n')])->count());or to count other month records (eg: June == 6):
$this->set('total_june_month', $this->Reports->find()->where(['MONTH(Reports.created)' => date('6')])->count());For date-time format, refer here: PHP.net
Count Specific Month Record/ Based on Date/Duration
Let say you want to count total reports for May 2020:
$this->set('may_2020', $this->Reports->find()
->where([
'Reports.created >=' => '2020-05-01',
'Reports.created <=' => '2020-05-31',
])->count());Count Record Based on Search Input
If you need to count record based on the search result, request the search query as shown below (eg: status):
$this->set('current_active', $this->Users->find()->where(['status' => $this->request->getQuery('status')])->count());Or:
$status = $this->request->getQuery('status');
$this->set('current_active', $this->Users->find()->where(['status' => $status])->count());The above solution will produce an error if the $status == NULL, you can manage the condition as shown below:
if ($status != NULL) {
$this->set('current_active', $this->Users->find()->where(['status' => $this->request->getQuery('status')])->count());
} else {
$this->set('current_active', $this->Users->find()->count());
}That all. Happy coding :)
Did this tutorial help?
243 readers found this helpful
Comments
No comments yet. Questions about this tutorial are welcome here: the answer helps the next reader too.