RFE: ActiveRecord::upsert

[dicrtarasov]

\yii\db\Command has very usefull method "upsert", but ActiceRecord has no same feature.

In some situation, when additing full set of attributes in model, for example upon importing data, the typical scenario is not suitable:

$record = Record::findOne([....]);
if (empty($record)) {
    $record = new Record();
}
$record->field1 = $val1;
$record->field2 = $val2;
$record->save();

Because this method requires a lot of extra SELECT to work. It will be good to have upsert method:

/**
 * Upsert (INSERT on duplicate keys UPDATE)
 *
 * @param boolean $runValidation
 * @param array $attributes
 * @return boolean
 */
public function upsert($runValidation = true, $attributes = null)
{
    if ($runValidation) {
        // reset isNewRecord to pass "unique" attribute validator because of upsert
        $this->setIsNewRecord(false);
        if (!$this->validate($attributes)) {
            \Yii::info('Model not inserted due to validation error.', __METHOD__);
            return false;
        }
    }

    if (!$this->isTransactional(self::OP_INSERT)) {
        return $this->upsertInternal($attributes);
    }

    $transaction = static::getDb()->beginTransaction();
    try {
        $result = $this->upsertInternal($attributes);
        if ($result === false) {
            $transaction->rollBack();
        } else {
            $transaction->commit();
        }

        return $result;
    } catch (\Exception $e) {
        $transaction->rollBack();
        throw $e;
    } catch (\Throwable $e) {
        $transaction->rollBack();
        throw $e;
    }
}

/**
 * Insert or update record.
 *
 * @param array $attributes
 * @return boolean
 */
protected function upsertInternal($attributes = null)
{
    if (!$this->beforeSave(true)) {
        return false;
    }

    // attributes for INSERT
    $insertValues = $this->getAttributes($attributes);

    // attributes for UPDATE exclude primaryKey
    $updateValues = array_slice($insertValues, 0);
    foreach (static::getDb()->getTableSchema(static::tableName())->primaryKey as $key) {
        unset($updateValues[$key]);
    }

    // process update/insert
    if (static::getDb()->createCommand()->upsert(static::tableName(), $insertValues, $updateValues ?: false)->execute() === false) {
        return false;
    }

    // set isNewRecord as false
    $this->setOldAttributes($insertValues);

    // call handlers
    $this->afterSave(true, array_fill_keys(array_keys($insertValues), null));

    return true;
}

What steps will reproduce the problem?

no problems, just a feature request :)

[samdark]

Since 2.0 isn't accepting enhancements anymore, moved to Yii 3. If we'll decide to implement Active Record, we'll consider it.

[omsi668]

Hello,

My take on that since I just had the issue : I think save() should do an upsert by default, and that we shouldn't have another method for that.

If the primary key is specified on the object, Active Record should be able to understand it's an upsert, if it doesn't, it's a classic insert();

Thank you

[samdark]

@omsi668 that won't always work. Especially if the primary key is not auto-generated.

[omsi668]

Well that's why it should depend on weather you declare the primary key or not in the code?

[samdark]

You're right. It should not.

[Tigrov]

This should work not only for the primary key, but for any unique key.