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.