In my app I need a way to update many records and change active
from 1 to 0; and then commit new data to replace what was “active” previously.
Sort of like a version system - e.g. the DB has 100 records that are active. The user makes changes to all 100 (I am using a JS spreadsheet plugin to handle data entry).
The previous 100 is must be updated to “inactive”
The new 100 is added.
It is possible the user only updates 20 of the 100; so what that means is those 20 rows become inactive, 20 new ones are added; and the 80 other rows are unaffected. (or 50, or 75…)
I’m using this code:
// set old data to be inactive
$this->MyModel->query()
->update()
->set(['active' => 0])
->where(['active' => 1, 'id IN' => $results])
->execute();
// save new data
$data = $this->MyModel->newEntities($newData);
$this->MyModel->saveMany($data);
where $results
is an array of the IDs of the records being replaced so the IN(... ... ...)
could get quite large quite quickly.
I can’t just updateAll because there is other data in the table that shouldn’t be modified (i.e. a user can only update records belonging to them.
I am struggling to explain clearly so hopefully this makes some degree of sense.
Basically -is there a better way than using IN
to update a varying amount of data?
Thanks