CakePHP 3.x - hasMany through association - find

13,516

Normally you'd use matching, but the ORM doesn't seem to support matching on join table "associations", as they are not "real" associations at that point (you may want to suggest that as an enhancement), they are being added at a later point.

matching() workaround

What works is using matching() and where() on the outer query, ie

$query = $this->Courses
    ->find('all')

     // contain needs to use `Students` instead (the `CourseMemberships`
     // data can be found in the `_joinData` property of the tag),
     // or dropped alltogether in case you don't actually need that
     // data in your results
    ->contain(['Students'])

     // this will do the magic
    ->matching('Students')

    ->where([
        'CourseMemberships.student_id' => $student['id'],
        'CourseMemberships.grade' => 'A'
    ]);

This will join in the students table as well as the courses_students join table using the CourseMemberships alias, like

INNER JOIN
    students Students ON 1 = 1
INNER JOIN
    courses_students CourseMemberships ON (
        Courses.id = (CourseMemberships.course_id)
        AND Students.id = (CourseMemberships.student_id)
    )

and so the conditions can be applied. That feels like a not very nice workaround tough.

Use an additional association (probably the better approach)

Another option would be to add another, explicit association (as kind of mentioned @AtaboyJosef), ie a hasMany association for the join table (this would be done automatically at a later point, but as already mentioned, it's too late for matching()).

Note that this will require the join table to be named course_memberships!

class CoursesTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsToMany('Students', [
            'joinTable' => 'course_memberships',
            'through' => 'CourseMemberships',
        ]);

        $this->hasMany('CourseMemberships', [
            'foreignKey' => 'course_id'
        ]);
    }
}

That way you can use matching on the CourseMemberships association

$query = $this->Courses
    ->find('all')
    // with this solution you can also use contain for `CourseMemberships`
    ->contain(['CourseMemberships'])
    ->matching('CourseMemberships', function(\Cake\ORM\Query $query) use ($student) {
        return $query->where([
            'CourseMemberships.student_id' => $student['id'],
            'CourseMemberships.grade' => 'A'
        ]);
    });

which should create a query like

INNER JOIN course_memberships CourseMemberships ON (
    CourseMemberships.student_id = 1 
    AND CourseMemberships.grade = 'A' 
    AND Course.id = (CourseMemberships.course_id)
)

which might be a little more efficient as it requires less selects.

Share:
13,516
Zbigniew Ledwoń
Author by

Zbigniew Ledwoń

https://about.me/zledwon

Updated on June 20, 2022

Comments

  • Zbigniew Ledwoń
    Zbigniew Ledwoń over 1 year

    Assuming I have exactly the setup as in CookBook here: http://book.cakephp.org/3.0/en/orm/associations.html

    class StudentsTable extends Table
    {
        public function initialize(array $config)
        {
            $this->belongsToMany('Courses', [
                'through' => 'CourseMemberships',
            ]);
        }
    }
    
    class CoursesTable extends Table
    {
        public function initialize(array $config)
        {
            $this->belongsToMany('Students', [
                'through' => 'CourseMemberships',
            ]);
        }
    }
    
    class CoursesMembershipsTable extends Table
    {
        public function initialize(array $config)
        {
            $this->belongsTo('Students');
            $this->belongsTo('Courses');
        }
    }
    
    Student BelongsToMany Course
    Course BelongsToMany Student
    
    id | student_id | course_id | days_attended | grade
    

    How should I construct the query to find Courses for given Student that he has Grade == "A"?

    $query = $this->Courses->find('all')
        ->contain(['CourseMemberships'])
        ->where(['CourseMemberships.student_id' => $student['id'], 'CourseMemberships.grade' => 'A']);
    

    This will not work. How should I write it?