Magento: Set LIMIT on collection

Question

The question that I tried to find out was how do we set a Limit on a Collection, the answers that I found on Google was only available for the Catalog with a setPage($pageNum, $pageSize). That didn't work on any other collections.
See the answer below.

Solution 1

There are several ways to do this:

$collection = Mage::getModel('...')
            ->getCollection()
            ->setPageSize(20)
            ->setCurPage(1);

Will get first 20 records.

Here is the alternative and maybe more readable way:

$collection = Mage::getModel('...')->getCollection();
$collection->getSelect()->limit(20);

This will call Zend Db limit. You can set offset as second parameter.

Solution 2

The way to do was looking at the code in code/core/Mage/Catalog/Model/Resource/Category/Flat/Collection.php at line 380 in Magento 1.7.2 on the function setPage($pageNum, $pageSize)

 $collection = Mage::getModel('model')
     ->getCollection()
     ->setCurPage(2) // 2nd page
     ->setPageSize(10); // 10 elements per pages

I hope this will help someone.

Solution 3

Order Collection Limit :

$orderCollection = Mage::getResourceModel('sales/order_collection'); 
$orderCollection->getSelect()->limit(10);

foreach ($orderCollection->getItems() as $order) :
   $orderModel = Mage::getModel('sales/order');
   $order =   $orderModel->load($order['entity_id']);
   echo $order->getId().'<br>'; 
endforeach; 

Solution 4

You can Implement this also:- setPage(1, n); where, n = any number.

$products = Mage::getResourceModel('catalog/product_collection')
                ->addAttributeToSelect('*')
                ->addAttributeToSelect(array('name', 'price', 'small_image'))
                ->addFieldToFilter('visibility', Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH) //visible only catalog & searchable product
                ->addAttributeToFilter('status', 1) // enabled
                ->setStoreId($storeId)
                ->setOrder('created_at', 'desc')
                ->setPage(1, 6);

View more solutions

Comments

[Pavel Novitsky]

or just $collection->setPage($pageNum, $pageSize);

[Shadowbob]

+Pavel no setPage is only for Catalog and I needed outside of Catalog like I explained in the question.

[Shadowbob]

limit is only for Catalog as well, if you do your own module you will have to implement that in your collection, and limit(20) = LIMIT 0, 20 and not LIMIT 20, 20 how do you change page ? The first solution is the one I put below.

[freento]

$collection->getSelect()->limit(20, 20); //LIMIT 20, 20 OR $collection = Mage::getModel('...')->getCollection()->setPageSize(20) ->setCurPage(2); //LIMIT 20, 20

[freento]

$collection->getSelect()->limit(20, 20); will work for every collection. See Zend DB Select, on which Magento queries to DB are based.

[Shadowbob]

Oh sorry I was not calling the getSelect(). This works as well, and maybe it's better to use that.

[zamil]

@Shadowbob: searching for the same i reached here. but I am trying to call $collection->getSelect()->limit(20); from my custom module that overrides this core block method _getProductCollection() . But it throws an error like this Unrecognized method 'setCurPage()' Any idea what i am doing wrong

[Tyler V.]

Another note for people using setPageSize(20). This is a Varien construct, not part of Zend_DB, and doesn't appear to add LIMIT 20 to the MySQL query. Therefore, if you're trying to limit to amount of data returned for speed, you're better off using limit(20).

[Rico Neitzel]

Your example is working but never ever use a foreach to load single entities from a collection. that's what a collection is used for! So better remove the first two lines in your foreach … it'll just work but is much faster and more scalable!

[itsazzad]

hmm! querying in a loop

[Ajay Patidar]

I want to get all products according to added filters without limit in Magento 2