CakePHP: How to use Containable for nested HABTM m

Question

I'm trying to use Containable to return a model with several of its associated models (and those arrays of data). The models deal with test results. Here are the models:

Models:

Question : has and belongs to many Category
Category : has and belongs to many Question
Attempt : has many AttemptedQuestions
AttemptedQuestion : belongs to Question

I want to return an Attempt with all of it's AttemptedQuestions and their corresponding Quesions + Category

So basically, the relationships map like this:

Attempt => AttemptedQuestion(s) => Question => Category

I suspect that because of the HABTM relationship, Cake would prefer to return:

Attempt => AttemptedQuestion(s) => array( Question, Category ) <--Categories are not contained within the Question array, but rather they are sisters. This is fine as well.

At the moment I am unable to get Category to show up at all. Here's what I'm doing inside a controller (this does NOT result in the Categories appearing in results):

$this->Attempt->contain(array('AttemptedQuestion' => array('Question'=>'Category'))); 
$attempt_to_be_graded = $this->Attempt->findById( $attempt_id );

What am I doing wrong?

Update Here's a revision based on the answer from @nunser. This also does not work.

$this->Attempt->contain(array('AttemptedQuestion' => array('Question'=>array('Question'=>array('Category') )))); 
$attempt_to_be_graded = $this->Attempt->findById($attempt_id );

This is what the data returned looks like:

array(
    'Attempt' => array(
        'id' => '39',
        ...
    ),
    'AttemptedQuestion' => array(
        (int) 0 => array(
            'id' => '189',
            ...,
            'Question' => array(
                'id' => '165',
                ...
            )
        ),
        (int) 1 => array(
            'id' => '188',
            ...,
            'Question' => array(
                'id' => '164',
                ...
            )
        )

    )
)

Update 2

I'm still struggling with this, but I think my associations have to be correct because the following returns a list of all my Categories just as expected:

$categories = $this->Attempt->AttemptedQuestion->Question->Category->find('all');

Update 3

I've narrowed down the scope of this problem by testing the results of $this->Question->find('first') from various points throughout the code. It appears that the results are expected UNTIL after this: $test = $this->Test->findById($test_id);.

Here is the code which demonstrates:

    $this->loadModel('Question');       
    $test = $this->Question->find('first');
    debug($test);
//THESE RESULTS INCLUDE Category DATE

    $test = $this->Test->findById($test_id);

    $this->loadModel('Question');       
    $test = $this->Question->find('first');
    debug($test);
    exit;
//THESE RESULTS DO NOT INCLUDE Category DATE

So for reasons I completely do not understand, the intervening Test->find() seems to prevent the Category data from appearing afterwards. Weird, huh?

Answer 1

Sometimes, on my side is better to use binModel nested instead of a behavior, cause is more configurable, check please this link, and you probably could help you on your question

CakePHP - Building a Complex Query

cakephp join more than 2 tables at once in one join table

Answer 2

Try

$this->Attempt->find('first', array('conditions'=>array('id'=>$attempt_id),
                                'contain'=>array('AttemptedQuestion'=> 
                                               array('Question' => 
                                                  array('Category')
                                               )
                                )));

Answer 3

HABTM Headaches

These are difficult to manage in Cake. What I've started doing is called "hasMany Threw" which is described in the book here.

The advantage is more control over find queries. It requires an additional Model be added, but it's worth it. The automated handling of HABTM in CakePHP is often difficult to understand. Using associations to explicitly define the relationships is more flexible.

Circular Reference

Your Models are currently setup with a circular reference, and this can be problematic for a HABTM.

Question : has and belongs to many Category
Category : has and belongs to many Question

It is very difficult in Cake to create a HABTM on both models. Usually this is done only on one of them, and I'd pick Question. With a "hasMany Threw" it's easier.

HasMany Threw

If we use a "hasMany Threw" then the containable rules become a lot easier to explain.

Question : hasMany QuestionCategory
Category : hasMany QuestionCategory
QuestionCategory : belongs to Question, belongs to Category.
Attempt : belongs to Question

Now, I removed AttamptedQuestion. I'm not exactly sure what you're trying to model, but if a person can create multiple Attempts per Question, then it's not needed. Just create more then one Attempt record.

Find All Records For An Attempt

To find all the associated records for an Attempt using Containable the find would be as follows.

$this->Attempt->find('first',array(
    'conditions'=>array('Attempt.id'=>$attempt_id),
    'contain'=>array(
        'Question'=>array(
            'QuestionCategory'=>array(
                'Category'=>array()
            )
        )
    ));

You can still add back the AttemptedQuestion model, and just contain it before Question.

Ambiguous Fieldnames With Associations

You should get into the habit of using Attempt.id in conditions instead of just id. When you uses associations there are often duplicate fieldnames in the SQL result. You need to clarify which ones you are referring to, because you can also have Question.id as a condition in Attempt if a JOIN is used. So id is ambiguous on it's own.

Answer 4

Give the new information about what triggers the problem.

$this->loadModel('Question');       
$test = $this->Question->find('first');
debug($test);
//THESE RESULTS INCLUDE Category DATE

$test = $this->Test->findById($test_id);

$this->loadModel('Question');       
$test = $this->Question->find('first');
debug($test);
exit;
//THESE RESULTS DO NOT INCLUDE Category DATE

In the above scenario the findById magic function (that's what I call them) calls the lower level data source driver to perform the query.

In the DboSource.php it's not using the Containable behavior to control recursion, and changes the recursive property in the Model. I think this is causing the trouble with the Containable behavior.

You can fix this by reset the recursive back to it's default after the findById.

$test = $this->Test->findById($test_id);
$this->Test->recursive = 1;

$this->loadModel('Question');       
$test = $this->Question->find('first');
debug($test);
exit;

I'm going to guess this fixes the problem, but you have to remember to reset it after using findById or any other magic function.

This should be report to the Cake developers as a bug if it's reproducible.

EDIT:

recursive default is 1 not 0.