I'm working with Magento version 1.4 and I added extra grid columns (names and skus) to Sales Order Grid, the returned data is correct but I'm having problems with pagination and total number of records, my code as follow:
First I Edited Mage_Adminhtml_Block_Sales_Order_Grid
protected function _prepareCollection()
{
$collection = Mage::getResourceModel($this->_getCollectionClass())
->join(
'sales/order_item',
'`sales/order_item`.order_id=`main_table`.entity_id',
array(
'skus' => new Zend_Db_Expr('group_concat(`sales/order_item`.sku SEPARATOR ", ")'),
'names' => new Zend_Db_Expr('group_concat(`sales/order_item`.name SEPARATOR ", ")'),
)
);
$collection->getSelect()->group('entity_id');
$this->setCollection($collection);
return parent::_prepareCollection();
}
Then I override this method to return correct results when filter by names or skus
protected function _addColumnFilterToCollection($column)
{
if($this->getCollection() && $column->getFilter()->getValue())
{
if($column->getId() == 'skus'){
$this->getCollection()->join(
'sales/order_item',
'`sales/order_item`.order_id=`main_table`.entity_id',
array(
'skus' => new Zend_Db_Expr('group_concat(`sales/order_item`.sku SEPARATOR ", ")'),
)
)->getSelect()
->having('find_in_set(?, skus)', $column->getFilter()->getValue());
return $this;
}
if($column->getId() == 'names'){
$this->getCollection()->join(
'sales/order_item',
'`sales/order_item`.order_id=`main_table`.entity_id',
array(
'names' => new Zend_Db_Expr('group_concat(`sales/order_item`.name SEPARATOR ", ")'),
)
)->getSelect()
->having('find_in_set(?, names)', $column->getFilter()->getValue());
return $this;
}
}
return parent::_addColumnFilterToCollection($column);
}
Then I edited this method getSelectCountSql() in Mage_Sales_Model_Mysql4_Order_Collection class
public function getSelectCountSql()
{
$countSelect = parent::getSelectCountSql();
//added
$countSelect->reset(Zend_Db_Select::HAVING);
//end
$countSelect->resetJoinLeft();
return $countSelect;
}
Any Idea how can I calculate number of rows? Thanks in Advance.
Maybe its a bit to late but in your code try using GROUP insted of HAVING:
Because you are using this statemen:
I had this issue and i have got it working by implementing custom getSize() function in the collection i am using
and to achieve Grid storing i have override
and Set filter_index of the columns TO
and you can use Callback function on filters for the columns