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:

Table name                 Model  name

 

FieldsAssociation
usersUser
idusernamepasswordfull_nameaddress
tasksTask
idnamedescriptiontask_typedue_date
HasMany TaskUser
task_usersTaskUser

 

idUser_idtask_idstatuscompleted
BelongsTo User

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

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.

Just call it after find query:

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.

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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *