Use 'on duplicate key update' when saving record in CakePHP

Question

My model has 2 unique indexes. The primary key, and an alphanumeric id.

I only have the alphanumeric id when updating said record, so I need to add an "on duplicate key update" statement to the save function, but how do I do that?

And I do not want to query for the primary key first, as that would make the import process incredibly long and slow.

You are trying to apply standard dev to a framework which won't work. You'd be better off including the id's in your form. Also check out the 'keepExisting' setting in your model relationships. If all else fails, shoehorn it with `$this->Model->query($mySQL)` — David Yell, Nov 15 '12 at 11:31

score 3 · Answer 1 · answered Jul 24 '14 at 08:31

Another option is to override Model->exists() in your models. This is actually the same as Cake does it, but extended to other keys not only primaryKey.

/**
 * Overrides Model->exists() method so we can check for uniqueness if primary key is not set
 *
 * If a record already exists, sets the primary key of that record to do an update
 *
 * @param int $id
 * @return bool True if the record exists, false otherwise
 */
public function exists($id = null)
{
    if (empty($id) && !$this->getID() && isset($this->data[$this->alias]) && is_array($this->data[$this->alias])) {
        // given $id and primary key are empty, try with data
        $exists = $this->find('first', array(
            'fields' => array($this->primaryKey),
            'conditions' => array(
                'key1'=>$this->data[$this->alias]['key1'],
                'key2'=>$this->data[$this->alias]['key2'],
            ),
            'recursive' => -1,
            'callbacks' => false,
        ));
        if ($exists) {
            $this->set($this->primaryKey, $exists[$this->alias][$this->primaryKey]);
            return true;
        }
    }
    return parent::exists($id);
}

score 2 · Accepted Answer · edited May 23 '17 at 12:33

2

There is no supported 'on duplicate key update' options in cakephp.

If you really do not want to do the lookup, I would suggest changing the alphanumeric id to the primary key (as long as it is something like a UUID and not a varchar). If that is not an option, my best advice is to add a beforeSave method that does the integrity check.

Here is a link that can help Cakephp check if record exists

edited May 23 '17 at 12:33

Community

1
1

answered Jan 16 '13 at 20:29

Chuck Burgess

11,600
5
41
74

1

Those are good tips. Unfortunately, speed was an issue. So I just created custom queries and added the "on duplicate key" statements – Jelle De Loecker Feb 15 '13 at 19:47
Sadly does cake really force you into have two queries for a single insert? – Jonathan Aug 07 '14 at 06:47
@xcy7e Technically you can add a statement like `ON DUPLICATE KEY UPDATE...`, but no, CakePHP does not implicitly handle this in the ORM that I know of. Is there someone else who can verify this? – Chuck Burgess Aug 07 '14 at 20:36

Use 'on duplicate key update' when saving record in CakePHP

2 Answers2