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.

score 123 · Accepted Answer · edited Dec 08 '15 at 15:21

123

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.

edited Dec 08 '15 at 15:21

Petter Friberg

21,252
9
60
109

answered Jan 14 '13 at 17:01

freento

2,939
4
29
53

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. – Shadowbob Jan 14 '13 at 17:08
1

$collection->getSelect()->limit(20, 20); //LIMIT 20, 20 OR $collection = Mage::getModel('...')->getCollection()->setPageSize(20) ->setCurPage(2); //LIMIT 20, 20 – freento Jan 14 '13 at 17:11
1

$collection->getSelect()->limit(20, 20); will work for every collection. See Zend DB Select, on which Magento queries to DB are based. – freento Jan 14 '13 at 19:45
Oh sorry I was not calling the getSelect(). This works as well, and maybe it's better to use that. – Shadowbob Jan 15 '13 at 09:33
@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 – zamil Nov 14 '13 at 06:22
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)`. – Tyler V. Jan 17 '15 at 04:29
I want to get all products according to added filters without limit in Magento 2 – Ajay Patidar Jan 08 '20 at 12:16

score 13 · Answer 2 · answered Jan 14 '13 at 16:21

13

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.

answered Jan 14 '13 at 16:21

Shadowbob

1,402
2
16
24

or just `$collection->setPage($pageNum, $pageSize);` – Pavel Novitsky Jan 14 '13 at 16:57
1

+Pavel no setPage is only for Catalog and I needed outside of Catalog like I explained in the question. – Shadowbob Jan 14 '13 at 17:05

Israt Jahan Simu · Answer 3 · 2019-11-12T04:58:59.423

5

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;

edited Nov 12 '19 at 04:58

answered May 26 '16 at 07:38

Israt Jahan Simu

1,040
13
7

2

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! – Rico Neitzel Jun 23 '17 at 06:27
hmm! querying in a loop – itsazzad Oct 22 '17 at 11:22

score 1 · Answer 4 · answered Jan 20 '17 at 06:35

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);

Magento: Set LIMIT on collection

4 Answers4

Linked