CakePHP 4 Find & Count

CakePHP 4 Find, Sort & Count

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 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 is 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 (eg: June == 6):

$this->set('total_current_month', $this->Reports->find()->where(['MONTH(Reports.created)' => date('n')])->count());


or to count other month records eg: June:

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


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