Trying to get additional data using BelongsToMany with multiple tables


#1

I am trying to use a belongsToMany relation where the extra data are ids of other tables. It works with my current setup except in _joinData i now only get the id values for the other tables, but i would like to get the other column data as well. Somebody suggested a through-relation, but i cannot seem to grasp the idea of how this should work. Is a through-relation the correct/best way to do this? and how would i set this up in Cake.

APPLICANTS
id | name

DEGREES
id | value

EDUCATIONAL_INSTITUTIONS
id | value

FIELDS_OF_STUDIES
id | value 

APPLICANTS_DEGREES
id | applicant_id | degree_id | educational_institution_id | fields_of_study_id | degree_acquired

The table-models are created by Bake as follows:

class ApplicantsTable extends Table
{

    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->setTable('applicants');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');

        $this->addBehavior('Timestamp');

        $this->belongsToMany('Degrees', [
            'foreignKey' => 'applicant_id',
            'targetForeignKey' => 'degree_id',
            'joinTable' => 'applicants_degrees'
        ]);

    }
}

class DegreesTable extends Table
{

    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->setTable('degrees');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');

        $this->belongsToMany('Applicants', [
            'foreignKey' => 'degree_id',
            'targetForeignKey' => 'applicant_id',
            'joinTable' => 'applicants_degrees'
        ]);
    }
}


class FieldsOfStudiesTable extends Table
{

    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->setTable('fields_of_studies');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');


    }
}

class EducationalInstitutionsTable extends Table
{

  
    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->setTable('educational_institutions');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');

    }
}

class ApplicantsDegreesTable extends Table
{

    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->setTable('applicants_degrees');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');

        $this->belongsTo('Applicants', [
            'foreignKey' => 'applicant_id',
            'joinType' => 'INNER'
        ]);
        $this->belongsTo('Degrees', [
            'foreignKey' => 'degree_id',
            'joinType' => 'INNER'
        ]);
        $this->belongsTo('EducationalInstitutions', [
            'foreignKey' => 'educational_institution_id',
            'joinType' => 'INNER'
        ]);
        $this->belongsTo('FieldsOfStudies', [
            'foreignKey' => 'fields_of_study_id',
            'joinType' => 'INNER'
        ]);
    }
}

I call find like this:

 $applicant = $this->Applicants->findById($applicant_id)
            ->find('public')
            ->contain([
                'Degrees',
            ])
            ->first();

Now the _joinData will contain degree_id, fields_of_study_id, educational_institution_id, but not the value from the other column.

What do i need to change to get this working. If i need a through relation, where and how do i create this?


#2

i think you missed out to define the relationship of FieldOfStudiesTable and EducationalInstitutionsTable, is it belongToMany Applicants or to Degrees