Yii2: Add limit and offset support to ActiveRecord::updateAll().

Created on 6 Mar 2015 · 8Comments · Source: yiisoft/yii2

Can we add limit and offset support to method ActiveRecord::updateAll()?

I'm trying to use this method to update the table of an AR. However, besides the condition, there are too many rows to update. I want current process to update the table with condition like ['someCol' => null] AND only 500 rows each time. Then I can leave the rest rows to other processes.

Please let me know if this is possible or I'll have to use QueryBuilder to perform this operation.

Thanks!

enhancement

Source

thinker-g

Most helpful comment

you may use it:

foreach (User::find()->batch(500) as $users) {
    $userIds = ArrayHelper::getColumn($users, 'id');
    User::updateAll(['status' => 2], ['id' => $userIds]);
}

zelenin on 7 Mar 2015

👍2

All 8 comments

I was using this extension as application component on Yii1.1 http://www.yiiframework.com/extension/activerecorditerator/ to help processing large amount of data, that was quite easy

udivankin on 7 Mar 2015

you may use it:

foreach (User::find()->batch(500) as $users) {
    $userIds = ArrayHelper::getColumn($users, 'id');
    User::updateAll(['status' => 2], ['id' => $userIds]);
}

zelenin on 7 Mar 2015

👍2

Hi zelenin,

Thanks for your suggestion. But that cannot solve my problem I think.
The situation is that I'm making a multi-process program. I'll start a new process of this program every 10 seconds, so they can process entries in database table parallelly. But the problem is to prevent one entry to be read by 2 (or more) processes at same time. So I'll be needing to send an update with a unique token of current process at the very first step , then the updated rows can be logically considered as "locked" by current process, and won't be modified by other processes.

The SQL statements would like following.
UPDATE data SET taken_by='ded2a32aef34' WHERE token_by IS NULL LIMIT 500;
WHILE LOOP to update one by one:
SELECT * FROM data WHERE taken_by='ded2a32aef34';
.... Some other process;
WHILE LOOP ENDS;

I think maybe one problem of implementing this in updateALL is that ORACLE doesn't have native support for LIMIT keyword.

Current I have to use query builder to do this, combining sql manually. Do you see any work around of this, maybe I missed something?

Regards,

thinker-g on 8 Mar 2015

Hi udivankin,

Thanks for the answer!
The issue is not on reading large amount of data. It's about a LOCK & UPDATE mechanism. Please refer to my comment above for details.

Regards,

thinker-g on 8 Mar 2015

+1 for ability to update only 1 record (LOCK)