How to paginate a findAll() query?

Yii 1.1 General Discussion
1

Hello there!

Im having trouble setting this up, how do i effectively set a pagination in a findAll() query?

Currently, i have a single query which can have thousands of results and im hitting the limit if that quantity is reached, yet i still need to get all rows from the database to do an operation with them.
How do i set a pagination limit so it does whatever it needs to do (be it an INSERT, UPDATE or smth) in a set limit? (eg: insert 100 rows at time from 1000 using findAll results, until all 1000 rows have been inserted)

2

Hello.
You can use count() first:
query builder: count()
then limit() and offset() in your select queries:
query builder: limit() and offset()
This way you can easily cut whole job into batches choosing batch size whatever you want.

3

That worked! Thanks a lot!

4

Example: process 100 rows at a time


$limit = 100;
$offset = 0;

while (true) {

    $models = YourModel::model()->findAll([
        'limit' => $limit,
        'offset' => $offset,
        'order' => 'id ASC', // VERY IMPORTANT
    ]);

    if (empty($models)) {
        break;
    }

    foreach ($models as $model) {
        // do your insert/update logic here
        // Example:
        // $new = new AnotherModel();
        // $new->name = $model->name;
        // $new->save();
    }

    $offset += $limit;
}
5

I’d avoid loading all the rows with one call. Fetch them in batches, such as 100 at a time, process that batch, then move to the next one until there are no results left. This should also keep memory usage much lower.