Clever Geek Handbook

CakePHP 3.x - hasMany via association - find

Assuming I have exactly the same setup as in the 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 build a query to find the Courses for a given Student that he has class == "A"?

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

It won't work. How should I write?

+3


source to share


2 answers


You usually use matching , but the ORM does not seem to support matching in the "association" join table as they are not "real" associations at this point (you might want to suggest as an improvement ), they are added later.

matching()

workaround

Which works using matching()

and where()

in the outer query i.e.

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

It will be a join to a table students

as well as a join table courses_students

using an alias CourseMemberships

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 therefore conditions may apply. This looks like a not-so-nice workaround.

Use additional association (perhaps a better approach)



Another option would be to add another explicit association (like the kind mentioned by @AtaboyJosef), i.e. an association hasMany

for the join table (this will be done automatically at a later point, but as mentioned, this is too late for matching()

).

Note that this requires 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'
        ]);
    }
}

This way you can use matching in association 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'
        ]);
    });

which should create a request like

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

which can be a little more efficient as it requires less choice.

+15


source


through

Allows you to specify the name of the table instance that you want to use in the join table, or the instance itself. This makes it easy to customize the join table keys and allows you to customize the behavior of the pivot table.

Define more specific relationships using the array syntax:

class StudentsTable extends Table
{

    public function initialize(array $config)
    {
        $this->belongsToMany('Courses', [
            'joinTable' => 'courses',
            'through' => 'CourseMemberships',
        ]);
    }
}

class CoursesTable extends Table
{

    public function initialize(array $config)
    {
        $this->belongsToMany('Students', [
            'joinTable' => 'students',
            'through' => 'CourseMemberships',
        ]);
    }
}

class CoursesMembershipsTable extends Table
{

    public function initialize(array $config)
    {
        $this->belongsTo('Students', [
            'foreignKey' => 'student_id',
            'joinType' => 'INNER',        // OR specify the type 
        ]);
        $this->belongsTo('Courses', [
            'foreignKey' => 'course_id',
            'joinType' => 'INNER',
        ]);
    }
}

Make sure you have tables courses

, students

and course_memberships

.



Now run the code:

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

Well I'm afraid if you really need something related to . HasMany Associations

0


source






More articles:


All Articles