Database/ORM - jointable creates new records on update

Need Help
1

Hi,

I am creating a website where I need to use a jointable between:

  • workouts, and
  • teams
    One team can have multiple workouts.
    One workout can have multiple teams.

So I created a table teams_workouts (columns: id, team_id, workout_id, created, updated).
Problem now is that when I update a workout, it will always create a new record in the jointable (instead of updating the existing ones).How can I fix this?

atom-screenprint
atom-screenprint562×767 72.2 KB

2

and an example of how the database looks like

db-screenprint
db-screenprint763×197 23.3 KB

3

Read up on the saveStrategy option of belongsToMany associations.

4

Hi, that was the page I was following. Based on that explanation I would expect the default behaviour to ‘replace’ and not append

5

You’ll probably need to show us more code then, specifically what’s involved when the problem happens.

6

This should be the relevant code. If you need something else in order to check where the problem could be, please let me know:

WorkoutsController:

public function edit($id = null)
{
    $workout = $this->Workouts->get($id, [
        'contain' => ['Members', 'Teams'],
    ]);
    if ($this->request->is(['patch', 'post', 'put'])) {
        $workout = $this->Workouts->patchEntity($workout, $this->request->getData());
        if ($this->Workouts->save($workout)) {
            $this->Flash->success(__('The workout has been saved.'));

            return $this->redirect(['action' => 'index']);
        }
        $this->Flash->error(__('The workout could not be saved. Please, try again.'));
    }
    $captains = $this->Workouts->Captains->find('list', ['limit' => 200]);
    $routes = $this->Workouts->Routes->find('list', ['limit' => 200]);
    $seasons = $this->Workouts->Seasons->find('list', ['limit' => 200]);
    $members = $this->Workouts->Members->find('list', ['limit' => 200]);
    $teams = $this->Workouts->Teams->find('list', ['limit' => 200]);
    $this->set(compact('workout', 'captains', 'routes', 'seasons', 'members', 'teams'));
}

WorkoutsTable:

class WorkoutsTable extends Table
{
/**
* Initialize method
*
* @param array $config The configuration for the Table.
* @return void
*/
public function initialize(array $config): void
{
parent::initialize($config);

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

    $this->addBehavior('Timestamp');

    $this->belongsTo('Captains', [
        'className' => 'Members',
        'foreignKey' => 'captain_id',
    ]);
    $this->belongsTo('Routes', [
        'foreignKey' => 'route_id',
        'joinType' => 'INNER',
    ]);
    $this->belongsTo('Seasons', [
        'foreignKey' => 'season_id',
        'joinType' => 'INNER',
    ]);
    $this->belongsToMany('Members', [
        'foreignKey' => 'workout_id',
        'targetForeignKey' => 'member_id',
        'joinTable' => 'members_workouts',
    ]);
    $this->belongsToMany('Teams', [
        'foreignKey' => 'workout_id',
        'targetForeignKey' => 'team_id',
        'joinTable' => 'teams_workouts',
    ]);
}

/**
 * Default validation rules.
 *
 * @param \Cake\Validation\Validator $validator Validator instance.
 * @return \Cake\Validation\Validator
 */
public function validationDefault(Validator $validator): Validator
{
    $validator
        ->integer('id')
        ->allowEmptyString('id', null, 'create');

    $validator
        ->date('date')
        ->requirePresence('date', 'create')
        ->notEmptyDate('date');

    $validator
        ->time('time')
        ->requirePresence('time', 'create')
        ->notEmptyTime('time');

    return $validator;
}

/**
 * Returns a rules checker object that will be used for validating
 * application integrity.
 *
 * @param \Cake\ORM\RulesChecker $rules The rules object to be modified.
 * @return \Cake\ORM\RulesChecker
 */
public function buildRules(RulesChecker $rules): RulesChecker
{
    $rules->add($rules->existsIn(['captain_id'], 'Captains'));
    $rules->add($rules->existsIn(['route_id'], 'Routes'));
    $rules->add($rules->existsIn(['season_id'], 'Seasons'));

    return $rules;
}

}

The WorkoutsEntity does not contain anything specific.

7

Also which is very strange: the table has two ‘hasmany’ relations. The relation with members is updated correctly, the relation with teams not… but they are defined in exact the same way…

8

And what does your edit form look like?

9 
        <?= $this->Form->create($workout) ?>
        <fieldset>
            <legend><?= __('Edit Workout') ?></legend>
            <?php
                echo $this->Form->control('date');
                echo $this->Form->control('time');
                echo $this->Form->control('captain_id', ['options' => $captains, 'empty' => true]);
                echo $this->Form->control('route_id', ['options' => $routes]);
                echo $this->Form->control('season_id', ['options' => $seasons]);
                echo $this->Form->control('members._ids', ['options' => $members]);
                echo $this->Form->control('teams._ids', ['options' => $teams]);
            ?>
        </fieldset>
        <?= $this->Form->button(__('Submit')) ?>
        <?= $this->Form->end() ?>
10

OMG! I just found the problem.
After doublechecking my table, I saw that the columns team_id & workout_id were defined as varchar (instead of integer!).
After changing the column type to integer… everything worked correctly.
Sorry for wasting your time Zuluru!

11

Well, that’s not something I would have guessed!