CakePHP: Using multilevel Containable Behaviour

Question

I've been quite some time trying to use the Containable Behavior in CakePHP but I can't get to make it work as I expected.

My application is different, but to simplify I'll put this example. Let's say I have a forum with threads and activities, and the activities can be rated. The general relations would be:

Forum: hasMany [Thread]
Thread: belongsTo [Forum], hasMany [Activity]
Activity: belongsTo [Thread], hasMany [Rating]
Rating: belongsTo [Activity]

What I want to achieve is, using the find method, get all the ratings performed on a certain forum. What I suppose should be done is the following:

 $this->Rating->find('count', array(
    'contain' => array(
        'Activity' => array(
            'Thread'
        )
    ),
    'conditions' => array(
        'Thread.forum_id' => 1
    )
));

But the result query is:

SELECT COUNT(*) AS `count` FROM `ratings` AS `Rating` LEFT JOIN `activities` AS `Activity` ON (`Rating`.`activity_id` = `Activity`.`id`) WHERE `Thread`.`forum_id` = 1;

I've accomplished this using the 'joins' option, but it's more complex and I have to use this kinda action in many situations.

All the files related with the example can be found here: http://dl.dropbox.com/u/3285746/StackOverflow-ContainableBehavior.rar

Thanks

Update 23/11/2011

After investigating the framework and thanks to the answers of Moz Morris and api55 I found the source of the problem.

The basic problem was that, as I understood CakePHP, I thought it was querying using joins each time. The thing it that it doesn't do that, the real operation it would perform to obtain the result I was looking for would be something like this:

SELECT * FROM Rating JOIN Activity...
SELECT * FROM Activity JOIN Thread...
SELECT * FROM Activity JOIN Thread...
...

Meaning that it would do a query to get all the activities and then, for each activity, perform a query to get the Threads... My approach was failing not because of the Containable Behaviour being used wrong, but because the 'conditions' option was applied to all queries and, on the first one, it crashed because of the absence of the Thread table. After finding this out, there are two possible solutions:

• As api55 said, using the conditions inside the 'contain' array it would apply them only to the queries using the Thread table. But doing this the problem persists, because we have way too many queries.

• As Moz Morris said, binding the Thread model to Rating would also work, and it would perform a single query, which is what we want. The problem is that I see that as a patch that skips the relations betweem models and doesn't follow CakePHP philosophy.

I marked api55 solution as the correct because It solves the concrete problem I had, but both give a solution to the problem.

Answer 1

First of all, have you put the actAs containable variable in the appModel?? without it this beahaviour won't work at all (i see it is not working correctly since it didn't join with Thread table)

I would do it from the top, i mean from forum, so you choose your forum (im not sure you want forum or thread) and get all its rating, if theres no rating you will end up with the rating key empty.

something like this

appModel

public $actsAs = array('Containable');

rating controller

 $this->Rating->Activity->Thread->Forum->find('count', array(
    'contain' => array(
        'Thread' => array(
             'Activity' => array(
                 'Rating' => array (
                       'fields' => array ( 'Rating.*' )
                  )
              )
        )
    ),
    'conditions' => array(
        'Forum.id' => 1
    )
));

Then if you need only a value in rating table just use Set:extract to get an array of this value.

As you did it IT SHOULD work anyways but i sugest not to use forum_id there, but in conditions inside contain like this

'contain' => array(
    'Activity' => array(
        'Thread' => array(
              'conditions' => array('Thread.forum_id' => 1)
         )
    )
),

Also, never forget the actsAs variable in the model using the containable behaviuor (or in app model)