Automatic insert or update

Hello. I created model with all necessary properties and relations. I also added such code to my model:

protected function beforeSave() {

        if (empty($this->id) && $existingModel = self::findFirst(array('title' => $this->title))) {

            $this->id = $existingModel->id;

        }

    }

Logic should be: if model with such title exists - update it else - insert new. But I'm receiving Exception: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY'

Can anybody help me fix this issue?



46.9k

It is known as an "upsert" and its non-standard SQL and not supported by PHQL. Keep in mind that this is not going to be automatically portable across databbases but I do something like this:

    /**
     *
     */
    public final function save($data = null, $whiteList = null)
    {
        return false;
    }

    /**
     *
     */
    public final function create($data = null, $whiteList = null)
    {
        return false;
    }

    /**
     *
     */
    public final function update($data = null, $whiteList = null)
    {
        return false;
    }

    /**
     *
     */
    public function upsert()
    {
        if ($this->fireEvent('beforeValidation') === false) {
            return false;
        } else if ($this->fireEvent('afterValidation') === false) {
            return false;
        } else if ($this->fireEvent('beforeSave') === false) {
            return false;
        }

        $sql = 'INSERT INTO ' . $this->getSource() . ' (id, recordsId, mode, property, value) VALUES';
        $sql .= " (:id, :recordsId, :mode, :property, :value)";
        $sql .= " ON DUPLICATE KEY UPDATE value = :value";

        $success = $this->getWriteConnection()
            ->getInternalHandler()
            ->prepare($sql)
            ->execute([
                'id'        => $this->id,
                'recordsId' => $this->recordsId,
                'mode'      => $this->mode,
                'property'  => $this->property,
                'value'     => $this->value
            ]);

        if ($success) {
            $this->fireEvent('afterSave');
        }

        return $success;
    }