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

collection;magento2product-collection

I'm trying to get a list of all the products in the store using the product-collections but it doesn't respect the page and page-size params. I'm using the sample data and have 174 products in my store but irrespective of the page number and size supplied when querying the collection:

/** @var Store $store */
$store = $this->_storeManager->getStore(true);
/** @var \Magento\Catalog\Model\ResourceModel\Product\Collection $collection */
$collection = $this->getCollection($store);
$collection = $collection->addAttributeToSelect('*');

$collection = $collection->setCurPage(100);
$collection = $collection->setPageSize(1000);
$collection = $collection->load();

var_dump($collection->getSize());
//var_dump($collection->count());
//var_dump(count($collection->getData()));

I've tried to output the size of the collection after querying it but the number of results are incorrect.

Best Answer

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.

Related Topic