Magento Sales Order Grid shows incorrect number of records when added Names and Skus columns

Magento Sales Order Grid shows incorrect number of records when added Names and Skus columns

July 31, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento Sales Order Grid shows incorrect number of records when added Names and Skus columns

Magento Grid Column Filtration and Pagination: A Comprehensive Guide

As an experienced developer working on Magento v1.4, you’ve likely encountered common issues when extending grid functionality. In this post, we’ll delve into the intricacies of adding new columns to the Sales Order Grid and address the challenges you’re facing with pagination and record counting.

The Current Implementation: Where Things Go Awry

You’ve taken a commendable approach by overriding the _prepareCollection() method in Mage_Adminhtml_Block_Sales_Order_Grid to join the order_item table and group data. Additionally, you’ve overrode the _addColumnFilterToCollection() method to apply filters on ‘skus’ and ‘names’. However, your modifications to getSelectCountSql() in Mage_Sales_Model_Mysql4_Order_Collection don’t fully address the counting issue.

The problem lies in the fact that Magento’s grid system relies heavily on its built-in mechanisms for counting records. By joining additional tables and applying complex filters, you’ve disrupted these mechanisms.

A Step-by-Step Solution: Restoring Grid Functionality

To resolve this, we’ll need to take a more holistic approach. Let’s break down the solution into manageable steps:

1. Maintain Separate Collections for Grid and Counting

Create two separate collections—one for displaying data in the grid (with your additional joins) and another for counting records (without the complex joins). Use the grid collection for rendering rows, and the count collection solely for calculating totals.

2. Modify the Grid Collection Join and Grouping

In your overridden _prepareCollection() method, maintain the existing join and grouping structure with group_concat(). However, instead of using this collection for counting records, simply return it after setting the grid’s collection property:

protected function _prepareCollection()
{
    // ... (existing code)

    $this->setCollection($collection);
    return parent::_prepareCollection();
}

3. Create a Separate Collection for Counting

Create a new collection that mirrors your existing one but excludes the complex joins and groupings:

public function getCountCollection()
{
    $countCollection = Mage::getResourceModel($this->_getCollectionClass())
        ->join(
            'sales/order_item',
            '`sales/order_item`.order_id=`main_table`.entity_id'
        );
    return $countCollection;
}

4. Override getSelectCountSql() for Counting

Now, in the Mage_Sales_Model_Mysql4_Order_Collection, override the getSelectCountSql() method to use your new counting collection:

public function getSelectCountSql()
{
    $countSelect = parent::getSelectCountSql();

    // Replace the main table select with our new count collection
    $countSelect->reset();
    $countSelect->from($this->getCountCollection()->getSelect());

    return $countSelect;
}

5. Update Pagination

Ensure that your grid uses the correct total record count by calling setTotalRecords() and setLastPageNumber() with appropriate values from your counting collection:

public function setCollection($collection)
{
    // ... (existing code)

    $totalRecords = $this->getCountCollection()->getSize();
    $lastPageNumber = ceil($totalRecords / ($this->getVar('limit') || 25));

    $this->setTotalRecords($totalRecords);
    $this->setLastPageNumber($lastPageNumber);

    return parent::setCollection($collection);
}

Summary and Next Steps

In this post, we’ve explored the complexities of extending Magento’s grid functionality, particularly when joining additional tables for complex filters. By maintaining separate collections for data display and record counting, we can ensure that our grids function correctly while allowing for advanced filtering options.

Remember to test your changes thoroughly, especially in scenarios involving extensive product catalogs or large numbers of orders. Always consult the official Magento documentation for the most up-to-date information on core class modifications.

For more insights into Magento development best practices and advanced customization techniques, consider exploring our comprehensive guides at Laravel Company.

Database API Admin Orders

Our network