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',

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

  • did you try getLastPageNum(); Nov 4, 2015 at 7:45
  • can you please show query of collection by $collection->getSelect()->__toString();
    – Amit Bera
    Nov 4, 2015 at 8:09
  • @AmitBera I added the collection in the question.
    – Pat
    Nov 4, 2015 at 8:59

2 Answers 2


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)
    $countSelect = (is_null($select)) ?
        $this->_getClearSelect() :
    $countSelect->columns('COUNT(DISTINCT e.entity_id)');
    if ($resetLeftJoins) {
    return $countSelect;

And I assume your problem is:

    if ($resetLeftJoins) { // true by default

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

  • I'm using setPageSize() and it's returning the products and pages correctly. (It stops showing products after page 11) But the page number is still returning wrong and saying there are 33 pages..
    – Pat
    Nov 4, 2015 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.