What I have

1. ConfigurationsTable hasMany ValuesTable

2. In return,

   • ValuesTable belongsTo ConfigurationsTable
   • ValuesTable belongsTo ParametersTable

Each value can belong to a unique combination of a configuration and a parameter. This is enforced on the database level by having a UNIQUE index on configuration_id and parameter_id.

What is the problem

I am saving a Configuration entity patched with Values.

object(App\Model\Entity\Configuration) {
    'id' => (int) 123
    'user_id' => (int) 456
    'values' => [
        (int) 0 => object(App\Model\Entity\Value) {
            'configuration_id' => (int) 2
            'parameter_id' => (int) 2
            'value' => 'hello world 2'
            '[new]' => true
            '[accessible]' => [ ]
            '[dirty]' => [ ]
            '[original]' => [ ]
            '[originalFields]' => [ ]
            '[virtual]' => [ ]
            '[hasErrors]' => false
            '[errors]' => [ ]
            '[invalid]' => [ ]
            '[repository]' => 'Values'
        },
    ]
    '[new]' => false
    // ...
}

I want CakePHP to recognise the association and either update the existing associated records or create new ones when necessary. This seems to be working (I got a new record created), but the composite UNIQUE index seems to be the problem:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-2' for key 'idx_configuration_id_parameter_id_UNIQUE'

The query that triggers the error is:

INSERT INTO values (configuration_id, parameter_id, value) VALUES (2, 2, 'hello world 2')

The code I use for saving is:

if ($this->getRequest()->is('put')) {
    $entity = $this->Configurations->patchEntity($entity, $this->getRequest()->getData(), [
        'associated' => ['Values'],
    ]);
    $this->Configurations->save($entity, [
        'associated' => ['Values'],
    ]);
    return $this->redirect(['action' => 'index']);
}

Can I have CakePHP add the ON DUPLICATE KEY UPDATE value=value clause while doing save()? The epilog() can be chained on actual queries, but I don't think it's a good idea to reconstruct the insert statement manually and from the ground up, instead of just using save()

What I have

1. ConfigurationsTable hasMany ValuesTable

2. In return,

   • ValuesTable belongsTo ConfigurationsTable
   • ValuesTable belongsTo ParametersTable

Each value can belong to a unique combination of a configuration and a parameter. This is enforced on the database level by having a UNIQUE index on configuration_id and parameter_id.

What is the problem

I am saving a Configuration entity patched with Values.

object(App\Model\Entity\Configuration) {
    'id' => (int) 123
    'user_id' => (int) 456
    'values' => [
        (int) 0 => object(App\Model\Entity\Value) {
            'configuration_id' => (int) 2
            'parameter_id' => (int) 2
            'value' => 'hello world 2'
            '[new]' => true
            '[accessible]' => [ ]
            '[dirty]' => [ ]
            '[original]' => [ ]
            '[originalFields]' => [ ]
            '[virtual]' => [ ]
            '[hasErrors]' => false
            '[errors]' => [ ]
            '[invalid]' => [ ]
            '[repository]' => 'Values'
        },
    ]
    '[new]' => false
    // ...
}

I want CakePHP to recognise the association and either update the existing associated records or create new ones when necessary. This seems to be working (I got a new record created), but the composite UNIQUE index seems to be the problem:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-2' for key 'idx_configuration_id_parameter_id_UNIQUE'

The query that triggers the error is:

INSERT INTO values (configuration_id, parameter_id, value) VALUES (2, 2, 'hello world 2')

The code I use for saving is:

if ($this->getRequest()->is('put')) {
    $entity = $this->Configurations->patchEntity($entity, $this->getRequest()->getData(), [
        'associated' => ['Values'],
    ]);
    $this->Configurations->save($entity, [
        'associated' => ['Values'],
    ]);
    return $this->redirect(['action' => 'index']);
}

Can I have CakePHP add the ON DUPLICATE KEY UPDATE value=value clause while doing save()? The epilog() can be chained on actual queries, but I don't think it's a good idea to reconstruct the insert statement manually and from the ground up, instead of just using save()

• cakephp
• cakephp-5.x

1 Answer 1

Okay, as always you find the answer as soon as you post the question.

CakePHP needs the id of associated record to understand it exists. When that happens, it updates the record instead of trying to create a new one.

So:

1. pass the id of the Value next to (already present) configuration_id, parameter_id, and value;
2. in Model/Entity/Value.php ensure $_accessible has 'id' => true.