CakePHP 3.x - hasMany 通过关联 - 查找

Question

假设我的设置与这里的 CookBook 完全相同:
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

我应该如何构造查询以查找给定学生的类(class)，他的成绩是“A”？

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

这是行不通的。我应该怎么写？

Answer 1

通常你会使用 matching ，但 ORM 似乎不支持对连接表“关联”的匹配，因为它们在那时不是“真正的”关联(您可能想要 suggest that as an enhancement)，它们将在稍后添加。
matching()解决方法

有效的是使用 matching()和 where()在外部查询上，即

$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'
    ]);

这将加入 students表以及 courses_students使用 CourseMemberships 连接表别名，如

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

因此可以应用条件。感觉这不是一个很好的解决方法。

使用额外的关联(可能是更好的方法)

另一种选择是添加另一个显式关联(如@AtaboyJosef 所述)，即 hasMany连接表的关联(这将在稍后自动完成，但正如已经提到的，对于 matching() 为时已晚)。

请注意，这将要求将连接表命名为 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'
        ]);
    }
}

这样你就可以在 CourseMemberships 上使用匹配协会

$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'
        ]);
    });

这应该创建一个查询

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

这可能会更有效率，因为它需要更少的选择。