CakePHP 3.x - hasMany 通过关联 - 查找 [英] CakePHP 3.x - hasMany through association - find

问题描述

假设我的设置与这里的 CookBook 完全相同:http://book.cakephp.org/3.0/en/orm/associations.html

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

我应该如何构造查询以查找给定学生的课程，他的成绩 ==A"?

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?

推荐答案

通常你会使用 匹配，但 ORM 似乎不支持连接表关联"的匹配，因为它们是那时不是真正的"关联(您可能想要建议将其作为增强功能)，它们将在稍后添加.

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() 和 where()，即

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

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

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.

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

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()).

请注意，这将要求将连接表命名为course_memberships！

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

这样你就可以在 CourseMemberships 关联上使用匹配

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(CakeORMQuery $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)
)

这可能会更有效一些，因为它需要更少的选择.

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

这篇关于CakePHP 3.x - hasMany 通过关联 - 查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！