`getLastPageNumber()` returning wrong number of pages

Question

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

Answer 1

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

Answer 2

As per as query,on your collection, pager and page Size not implemented.

If you want to get page no then need to use setPageSize() to your collection for getting total page number & last page no. With out set Page size you does not get last page number.

For how to implement pager check

• excellencemagentoblog
• devinrolsen