Is it possible to iterate over Magento collections with pagination natively?

Question

What I mean by that is - is there a way to do:

$collection = $model->getCollection();
foreach ($collection as $item) { 
    $item->doStuff();
}

In such a way that even if the collection had 100k rows, it would only load a page of rows at a time from MySQL and magically paginate them for you behind the scenes.

From looking at Varien_Data_Collection_Db::load() it doesn't seem like it's possible, but just wanted to check. This seems like something that should be a common need.

score 21 · Accepted Answer · edited May 23 '17 at 12:37

21

You should really use

Mage::getSingleton('core/resource_iterator')

for this purpose, as it exists purely for the performance reasons you mentioned.

Otherwise, you can use a slightly less elegant solution using a loop with setPageSize - there's a good example here, https://stackoverflow.com/questions/3786826/how-to-loop-a-magento-collection

edited May 23 '17 at 12:37

Community

1

answered Apr 16 '13 at 16:07

Ben Lessani

17,630
4
44
68

2

YOU SIR, are a gentleman and a scholar. – kalenjordan Apr 16 '13 at 17:56
1

Another thing that I realized is that the core/resource_iterator solution actually doesn't paginate the mysql query. It loads the entire result set at once, but then it gives you a row at a time to deal with in your PHP code. So it does avoid memory faults within PHP, but at some point it will trigger mysql max packet sizes, if the result set is very large. I think I'm going to try and build out a nice abstract chunked resource_iterator using setPageSize() – kalenjordan Apr 26 '13 at 16:20
Yeah, I kind of omitted that in pursuit of mwoar points! Its really aimed at single product loading versus a paginated collection. But it should serve as a base to build upon. – Ben Lessani Apr 26 '13 at 16:32
I implemented a generic batched iterator that batches the queries to MySQL but also provides an individual collection item callback. Curious what you think: https://gist.github.com/kalenjordan/5483065 – kalenjordan Apr 29 '13 at 17:10
Doesn't magento iterator basically introduce an n+1 database query problem? – ahnbizcad May 02 '17 at 18:02

score 8 · Answer 2 · edited Apr 13 '17 at 12:55

I agree with Ben Lessani that you should use the core/iterator resource model to load big collections one row at a time if possible.

However, there are limitations. As explained in "addAttributeToSelect not working with core/resource_iterator?" it does not work well with EAV models if you need to include values from the attribute value tables.

And the linked example from StackOverflow is actually not that good because it repeats the same query with different LIMIT expressions. For complex queries this might be a performance problem, but even more important, you will get duplicates if new rows are added in between.

A better way to handle collections in chunks is to first load all the ids, then use these ids as a filter for the actual paged collection.

Simple example for products:

$ids = Mage::getModel('catalog/product')
    ->getCollection()
    ->getAllIds();

$page = 1;
do {
    $collection = Mage::getModel('catalog/product')
        ->getCollection()
        ->addIdFilter($ids)
        ->setPageSize(100)
        ->setCurPage($page);

    $results = $collection->load();

    // do stuff ......

    $page++;

} while ($results->count());

Damn, that the exact thing I wanted. I was getting duplicate entries some times while using setPageSize directly, this would solve that. — Shankar Thyagarajan, Jan 06 '21 at 04:34

Is it possible to iterate over Magento collections with pagination natively?

2 Answers2

Simple example for products:

Linked