</> Code The Pixel

CakePHP 4 Find, Sort & Count

Asyraf Wahi Anuar - June 02, 2020

Estimated reading time: 3 minutes, 54 seconds

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 :)


Cite this article (APA 6th Edition)

Popular
CakePHP 4 Print PDF Using CakePDF
May, 17 2020
CakePHP 4 Authentication Using Auth...
May, 14 2020
CakePHP 4 Sending Email
February, 01 2022
CakePHP 4 jQuery Date Time Picker
October, 01 2018
CakePHP 4 Export To CSV
May, 29 2020
CakePHP 4 Authentication Using...
May, 11 2020
CakePHP 4 Find, Sort & Count
June, 02 2020
Share
Sharing Link
Click the icon to share