I want to update the data in the table via Actite Record . How to write a simple SQL query I know, the task is to use AR. We update one field; in the other, we calculate a new value. The problem is exactly how to describe the expression [current value + some number].

// $oldID - старый ID группы // $newID - новый ID группы // $delta - некое число, которое нужно прибавить к значению поля in_group // Код, который не работает Item::updateAll([ 'id_group' => $newID, 'in_group' => '[[in_group]] + $delta', ], ['=', 'id_group', $oldID]);

The updateAllCounters () and updateAll () methods will not work separately due to restrictions in the database: the id_group and in_group pair is a unique key, so if you update, then at the same time.

UPD. There is an option using yii \ db \ Expression :

 // Код, который работает Item::updateAll([ 'id_group' => $newID, 'in_group' => new Expression("in_group + {$delta}"), ], ['=', 'id_group', $oldID]);
  • but he does not like me. Can I do without Expression ?

    1 answer 1

    In the absence of the Item object, in a situation when you have to use the static updateAll function updateAll your option is the only correct one.

     Item::updateAll([ 'id_group' => $newID, 'in_group' => new Expression("in_group + {$delta}"), ], ['=', 'id_group', $oldID]);

    The disadvantage of this approach is that the database structure is distributed under the project. For good you need to hide the structure of the database for ActiveRecord. Since $oldID present in the code, it is probably better to get an ActiveRecord object and work with it rather than with a simple wrapper over SQL.

    If I have a choice between the absence of an "extra" sql query to the database and the ability to work with an ActiveRecord object, I would prefer ActiveRecord. I will be engaged in optimization of only bottlenecks of the project, in other cases the beauty of the code is more important to me than performance.

    Get the Item object and change it:

     $item = Item::find()->groupId($oldID)->one(); if (!$item) { throw new Exception('Failed to get Item with old id'); } /** * Там же изменяем in_group. Или что-нибудь ещё. * Логика установки идентификатора группы всегда в одном месте. */ $item->setGroupId($newID); $item->save(); if (!$item->save()) { throw new Exception('Failed to save Item with new id'); }

    And so, the client code has ceased to know about what the database structure is there :)

    • one
      In this case, two synchronous updates will lose the delta of one of them. - etki
    • @Etki How? The setGroupId will be spelled $ this-> in_group = $ this-> in_group + $ this-> getDelta (). Or delta as a parameter. - Andrey Kolomensky
    • one
      two users come to the site, the system for both performs identical actions, reading the same value and adding a delta, and then writes it down; if the initial value is 5, and the delta for each of the requests is 3, then the final value should be 11, but instead two consecutive update requests with the value 8 will be executed. Welcome to the world with parallel execution scripts. - etki
    • @Etki Understood, thanks). That's right, my option is not applicable here. - Andrey Kolomensky