Need some help with belongsToMany saving with extra info fields

Need Help
1

I have three database tables:

-applicants
-language_skills
-applicants_language_skills

applicants_language_skills is the join table, which has some extra columns:

id | applicant_id | language_skill_id | read_level | write_level

these are set up like this:

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

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

        $this->belongsToMany('Applicants', [
            'through' => 'ApplicantsLanguageSkills',
        ]);
    }

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

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

        $this->belongsToMany('LanguageSkills', [
            'through' => 'ApplicantsLanguageSkills',
        ]);
    }

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

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

        $this->belongsTo('Applicants');
        $this->belongsTo('LanguageSkills');

    }

I need some code examples on how to read, insert and update. I do not understand the documentation very good.

reading
This works:

        $applicant = $this->Applicants->findBySlug($applicant_slug)
            ->contain([
                'LanguageSkills',
            ])
            ->first();

I get an applicant entity which has an array of language_skills and in each language_skill is a value _joinData that contains read_level and write_level.

How should i update values of read_level and write_level in the joinTable.

I tried this, which works, but is this the correct way???

       $current_readlevel = $applicant->language_skills[0]->_joinData->read_level;
        $applicant->language_skills[0]->_joinData->read_level = 5;
        $applicant->setDirty('language_skills', true);

        $result =  $this->Applicants->save($applicant, [
            'associated' => ['LanguageSkills._joinData'],
        ]);

How can i add a new language skill for the applicant ?

$skill_klingon = $this->Applicants->LanguageSkills->get(5); // get a skill from the db
$skill_klingon->_joinData = new Entity(['read_level' => 3, 'markNew' => true]);
$applicant->language_skills[] = $skill_klingon;

$applicant->setDirty('language_skills', true);
$result =  $this->Applicants->save($applicant);

This works, but if the $applicant already has a language_skill with id 5, it is not overwritten, i end up with two entries in the jointable for the applicant_id and language_skill_id 5.

The docs are not very helpful and searching google also does not really help. The docs also talk about link() for example.

Hope somebody has some examples using this setup.

2

One way is to change the relationships such that the join table has 2 hasMany relationship and the other two tables have belongsTo relationships. When you save your data you would save to join table with associated data being the other 2 tables. That make sense?

3

If you want to continue exploring saving with joinData:

https://book.cakephp.org/3.0/en/orm/saving-data.html#saving-hasmany-associations
https://book.cakephp.org/3.0/en/orm/saving-data.html#saving-additional-data-to-the-join-table

This looks correct to me, that’s one way to do it.

For the below, I would use link() to add another row. belongsToMany has 2 strategies for handling the join table replace and append. Replace is the default – all rows will be replaced with the entities you set in language_skills. The strategy is defined in the initialize() function. Are you using the “append” strategy?

You might consider making a database level unique key on applicant_id,language_id if those are indeed unique keys. That would prevent a duplicate row being created.

4

hi,

i have read the book links, very unclear with bad incomplete examples.

Do you have a code example for using the link method you mentioned?

5

Not really. This is the setup as explained in the book. i think if i changed it the other way around, other things might become more complicated to handle?

6

one of options is to create new entity “applicant skill” with manually set ‘applicant_id’. When you save it, db will give you fresh ID (if you have got auto_increment in your primary key definition). You can pass it to newEntity
$some_thing = newEntity(["applicant_id" => 3]);