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


#3

If i do this:

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

I get a message: InvalidArgumentException: Degrees is not associated with FieldsOfStudies


Associating _joinData
#4

What eventually worked was adding a new assocciation to the ApplicantsTable

   $this->hasMany('DegreesOfTheApplicant',
        [
            'bindingKey' => 'id',
            'foreignKey' => 'applicant_id',
            'className' => 'ApplicantsDegrees',
        ]);

calling the find like this:

$test = $this->ApplicantsTable
    ->find()
    ->where(
        [
            'Applicants.id' => '00000000-0000-0000-0000-100000000001',
        ]
    )
    ->contain(
        [
            'DegreesOfTheApplicant' => ['FieldsOfStudies'],
        ]);

seems to get the results as i would expect it.