CODEWITHSUNDEEP
phpmysqlsql-updatesilverstripe
sabanana
sabanana

Reputation: 43

Silverstripe SQLUpdate multiple rows

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!

Upvotes: 2

Views: 346

Answers (1)

bummzack
bummzack

Reputation: 5875

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.

Upvotes: 2

Related Questions