CakePHP Containable: Loading too much relations?

Question

I'm a huge fan of cakephp's containable element, because I always thought, that it would handle loading of additional models appropriate. But in the last days I dug deeper and found out, that there's really an memory issue.

Think of the following model structure:

• Project has many Wall
• Project has many Participant
• Wall has many Post
• Post has many Comment
• Participant has many Post
• Participant has many Comment
• Participant belongs to User
• Participant belongs to Project

and vice versa

• Post belongs to Participant
• Post belongs to Wall
• Comment belongs to Participant
• Comment belongs to Post
• User has many Participant

In the wall-Controller I have following find-Statement:

$this->set(
  "posts", 
  $this->Post->find(
    "all", array(
      "conditions" => array("Post.wall_id" => $wall["Wall"]["id"]), 
      "contain" => array("Participant")
    )
  )
);

I would expect, that cakephp would find all posts and include only the corresponding participant-objects. But, what I get is a list of all Posts (correct) & their Participants (correct) but also of the corresponding Wall (incorrect) & the corresponding Comments, if available (incorrect). So from performance point of view: way too much objects, which can lead to a "FATAL ERROR - memory overload".

And for the, theoratically really sexy & interesting part:

$this->set(
  "posts", 
  $this->Post->find(
    "all", array(
      "conditions" => array("Post.wall_id" => $wall["Wall"]["id"]), 
      "contain" => array("Participant.User")
    )
  )
);

Because I'm only interested in the Post & Participant.User objects, I change the contain-array to Participant.User. But, again, now I'm getting not only the User object, but also all other related objects to the Participant (project, posts, comments, ...) and the object-tree is much bigger than before.

So I was wondering, what is the correct way to implement this? Do I need to explicitly set the "join" option or do I have to set the fields option (in the root or in the contain-option)?

Greets from Austria.

Answer 1

Stop using Containable. It really generates too many queries. Use joins syntax. Take a look at this question and at the cookbook article.

UPDATE

Joining tables

In SQL you can combine related tables using the JOIN statement. This allows you to perform complex searches across multiples tables (i.e: search posts given several tags).

In CakePHP some associations (belongsTo and hasOne) perform automatic joins to retrieve data, so you can issue queries to retrieve models based on data in the related one.

But this is not the case with hasMany and hasAndBelongsToMany associations. Here is where forcing joins comes to the rescue. You only have to define the necessary joins to combine tables and get the desired results for your query.

Remember you need to set the recursion to -1 for this to work. I.e: $this->Channel->recursive = -1;

To force a join between tables you need to use the “modern” syntax for Model::find(), adding a ‘joins’ key to the $options array. For example:

$options['joins'] = array(
    array('table' => 'channels',
        'alias' => 'Channel',
        'type' => 'LEFT',
        'conditions' => array(
            'Channel.id = Item.channel_id',
        )
    )
);

$Item->find('all', $options);

Note that the ‘join’ arrays are not keyed.

In the above example, a model called Item is left joined to the channels table. You can alias the table with the Model name, so the retrieved data complies with the CakePHP data structure.

The keys that define the join are the following:

• table: The table for the join.
• alias: An alias to the table. The name of the model associated with the table is the best bet.
• type: The type of join: inner, left or right.
• conditions: The conditions to perform the join.

With joins, you could add conditions based on Related model fields:

$options['joins'] = array(
    array('table' => 'channels',
        'alias' => 'Channel',
        'type' => 'LEFT',
        'conditions' => array(
            'Channel.id = Item.channel_id',
        )
    )
);

$options['conditions'] = array(
    'Channel.private' => 1
);

$privateItems = $Item->find('all', $options);

You could perform several joins as needed in hasBelongsToMany:

Suppose a Book hasAndBelongsToMany Tag association. This relation uses a books_tags table as join table, so you need to join the books table to the books_tags table, and this with the tags table:

$options['joins'] = array(
    array('table' => 'books_tags',
        'alias' => 'BooksTag',
        'type' => 'inner',
        'conditions' => array(
            'Books.id = BooksTag.books_id'
        )
    ),
    array('table' => 'tags',
        'alias' => 'Tag',
        'type' => 'inner',
        'conditions' => array(
            'BooksTag.tag_id = Tag.id'
        )
    )
);

$options['conditions'] = array(
    'Tag.tag' => 'Novel'
);

$books = $Book->find('all', $options);

Using joins with Containable behavior could lead to some SQL errors (duplicate tables), so you need to use the joins method as an alternative for Containable if your main goal is to perform searches based on related data. Containable is best suited to restricting the amount of related data brought by a find statement.

Answer 2

First thing to do is in your parent model, set the propriety recursive to -1 like that $this->recursive = -1; by doing that cake will load only models we set in the contain array(). Thanks

Answer 3

Theoretically, that should work fine. Make sure that you've set

var $actsAs = array('Containable');

on every model that you're referencing.

Edit: Looking more closely, perhaps not. Try the following...

$this->set(
  "posts", 
  $this->Post->find(
    "all", array(
      "conditions" => array("Post.wall_id" => $wall["Wall"]["id"]), 
      "contain" => array("Participant" => array("User"))
    )
  )
);

Answer 4

Does this tend to help:

$this->loadModel('Participant');
                $this->Participant->bindModel(array(
                    'belongsTo' => array(
                        'User' => array(
                            'className' => 'Wish',
                            'foreignKey' => 'user_id'
                        )
                    )
                ),0);

 $this->set(
  "posts", 
  $this->Post->find(
    "all", array(
      "conditions" => array("Post.wall_id" => $wall["Wall"]["id"]), 
      "contain" => false
    )
  )
);

Answer 5

I am using the ContainableBehaviour in one of my projects and it works exactly as expected even with a large number of relationships. I believe it is not working correctly in your case because of the following relationships:

• Participant has many Post
• Participant has many Comment
• Participant belongs to Post
• Participant belongs to Comment

Why does a Participant belong to a Post? Why does a Participant belong to a Comment?

Does contain work as expected when you get rid of those relationships?

If you need these all these relationships and have set up your HABTM relationship correctly. Check you this post which explains how to use the ContainableBehaviour with a HABTM relationship.

Answer 6

I never used the Dot Syntax. Did you try

"contain" => array('Participant'=>array('User'))

?

For me it looks like the containable behavior is somehow not attached, either. How did you attach/activate your behavior?

Answer 7

I have been solving same problem and then I found that in some Model I set afterFind callback and within another SQL Query, which made mess in containable behaviour. So I suggest to rewrite inner queries in callbacks to plain SQL, helped for me and solved containable chaining.