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.