Magento – `getLastPageNumber()` returning wrong number of pages

collection;magento-1.9paginationproduct-collection

For some reason, using $collection->getLastPageNumber() is returning the wrong number of pages.

For example:

$collection->addFieldToFilter('visibility', Mage_Catalog_Model_Product_Visibility::VISIBILITY_NOT_VISIBLE);
$collection->getSelect()->joinLeft(array('link_table' => 'catalog_product_super_link'),'link_table.product_id = e.entity_id',
            array('product_id','parent_id')
        );
$collection->getSelect()->group('link_table.parent_id');

I'm not quite sure what the issue might be. I'm using joinLeft and group on my collection, as seen above. As far as I can tell, it's the only thing 'out of the ordinary' that I'm doing.

When I debugged the query, it returned, for example, 625 rows. But, using a pagelimit of 10, and then calling getLastPageNumber() returns a total of 325 pages, instead of 62 pages!

What might be causing such an issue?

EDIT:
The query is:

SELECT e.*, IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) AS visibility, link_table.product_id, link_table.parent_id, color_t1.attribute_id, color_t1.value, color_t1.store_id, at_stock_status.stock_status, price_index.price, price_index.tax_class_id, price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price, price_index.tier_price FROM catalog_product_entity AS e
INNER JOIN catalog_product_entity_int AS at_visibility_default ON (at_visibility_default.entity_id = e.entity_id) AND (at_visibility_default.attribute_id = '102') AND at_visibility_default.store_id = 0
LEFT JOIN catalog_product_entity_int AS at_visibility ON (at_visibility.entity_id = e.entity_id) AND (at_visibility.attribute_id = '102') AND (at_visibility.store_id = 1)
LEFT JOIN catalog_product_super_link AS link_table ON link_table.product_id = e.entity_id
LEFT JOIN catalog_product_entity_int AS color_t1 ON color_t1.entity_id = e.entity_id AND color_t1.store_id = 0 AND color_t1.attribute_id = 92
LEFT JOIN cataloginventory_stock_status AS at_stock_status ON (at_stock_status.product_id=e.entity_id) AND (at_stock_status.stock_id=1)
INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = '1' WHERE (IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) = '1') GROUP BY link_table.parent_id,
color_t1.value

Best Answer

You are directly operating on Zend_Db_Select which is always a bad idea.

The thing here is, that getLastPageNumber is using getSize, which is \Varien_Data_Collection_Db::getSize and uses \Varien_Data_Collection_Db::getSelectCountSql which is overwritten here \Mage_Catalog_Model_Resource_Product_Collection::getSelectCountSql and implemented with \Mage_Catalog_Model_Resource_Product_Collection::_getSelectCountSql.

Digging deeper in this methods shows:

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

And I assume your problem is:

    if ($resetLeftJoins) { // true by default
        $countSelect->resetJoinLeft();
    }
Related Topic