Magento – Set Page Size on Collection for In-Stock Items Only

collection;product-collectionstock

how do i load a collection in magento that is limited to 10 products but only includes products that are in stock?

what i tried

$productCollection = Mage::getModel('catalog/product')
            ->getCollection()
            ->setPageSize(10);

 Mage::getSingleton('cataloginventory/stock')
       ->addInStockFilterToCollection($productCollection);
 // problem with this method - if one of the ten items are not in stock my
 // collection only has 9 items

problem with this method – if one of the ten items are not in stock my collection only has 9 items

$collection = Mage::getModel('catalog/product')
 ->getCollection()
 ->addAttributeToSelect('*')
 ->joinField('qty',
             'cataloginventory/stock_item',
             'qty',
             'product_id=entity_id',
             '{{table}}.stock_id=1',
             'left')
 ->addAttributeToFilter('qty', array("gt" => 0));
// problem with this method - configurable parent products don't have stock 
// (only the children do), this causes the parents not to show

problem with this method – configurable parent products don't have stock (only the children do), this causes the parents not to show

Any suggestions?

Solution

Here is a more fleshed out version of the correct answer below:

$collection = Mage::getModel('catalog/product')
     ->getCollection()
     ->addAttributeToSelect('*')
     ->joinField('is_in_stock',
            'cataloginventory/stock_item',
            'is_in_stock',
            'product_id=entity_id',
            '{{table}}.stock_id=1',
            'left')
        ->addAttributeToFilter('is_in_stock', array("eq" => 1))
        ->setPageSize(10);

Best Answer

is_in_stock should probably used to determined if something is in stock, instead of the qty you're using in the join. See below method for Magento's approach.

See Mage_CatalogInventory_Model_Resource_Stock::setInStockFilterToCollection for details.

Related Solutions

Magento 1.9 Product Collection – Incorrect Pricing Data or Not Showing Out of Stock Items

addFinalPrice() also calls applyProductLimitationFilters. This method applies several filters according to how the collection is configured, like the store filter which only shows the products that are visible in the current store.

If you want to load all products, visible or not, like without the price filter, you can remove the store filter like this, before calling addFinalPrice():

$collection->setStoreId(null);

Magento – Why does the Product\Collection not respect the page-number and page-size attributes

Take a look at the definition of getSize (it's in \Magento\Framework\Data\Collection\AbstractDb). You'll see the following:

public function getSize()
{
    if ($this->_totalRecords === null) {
        $sql = $this->getSelectCountSql();
        $this->_totalRecords = $this->getConnection()->fetchOne($sql, $this->_bindParams);
    }
    return intval($this->_totalRecords);
}

The method getSelectCountSql does exist in the original class, but if you follow it down:

public function getSelectCountSql()
{
    return $this->_getSelectCountSql();
}

To:

protected function _getSelectCountSql($select = null, $resetLeftJoins = true)
{
    $this->_renderFilters();
    $countSelect = is_null($select) ? $this->_getClearSelect() : $this->_buildClearSelect($select);
    $countSelect->columns('COUNT(DISTINCT e.entity_id)');
    if ($resetLeftJoins) {
        $countSelect->resetJoinLeft();
    }
    return $countSelect;
}

To:

protected function _buildClearSelect($select = null)
{
    if (null === $select) {
        $select = clone $this->getSelect();
    }
    $select->reset(\Magento\Framework\DB\Select::ORDER);
    $select->reset(\Magento\Framework\DB\Select::LIMIT_COUNT);
    $select->reset(\Magento\Framework\DB\Select::LIMIT_OFFSET);
    $select->reset(\Magento\Framework\DB\Select::COLUMNS);

    return $select;
}

You'll see that getSize will always reset the LIMIT and perform a MySQL query to give you the size of the full collection (assuming I didn't miss somewhere that sets _totalRecords outside of that method). Given that you've already loaded the collection, calling count($collection) should result in the right number.

Best Answer

Related Solutions

Magento 1.9 Product Collection – Incorrect Pricing Data or Not Showing Out of Stock Items

Magento – Why does the Product\Collection not respect the page-number and page-size attributes

Related Topic