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.

Coarsegrained

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) – Fledgy

Answer 1

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

Ferminafermion

Those are good tips. Unfortunately, speed was an issue. So I just created custom queries and added the "on duplicate key" statements – Coarsegrained

Sadly does cake really force you into have two queries for a single insert? – Weightless

@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? – Ferminafermion

Answer 2

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);
}

Alroi