Grid pagination not working when use group clause in collection

Grid pagination not working when use group clause in collection

October 19, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Grid pagination not working when use group clause in collection

Resolving Grid Pagination Issues with Group Clauses in Collections

When working with Magento product grids, you may encounter pagination or product count issues due to the use of group clauses in your collection setup. This issue arises because the default count functionality in Zend Framework (which is used by Magento) does not handle grouped collections correctly.

Let’s dive into the root cause and provide a step-by-step solution for resolving this problem.

Understanding the Issue

The core of the problem lies in how Magento handles product listing with filters and pagination. When you add a group clause to your collection, it prevents duplicate results but breaks the default pagination and count functions provided by Zend Framework’s Zend_Db_Select.

Here’s what happens behind the scenes:

  1. The group clause is used to prevent errors when multiple products share the same ID (which can occur with category filters).
  2. This grouping impacts the selection of rows, causing the standard COUNT(*) function to return incorrect product counts.
  3. As a result, pagination and per-page limits are calculated incorrectly, leading to wrong count displays and broken pagination.

Solving the Problem: A Practical Approach

To resolve this issue without modifying core files, you can create an observer that intercepts and modifies the collection before it’s used for grid rendering. This approach allows you to maintain the grouping functionality while ensuring correct pagination and counting.

Here’s a step-by-step guide:

  1. Create a new module (if you don’t have one already) and declare your observers in the etc/config.xml file:
<config xmlns:xs="http://www.w3.org/2001/XMLSchema-instance" xs:noNamespaceSchemaLocation="urn:magento:framework:app/etc/config.xsd">
    <type name="Magento\Framework\View\Element\Template">
        <arguments>
            <argument name="templateFileResolver" xsi:type="object">Vendor\Module\Model\Plugin\TemplateFileResolver</argument>
        </arguments>
    </type>
</config>
  1. Create the observer class (e.g., Vendor\Module\Model\Observer) and define your collection modification logic:
public function interceptCollection(Observer $observer)
{
    $block = $observer->getBlock();
    if ($block instanceof \Magento\Catalog\Block\Product\ListProduct) {
        $collection = $block->getCollection();
        $select = $collection->getSelect();

        // Check if the collection has a group clause
        if (count($select->getPart(Zend_Db_Select::GROUP)) > 0) {
            // Remove the existing group and distinct clauses
            $select->reset(Zend_Db_Select::GROUP);
            $select->distinct(false);

            // Add a new distinct count based on grouped columns
            $groupColumns = implode(", ", $select->getPart(Zend_Db_Select::GROUP));
            $select->columns("COUNT(DISTINCT {$groupColumns})");
        }
    }

    return $observer;
}
  1. Define the observer definition in your module’s di.xml file:
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Catalog\Block\Product\ListProduct">
        <plugin name="vendor_module_intercept_collection" type="Vendor\Module\Model\Observer"/>
    </type>
</config>

Conclusion

In this post, we’ve explored the challenges of working with grouped collections in Magento product grids and provided a practical solution using event observers.

By intercepting the collection before it’s used for grid rendering, you can maintain grouping functionality while ensuring correct pagination and counting. This approach avoids the need to modify core files, making your solution more robust and easier to maintain.

Remember to replace Vendor with your actual vendor name throughout the code examples provided above.

For further reading on Magento event system and observer best practices, I recommend checking out this comprehensive guide on Event Observers in Magento.

Happy coding!

Catalog Extensions Admin Errors

Our network