Why do conditions in model associations not filter the find results in the controller in CakePHP?

Question

I have two models: Users and Lessons

• User has one Teacher
• User has and belongs to many Students

Both Teachers and Students are Users

In my Users model I have this association:

public $hasMany = array(
    'Lesson' => array(
        'className' => 'Lesson',
        'foreignKey' => 'user_id',
        'dependent' => false,
    )
);

In my Lessons model I currently have this association:

public $belongsTo = array(
    'Teacher' => array(
        'className' => 'User',
        'foreignKey' => 'teacher_id',
        'conditions' => array('Teacher.group_id' => '2'),
        'fields' => '',
        'order' => ''
    )
);

My questions are:

1. Why does $teachers = $this->Lesson->Teacher->find('list'); return all the users and not just the teachers? Why don't the conditions set in the belongsTo part of the model filter the results to show only the Users with the group_id of 2 i.e. the Teachers? And if that's not the way it's supposed to work then what is the point of setting conditions here?

   As of now I have to include it in the find each time which seems kind of redundant: $teachers = $this->Lesson->Teacher->find('list', array('conditions'=>array('Teacher.group_id'=>'2')))

2. How do I write the associations for Students so that Lessons has many students? And how do I set up the database for it?

Answer 1

1. The condition in your association should be changed to include the Model's name rather than the alias:

'conditions' => array('User.group_id' => 2),

2. The association should be hasAndBelongsToMany (HABTM), and can be defined in your User model like this:

public $hasAndBelongsToMany = array(
    'Lessons' => array(
        'className' => 'Lesson',
        'joinTable' => 'lessons_users', // or the name from DB
        'foreignKey' => 'user_id',
        'associationForeignKey' => 'lesson_id',
        'unique' => 'keepExisting' // Set this based on your needs, check link above
    )
);

Given the association above, you need a new table lessons_users in your database to store data about this relationship between students and lessons:

          +++++++++++++++++++++++++++++++++++++++++++++++++
          +  id (pk)  +  lesson_id (fk)  +  user_id (fk)  +
          +++++++++++++++++++++++++++++++++++++++++++++++++
          +     1     +         1        +        1       +
          +-----------+------------------+----------------+
          +     2     +         1        +        2       +
          +-----------+------------------+----------------+
          +     3     +         2        +        1       +
          +-----------+------------------+----------------+
          +     4     +         2        +        2       +
          +++++++++++++++++++++++++++++++++++++++++++++++++

From the example above; user with id 1 is taking lessons 1 & 2, and user with id 2 is taking lessons 1 & 2 as well. Likewise, lesson with id 1 has users with ids 1 & 2 enrolled, and lesson with id 2 has users with ids 1 & 2 enrolled as well.