Limiting Collection Size in Magento

Sometimes you need to get a collection of objects from Magento but only want to limit them to a certain number. This is especially useful if your collection is going to be in the hundreds of thousands – as you do not want to be performing complex operations on a collection of that size.

Some of the best ways to limit your collection size are:

setPageSize()

This function will limit the number of results that are returned – basically using a LIMIT X mysql query. For example:

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->setPageSize(10);
echo $collection->count(); // will echo 10

setPage()

setPage() is similar to using setPageSize but you can add two parameters – just like you would with mysql LIMIT 10,5 for example. The first parameter being the start from point – the next being how many you wish to bring back:

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->setPage(0,20); // will bring back the first 20 products
$collection->setPage(10,5); // will bring back 5 products after the 10th product

getSelect()->limit()

This function allows you to add your own limit to the select query – its basically the same as entering LIMIT X. For example:

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->getSelect()->limit(20); // will bring back our first 20 results

A handy hint to see what these limits are doing to your mysql query is to echo out your mysql query in the code. To do this you can simply perform the following after you’ve tweaked your $collection object:

echo $collection->load()->getSelect(); // will echo out our mysql query