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.

Kingdom

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)

Shutter

Moving the 'conditions' inside the Thread array won't work because results will still be returned for everything else, with the 'Thread' array empty. – Darrendarrey

@MozMorris thats true, because is left join... but if he runs it from the top (from forum/thread) it will get all ratings or empty, though it will have a lot of annoying tree (i usually use the linkable behaviour) – Shutter

Thanks for replying. I tried that on the code I provided on the link and the query is SELECT COUNT(*) AS count FROM forums AS Forum WHERE Forum.id = 1. I already have the Containable Behaviour set on the AppModel and everything. I don't know if I have some configuration problem or something, but it doesn't seem to be working correctly. – Kingdom

@NoelDeMartin to be 100% sure that you are loading the behaviour correctly can you write in your controller the following piece of code $this->Rating->Behaviors->attach('Containable'); this is for testing purposes... if you are calling the find from Forum do $this->Rating->Activity->Thread->Forum->Behaviors->attach('Containable') – Shutter

@Shutter I've updated the question with the final solution, thank you for your answer, it helped me find the problem. – Kingdom

Answer 2

Whist I like api55's solution, I think the results are a little messy - depends on what you intend to do with the data I guess.

I assume that when you said using the 'joins' method you were talking about using this method:

$this->Rating->bindModel(array(
  'belongsTo' => array(
    'Thread' => array(
      'foreignKey' => false,
      'conditions' => 'Thread.id = Activity.thread_id',
    ),
    'Forum' => array(
      'foreignKey' => false,
      'conditions' => 'Forum.id = Thread.forum_id'
    )
  )
));

$ratings = $this->Rating->find('all', array(
  'conditions' => array(
    'Forum.id' => 1 // insert forum id here
  )
));

This just seems a little cleaner to me, and you don't have to worry about using the containable behaviour in your AppModel. Worth considering.

Darrendarrey

If you need the count, then just replace the 'all' with 'count', obviously. :) – Darrendarrey

i never thought of that XD but its true its cleaner, the other way gives to many recursions... – Shutter

Thank you for your answer, that would work on this example. The problem is that on the real application I have to do this many times and I want to do it more modular (having an array with the "enabled" models to use). Doing what you say I should also set all the conditions each time. Besides, it bugs me that it's not working, that's what the Containable Behaviour is supposed to be for, I don't know if I'm missing something. Thank you anyways. – Kingdom