# Trying to get additional data using BelongsToMany with multiple tables

**URL:** <https://discourse.cakephp.org/t/trying-to-get-additional-data-using-belongstomany-with-multiple-tables/3699>\
**Category:** Need Help\
**Created:** [December 28, 2017, 9:10am UTC](https://discourse.cakephp.org/t/trying-to-get-additional-data-using-belongstomany-with-multiple-tables/3699 "2017-12-28T09:10:21Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![wschwarte](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/wschwarte/32/31_2.png) [@wschwarte](https://discourse.cakephp.org/u/wschwarte)\
**Post date:** [December 28, 2017, 9:10am UTC](https://discourse.cakephp.org/t/trying-to-get-additional-data-using-belongstomany-with-multiple-tables/3699/1 "2017-12-28T09:10:21Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![donnidani](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/donnidani/32/744_2.png) [@donnidani](https://discourse.cakephp.org/u/donnidani)\
**Post date:** [December 28, 2017, 8:09pm UTC](https://discourse.cakephp.org/t/trying-to-get-additional-data-using-belongstomany-with-multiple-tables/3699/2 "2017-12-28T20:09:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![wschwarte](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/wschwarte/32/31_2.png) [@wschwarte](https://discourse.cakephp.org/u/wschwarte)\
**Post date:** [March 13, 2018, 4:43pm UTC](https://discourse.cakephp.org/t/trying-to-get-additional-data-using-belongstomany-with-multiple-tables/3699/3 "2018-03-13T16:43:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![wschwarte](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/wschwarte/32/31_2.png) [@wschwarte](https://discourse.cakephp.org/u/wschwarte)\
**Post date:** [March 14, 2018, 9:30am UTC](https://discourse.cakephp.org/t/trying-to-get-additional-data-using-belongstomany-with-multiple-tables/3699/4 "2018-03-14T09:30:01Z")

</div>

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.