It is possible to replace an inner join query by a containable in a habtm relationship?

Question

I read the section Containable and I didn't find a clear example to replace an inner join query on a habtm relationship by a containable query. Example :

Model

Student hasAndBelongsToMany Teacher
Teacher hasAndBelongsToMany Student

Query

$joins = array(
    array(
        'table' => 'teachers_students',
        'type' => 'INNER',
        'conditions' => array(
            'teachers_students.teacher_id' => $teacherId,
            'teachers_students.student_id = Student.id'
        )
    )
);

$data = $this->find('all', array('joins' => $joins));

Comments

The hasAndBelongsToMany attributes are set in each model.
pseudo-variable $this reference to model : class Student
$teacherId is a parameter (there is a filter to show students that belong to one specific teacher).

What I'm looking for

To be able to write the same query without joins, using contain. Something like :

$contain = array(
    'Teacher' => array(
        'conditions' => array('???' => '???')
    )
);    

$data = $this->find('all', array('contain' => $contain));

score 1 · Accepted Answer · answered Aug 13 '14 at 14:37

If I understand your question (trying to get the students for a specific teacher), you'll either need to A) Use Joins, or B) switch the direction of your query and build it from the Teacher model instead:

//Teacher Model
$this->find('all', array(
    'conditions' => array('Teacher.id' => $teacherId),
    'contain' => array(
        'Student'
    )
);

You cannot limit the results of the main-model based on conditions against contained models because using "contain" actually creates separate queries.

It is possible to replace an inner join query by a containable in a habtm relationship?

1 Answers1