Be careful while using Containable behavior with deep associations in CAKEPHP 2

Containable behavior is used to get selected associated records in CAKEPHP 2. It helps to speed up process of retrieving records by fetching associated data at the same time. But for deep associations from level 2 (equal to recursive =2) it can result in large number of queries to database. Its effect can be seen in large applications so if you are using containable and getting performance issues then read this article.

Let’s take an example:

Suppose there are three associated models:

Now query tasks and associated task_users data with user info (full_name) using containable:

$this->Task->Behaviors->load('Containable');
$this->Task->recursive = -1;
$tasks=$this->Task->find('all',['contain'=>['TaskUser.User']]);

For getting user info above code generate queries to user table equals to task_users entries for each task.

If there are 100 users assigned to a task then 100 queries executed for users table.


SELECT * WHERE `User`.`id` = 1,
SELECT * WHERE `User`.`id` = 2,
......
SELECT * WHERE `User`.`id` = 100,


So for large number of records it could result in large number of connections to database server which can impact performance.

You can get last executed queries using following function. Create this function in core.php or where you place common functions.

function getLastQueries($model) {
$dbo = $model->getDatasource();
$logs = $dbo->getLog();
return $logs;
}

Just call it after find query:

debug(getLastQueries($this->Task));

You can now see the queries executed by find query.

Now question comes how to solve this? Solution depends on required data. In my case I want to get just user full_name with assignment data i.e TaskUser through tasks.

First I get only TaskUser and then all users list with key as user id and value as full_name.

$this->Task->Behaviors->load('Containable');
$this->Task->recursive = -1;
$tasks=$this->Task->find('all',['contain'=>['TaskUser']]);
$users_list = $this->Task->TaskUser->User->find('list',['fields'=>['full_name']]);

While displaying data you can display user full_name using user_id from TaskUser as key from $users_list. Example below:

//...tasks loop starts
//...task_users loop starts
$full_name = $users_list[$task_user['TaskUser']['user_id']];
echo $full_name;
//...task_users loop ends
//...tasks loop ends

In the above code two queries runs which minimize number of connections to database server and improves performance.

You can also avoid using containable in some cases by setting recursive and unbind to not required models.

Share on Facebook

Tweet

Follow us

Save