Silverstripe SQLUpdate multiple rows

Question

I have the following code (edited for simplicity's sake but my goal is to update the AccountID of multiple devices) :

$devices = Device::get();
if ($devices && $devices->count())
{
    foreach ($devices as $device)
    {
        $device->AccountID = 5; 
        $device->write();
    }
}

My problem is the above code can take a lot of time before execution is done especially if there are 30 or more devices. I was thinking of just having a single query and not having to call the write() function all the time. Let's say I have the array below where ID is the ID of the device to be updated and the AccountID is the field to be updated. Is it possible to have only one query with SQLUpdate? And how?

$values = array(
    array("ID" => 1, "AccountID" => 2),
    array("ID" => 2, "AccountID" => 12),
    array("ID" => 3, "AccountID" => 22),
    array("ID" => 4, "AccountID" => 32),
)

From https://docs.silverstripe.org/en/3/developer_guides/model/sql_query/ I can see that inserting multiple rows is possible with SQLInsert but how about for SQLUpdate? If not possible, do you have any suggestions on how can I implement this without calling write() for every device?

Thanks a lot!

score 2 · Accepted Answer · edited May 23 '17 at 12:02

No, you can't update multiple rows using SQLUpdate. You could write a raw query, something similar to this answer and run it using DB::query.

Calling write on a DataObject is quite different from just performing an SQL update. write will write to the current stage (for versioning), perform validation and also trigger onBeforeWrite and onAfterWrite hooks.

If you really just need the DB update, it will be faster to run SQLUpdate queries instead of the write. So that could gain you some performance.

And for extra performance, I suggest you also use a raw SQLSelect instead of Devices::get() to get the values you need instead of running through the ORM.

be aware: `write()` also does some extra magic (calling `onBeforeWrite()` and `onAfterWrite()`)... You can bypass that if you know for sure you don't need it (e.g. importing flat data) — wmk, Apr 03 '17 at 06:10

Silverstripe SQLUpdate multiple rows

1 Answers1