CakePHP Containable showing wrong data

Question

I have been looking around for a long time regarding the CakePHP containable, and somehow I get the impression there is a mistake in the CakePHP code... Let me give you the following example

Assume a Model "Accounting" that is related to "Instructions".

public $belongsTo = array('Instruction');

I do a join like this:

$options['contain'] = array(
        'Instruction' => array(
           'fields'  => array('Instruction.id', 'Instruction.main_student_id', 'Instruction.teacher_id'),
           'Student' => array(
               'fields' => array('Student.id', 'Student.full_name', 'Student.invoice_payer_id'),
           ),
           'Teacher' => array(
                'fields' => array('Teacher.id', 'Teacher.full_name'),
                'conditions' => array('Teacher.id' => $teacher_id)
           ),
        )
);      
return parent::find('all', $options);

So there is an instruction and to this instruction a student and a teacher belong to. I call find from the "Accounting" model

What I expect

the joins are done automatically and correct
the fields mentioned in the contain are retrieved

What I get

the fields are joined, but wrongly; so the result array contains an Instruction with a Student and a Teacher. But instead of displaying the correct Teacher belonging to an Instruction, it always displays the one specified in the condition.

Example:

Assume the table Instruction related to the Teachers "1 - John Appleseed","2 - Peter Googleseed", "3 Larry Microseed"
Assume multiple Instructions belonging to the 3 Teachers
if $teacher_id = 3 the query returns ALL Instruction, and instead of displaying the correct Teacher belonging to the Instruction, always the Teacher with the id = 3 is displayed; to be more precise: [Instruction][teacher_id] is set to the correct value, but the [Instruction][Teacher] is always set to the teacher of the condition

But let's go a step further:

if $recursive = -1 is set then I only get returned only the fields of the model "Accounting"; the fields of Instruction, Student, Teacher are not retrieved

Let's make sure

I put "public $actsAs = array('Containable');" in AppModel from which all the other Models inherit
Yes, Instruction has a "$belongsTo = array('Teacher', 'Student' => array(...))"
The fields necessary for joining are all chosen

So, what?

Could you please try to help me to understand... - why the joins are done wrongly? - why not all the fields are retrieved?

I tried already

To set the joins explicitly: very weird, then [Instruction][teacher_id] is empty and [Instruction][Teacher] is not set!
To leave out the explicit choice of the fields being retrieved

have you tried the contain without the fields?? just something like $this->find('all',array('contain'=>array('Instruction'=>array('Student','Teacher'))).. It should return all instructions with the associated Student and Teacher. If not, you'll know there's a problem on the relations between your models — pleasedontbelong
Yes, I tried that. Quite weird: it returns all the data, but now only displays a Teacher if the correct one is set (i.e. if the teacher fulfills the condition), otherwise [Instruction][Teacher] is not set. How can I just return the Instructions where the condition is true? It looks like it does a left join instead of an inner join — navige

pleasedontbelong pleasedontbelong · Accepted Answer · 2012-10-12T09:38:33

Containable also goes a step deeper: you can filter the data of the associated models.

http://book.cakephp.org/2.0/en/core-libraries/behaviors/containable.html#containing-deeper-associations

The conditions you give on the containable are used as filters and not as restrictions for the joins. So it will not return the Instructions of a certain Teacher, but return all Instructions with their associated Teachers filtering the teachers with the condition array('Teacher.id' => $teacher_id)

Try adding the condition on the Instruction instead of the Teacher. Something like:

'Instruction' => array(
    'Student','Teacher',
    'conditions' => array('Instruction.teacher_id' => $teacher_id)

and this will return ALL the Accounting and it will filter only the Instructions of a certain teacher.

You could always do the join manually (It migth not look good but it works), just remember to set containable to false if you are doing it.

Hope this helps