Bulk update [FR]

[i-panov]

MySQL has this syntax for bulk updating records:

update mytable 
set myfield = (case id 
	when 1 then 'value1' 
	when 2 then 'value2'
)
where id in (1, 2)

I would like the framework to have a wrapper for this.
Something like that: MyModel::bulkUpdate($updateField, $conditionField, $conditionValues).
And use: MyModel::bulkUpdate('myfield', 'id', [1 => 'value1', 2 => 'value2']).

Sample implementation:

public static function bulkUpdate($conditionField, $updateFields, $map) {
	if (empty($map))
		return 0;

	$conditionValues = array_keys($map);

	$buildValueExpression = function($conditionValues, $updateValues) use($conditionField) {
		$whenConditions = implode(' ', array_map(function($key, $value) { return "when $key then '$value'"; }, $conditionValues, $updateValues));
		return new Expression("(case $conditionField " . $whenConditions . ' end)');
	};

	if (is_array($updateFields)) { # one field in string or many fields in array
		if (empty($updateFields))
			return 0;

		$attributes = [];
		$isAssociative = ArrayHelper::isAssociative(current($map)); # values in map can be indexed by keys or numbers

		foreach ($updateFields as $index => $field) {
			$updateValues = ArrayHelper::getColumn($map, $isAssociative ? $field : $index);
			$attributes[$field] = $buildValueExpression($conditionValues, $updateValues);
		}
	} else {
		$attributes = [$updateFields => $buildValueExpression($conditionValues, $map)];
	}

	return static::updateAll($attributes, [$conditionField => $conditionValues]);
}

[samdark]

Is it supported by PostgreSQL, MSSQL, Oracle and SQLite?

[i-panov]

@samdark Yes, but a slightly different syntax. We can create multiple case expression builders for each DBMS.

[samdark]

Then it is doable and is usable. No objections having it.

[fcaldarelli]

Instead creating a new method "bulkUpdate" that also does not include support to update other fieds, what do you think to extend existent updateAll method?

Now we have:

Customer::updateAll(['status' => 1], 'status = 2');

It would be intuitive to have:

Customer::updateAll([
    'status' => 1,
    [ 'case', [ 'id = 1' => 'value1', 'id = 2' => 'value2'], 'myfield' ]
], 'status = 2');

and I'd explicit case condition instead working on a specific matching field (in this case id).

[rob006]

AFAIK you can use expression as field value in update* methods. So this is basically duplicate of https://github.com/yiisoft/yii2/issues/12781 - if we add support for case expressions, you could use it as:

Customer::updateAll(['myfield' => new CaseExpression(/* ... */)], ['id' => [1, 2]]);

[i-panov]

@rob006 Well, maybe this will be the solution, but I see the last message in that topic dated 2018... When can we expect the implementation of this feature?

[samdark]

@i-panov forever till someone will find it important enough and contribute it.