CakePHP 3.x - hasMany通过关联 - 查找 [英] CakePHP 3.x - hasMany through association - find
问题描述
假设我在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 '，
]）;
}
}
类CoursesTable extends表
{
public function initialize（array $ config）
{
$ this - > belongsToMany（'Students'，[
'through'=>'CourseMemberships'，
]）;
}
}
类CoursesMembershipsTable extends表
{
public function initialize（array $ config）
{
$ this - > belongsTo（'Students'）;
$ this-> belongsTo（'Courses'）;
}
}
学生属于多种课程
课程属于多学生
id | student_id | course_id | days_attended |成绩
如何构建查询以查找给定学生的课程， ？
$ query = $ this-> Courses-> find（'all'）
- > ; contains（['CourseMemberships']）
- > where（['CourseMemberships.student_id'=> $ student ['id']，'CourseMemberships.grade'=>'A']）;
这不会工作。我应该怎么写呢？
通常你会使用 匹配 ，但ORM不会似乎支持在连接表关联匹配，因为他们不是真正的关联在那一点（你可能想要 建议作为增强 ），稍后才会添加。
matching（）解决方法
使用 match（） $ c> where（）对外部查询，即
$ query = $ this-> ;课程
- > find（'all'）
//包含需要使用学生代替（CourseMemberships
//数据可以在`_joinData`标记的属性），
//或者完全删除，以防你在结果中不需要
//数据
- > contains（['Students' ]）
//这将做魔术
- > match（'Students'）
- >其中（[
'CourseMemberships .student_id'=> $ student ['id']，
'CourseMemberships.grade'=> 'A'
]）;
这将加入 students 以及 courses_students 连接表使用 CourseMemberships 别名，如
INNER JOIN
students学生ON 1 = 1
INNER JOIN
courses_students课程会员资格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'
]）;
}
}
$ c> CourseMemberships 关联
$ query = $ this-& > find（'all'）
//使用这个解决方案你也可以使用CourseMemberships的包含
- > contains（['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）
）
这可能会更高效一些，因为它需要更少的选择。 >
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?
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.
这篇关于CakePHP 3.x - hasMany通过关联 - 查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
