Hi everyone,
I have a problem with my application to solve.
I have a table with users profiles. each user can edit his own profile, and can add and edit courses (user are formers in fact)
When I try to edit my profile, for example, I have two specific fieds who are sort of tags. The user can enter his desired skills and his actual skills. To autocomplete each skill field, I use another table called skills.
And to link both together, I use a third table called skills_map.
The two skills fields can be found in profile form and course form.
I would like to use the same pivot table for both courses and profiles context.
Here is the tables representation
skills_map table (pivot table)
|skill_id|int(11)||
|content_type|varchar(255)|| (Used for “polymorphysm”, context exemple : Profiles.desiredSkills, or Courses.desiredSkills - I use the model to name, followed by skills type)
|content_type_id|int(11)||
|created|datetime||
|modified|datetime|
profiles table
|account_type|int(11)||
|first_name|varchar(255)||
|last_name|varchar(255)||
|gender|varchar(255)||
|slug|varchar(255)||
|active|int(11)||
|hash|varchar(255)||
|data|json NULL||
|created|datetime||
|modified|datetime|
Skills table (used for autocomplete)
|active|int(1)||
|name|varchar(255)||
|slug|varchar(255)||
|created|datetime||
|modified|datetime|
For here I will just put the profiles source code.
My profile Table contains my relations, I’ve added conditions to my belongsToMany, as you can see to use later with my “find with contain” queries.
public function initialize(array $config)
{
parent::initialize($config);
$this->setTable('profiles');
$this->setDisplayField('id');
$this->setPrimaryKey('id');
$this->addBehavior('Timestamp');
$this->hasOne('Users')->setForeignKey('profile_id');
$this->belongsToMany('Businesses', [
'foreignKey' => 'profile_id',
'targetForeignKey' => 'business_id',
'joinTable' => 'businesses_profiles'
]);
$this->belongsToMany('Schedules', [
'foreignKey' => 'profile_id',
'targetForeignKey' => 'schedule_id',
'joinTable' => 'schedules_profiles'
]);
$this->belongsToMany('ActualSkills', [
'className' => 'Skills',
'foreignKey' => 'content_type_id',
'targetForeignKey' => 'skill_id',
'joinTable' => 'skills_map',
'conditions' => ['SkillsMap.content_type' => 'Profiles.ActualSkills'],
]);
$this->belongsToMany('DesiredSkills', [
'className' => 'Skills',
'foreignKey' => 'content_type_id',
'targetForeignKey' => 'skill_id',
'joinTable' => 'skills_map',
'conditions' => ['SkillsMap.content_type' => 'Profiles.DesiredSkills'],
]);
}
My SkillsTable here
public function initialize(array $config)
{
parent::initialize($config);
$this->setTable('skills');
$this->setDisplayField('name');
$this->setPrimaryKey('id');
$this->addBehavior('Timestamp');
$this->belongsToMany('Profiles', [
'foreignKey' => 'skill_id',
'targetForeignKey' => 'content_type_id',
'joinTable' => 'skills_map',
'through' => 'SkillsMap'
]);
}
My skills map table
public function initialize(array $config)
{
parent::initialize($config);
$this->setTable('skills_map');
$this->setDisplayField('id');
$this->setPrimaryKey('id');
$this->addBehavior('Timestamp');
$this->belongsTo('Skills');
$this->belongsTo('Profiles');
}
The edit profile form part
<div class="card card-profile-skills py-4 mt-4">
<div class="card-body">
<fieldset>
<div class="form-row">
<div class="col">
<div class="form-group">
<label for="description"><?= __('edit_profile_actual_skills_label') ?></label>
<?=
$this->Form->input('actual_skills._ids', [
'label' => false,
'multiple' => 'multiple',
'type' => 'select',
'data-js-multiple-skills-select' => '',
'data-ajax-url' => $this->Url->build(['_name' => 'get_ajax_skills_list_path'], true),
'empty' => 'placeholder',
'options' => $selectedActualSkills,
'default' => array_keys($selectedActualSkills),
]);
?>
</div>
</div>
</div>
<div class="form-row mt-4">
<div class="col">
<div class="form-group">
<label for="description"><?= __('edit_profile_desired_skills_label') ?></label>
<?=
$this->Form->input('desired_skills._ids', [
'label' => false,
'multiple' => 'multiple',
'type' => 'select',
'data-js-multiple-skills-select' => '',
'data-ajax-url' => $this->Url->build(['_name' => 'get_ajax_skills_list_path'], true),
'empty' => 'placeholder',
'options' => $selectedDesiredSkills,
'default' => array_keys($selectedDesiredSkills),
]);
?>
</div>
</div>
</div>
</fieldset>
</div>
And my profile controller edit method
public function edit($hash = null)
{
$profile = $this->Profiles->findByHash($hash)->contain([
'ActualSkills' => function ($q) {
return $q;
//return $q->where(['Profiles.is_published' => true]); /* @ todo: finish request to fit with context skills */
},
'DesiredSkills' => function ($q) {
return $q;
//return $q->where(['Profiles.is_published' => true]); /* @ todo: finish request to fit with context skills */
}
])->formatResults(function($results){
return $results->map(function($row){
$row->data = json_decode($row->data);
return $row;
});
})->first();
$redirect = ['_name' => 'home'];
$associated = [
'associated' => [
'ActualSkills',
'ActualSkills._joinData',
'DesiredSkills',
'DesiredSkills._joinData',
]
];
if (!$profile || !$profile->isProfileOwner($this->_currentUser)) {
$this->Flash->error(__('Retourner une 404'));
}
$selectedActualSkills = $this->Util->generateList($profile->actual_skills, ['key' => 'id', 'value' => 'name']);
$selectedDesiredSkills = $this->Util->generateList($profile->desired_skills, ['key' => 'id', 'value' => 'name']);
if ($this->request->is(['patch', 'post', 'put'])) {
$profile = $this->Profiles->patchEntity($profile, $this->request->getData(), $associated);
$profile->set('slug');
if ($this->Profiles->save($profile, $associated)) {
/* <BEGIN> Update Pivot table */
if($profile->actual_skills)
{
foreach($profile->actual_skills as $skill)
{
$pivotRow = $this->Profiles->SkillsMap->findBySkillId($skill->id)->where([
'content_type_id' => $profile->id
])->first();
$pivotRow->content_type = $this->name . '.ActualSkills' ;
$this->Profiles->SkillsMap->save($pivotRow);
}
}
/* <END> Update Pivot table */
if (isset($profile->save_btn)) {
$redirect = ['_name' => 'my_profile_path', 'hash' => $profile->hash];
}
$this->Flash->success(__('edit_profile_success'));
return $this->redirect($redirect);
}
$this->Flash->error(__('edit_profile_error'));
}
$this->set(compact('profile', 'selectedActualSkills', 'selectedDesiredSkills'));
}
So, my column content_type needs to be populated when saving. I would like to use the _joinData but I read in Cake book that will not work.
I say before I put conditions in my table links (content_type to describe context, from profile or course form), for querying, and for exemple to retrieve the selected options of my skills fieds. But if my data are not saved when I edit form, my query cannot works.
I do not want to create three tables for skills, any possibily to achieve that with only one table ?
Thanks for your help ! 
Laurent.