CakePHP conditions in associated Models

Question

I have a database with Classes, Semesters, Users, and Visits(Visits is a join table for Users and Classes) The relations are:

User hasAndBelongsToMany Class (through Visits Class belongsTo Semester

Now I want to view all Visits with Classes in an active Semester (The Semester table has a field is_active) I read about a contain option for the find method and tried something like this:

$option = array(
    "contain" => array(
       "Class" => array(
          "Semester" => array(
                 "conditions" => array("Semester.is_active" => true)
           )
        ),
     ),
     "conditions" => array(
         "Visit.user_id" => $id,
         )
     );

But with this, classes in a not active semester are found, only the semester isn't.

Is something wrong with this? Or is there an other way?

Answer 1

Now I have a solution: I used the joins option for the find method.

"joins" => array(
         array(
            "table" => "classes_semesters",
            "alias" => "ClassesSemesters",
            "type" => "inner",
            "conditions" => array(
                "Visit.class_id = ClassesSemesters.class_id"        
            )   
        ),
        array(
            "table" => "semesters",
            "alias" => "Semester",
            "type" => "inner",
            "conditions" => array(
                "ClassesSemesters.semester_id = Semester.id"        
            )       
        )
 ),
"conditions" => array(
        "Visit.user_id" => $id,
        "Semester.is_active" => true
),

Answer 2

When you use contain, like in your case, the query is not a direct inner join, that is, Class inner joins with Semester. the class records are pulled first, then there will be a second query on the semesters table with the condition (where class_id IN ( results of first query). so even if there are no records found in the semesters table, cake will still return the Class records found.

query 1. $class = select * from classes where bla bla 
query 2. select * from semesters where class_id in (result from query 1) and other conditions bla bla

cake then merges the result from the 2 queries together to produce 1 result.