How to multiselect and filter in magento admin grids – IN and NOT IN
Did you ever need to create grid in Magento administration that is filterable by more column’s values, i.e. something from multiselect drop-down. Or perhaps more specific, you want to export all pending orders (order status: pending, pending_payment, pending_paypal) but you aren’t satisfied with Magento implementation of selecting, searching and exporting one by one pending order status. If you’re interested how I implemented similar behavior with input text field, simulating MySQL “IN” and “NOT IN” statement, keep reading!
Let’s rewrite sales_order grid and let’s implement this functionality for columns “Purchased From (Store)” and “Status”. If you have default Magento installation with sample data (tested on MCE version >= 1420) place several orders in different stores (english, french, german) so you can test this implementation.
Note that now you’ll need to specify value (code) for those columns but…
So, let’s begin:
1. Tell Magento about our module
2. Create module config and module structure
3. Rewrite Mage_Adminhtml_Block_Sales_Order_Grid
1. Create file app/etc/modules/Inchoo_InNin.xml with a following content
<?xml version="1.0" encoding="UTF-8"?>
<config>
<modules>
<Inchoo_InNin>
<active>true</active>
<codePool>local</codePool>
</Inchoo_InNin>
</modules>
</config>2. Create file app/code/local/Inchoo/InNin/etc/config.xml with a following content
:
<?xml version="1.0"?>
<config>
<modules>
<Inchoo_InNin>
<version>1.0.0.0</version>
</Inchoo_InNin>
</modules>
<global>
<blocks>
<adminhtml>
<rewrite>
<sales_order_grid>Inchoo_InNin_Block_Adminhtml_Sales_Order_Grid</sales_order_grid>
</rewrite>
</adminhtml>
</blocks>
</global>
</config>3. Create file app/code/local/Inchoo/InNin/Block/Adminhtml/Sales/Order/Grid.php with a following content
<?php
/**
* Adminhtml sales orders grid rewrited
*
* @author Inchoo <ivan.galambos@inchoo.net>
*/
class Inchoo_InNin_Block_Adminhtml_Sales_Order_Grid extends Mage_Adminhtml_Block_Widget_Grid
{
public function __construct()
{
parent::__construct();
$this->setId('sales_order_grid');
$this->setUseAjax(true);
$this->setDefaultSort('created_at');
$this->setDefaultDir('DESC');
$this->setSaveParametersInSession(true);
}
/**
* Modify column filter if needed by custom implementation of IN() and NOT IN() MySQL statement
*
*/
protected function _addColumnFilterToCollection($column)
{
if (($column->getId() === 'store_id' || $column->getId() === 'status') && $column->getFilter()->getValue() && strpos($column->getFilter()->getValue(), ',')) {
$_inNin = explode(',', $column->getFilter()->getValue());
$inNin = array();
foreach ($_inNin as $k => $v) {
if (is_string($v) && strlen(trim($v))) {
$inNin[] = trim($v);
}
}
if (count($inNin)>1 && in_array($inNin[0], array('in', 'nin'))) {
$in = $inNin[0];
$values = array_slice($inNin, 1);
$this->getCollection()->addFieldToFilter($column->getId(), array($in => $values));
} else {
parent::_addColumnFilterToCollection($column);
}
Zend_Debug::dump((string)$this->getCollection()->getSelect(), 'Filter column step: ');
} else {
parent::_addColumnFilterToCollection($column);
}
return $this;
}
/**
* Retrieve collection class
*
* @return string
*/
protected function _getCollectionClass()
{
return 'sales/order_grid_collection';
}
protected function _prepareCollection()
{
$collection = Mage::getResourceModel($this->_getCollectionClass());
$this->setCollection($collection);
return parent::_prepareCollection();
}
protected function _prepareColumns()
{
$this->addColumn('real_order_id', array(
'header'=> Mage::helper('sales')->__('Order #'),
'width' => '80px',
'type' => 'text',
'index' => 'increment_id',
));
if (!Mage::app()->isSingleStoreMode()) {
$this->addColumn('store_id', array(
'header' => Mage::helper('sales')->__('Purchased From (Store)'),
'index' => 'store_id',
// 'type' => 'store',
// 'store_view'=> true,
// 'display_deleted' => true,
));
}
$this->addColumn('created_at', array(
'header' => Mage::helper('sales')->__('Purchased On'),
'index' => 'created_at',
'type' => 'datetime',
'width' => '100px',
));
$this->addColumn('billing_name', array(
'header' => Mage::helper('sales')->__('Bill to Name'),
'index' => 'billing_name',
));
$this->addColumn('shipping_name', array(
'header' => Mage::helper('sales')->__('Ship to Name'),
'index' => 'shipping_name',
));
$this->addColumn('base_grand_total', array(
'header' => Mage::helper('sales')->__('G.T. (Base)'),
'index' => 'base_grand_total',
'type' => 'currency',
'currency' => 'base_currency_code',
));
$this->addColumn('grand_total', array(
'header' => Mage::helper('sales')->__('G.T. (Purchased)'),
'index' => 'grand_total',
'type' => 'currency',
'currency' => 'order_currency_code',
));
$this->addColumn('status', array(
'header' => Mage::helper('sales')->__('Status'),
'index' => 'status',
// 'type' => 'options',
'width' => '100px',
// 'options' => Mage::getSingleton('sales/order_config')->getStatuses(),
));
if (Mage::getSingleton('admin/session')->isAllowed('sales/order/actions/view')) {
$this->addColumn('action',
array(
'header' => Mage::helper('sales')->__('Action'),
'width' => '50px',
'type' => 'action',
'getter' => 'getId',
'actions' => array(
array(
'caption' => Mage::helper('sales')->__('View'),
'url' => array('base'=>'*/sales_order/view'),
'field' => 'order_id'
)
),
'filter' => false,
'sortable' => false,
'index' => 'stores',
'is_system' => true,
));
}
$this->addRssList('rss/order/new', Mage::helper('sales')->__('New Order RSS'));
$this->addExportType('*/*/exportCsv', Mage::helper('sales')->__('CSV'));
$this->addExportType('*/*/exportExcel', Mage::helper('sales')->__('Excel XML'));
return parent::_prepareColumns();
}
protected function _prepareMassaction()
{
$this->setMassactionIdField('entity_id');
$this->getMassactionBlock()->setFormFieldName('order_ids');
$this->getMassactionBlock()->setUseSelectAll(false);
if (Mage::getSingleton('admin/session')->isAllowed('sales/order/actions/cancel')) {
$this->getMassactionBlock()->addItem('cancel_order', array(
'label'=> Mage::helper('sales')->__('Cancel'),
'url' => $this->getUrl('*/sales_order/massCancel'),
));
}
if (Mage::getSingleton('admin/session')->isAllowed('sales/order/actions/hold')) {
$this->getMassactionBlock()->addItem('hold_order', array(
'label'=> Mage::helper('sales')->__('Hold'),
'url' => $this->getUrl('*/sales_order/massHold'),
));
}
if (Mage::getSingleton('admin/session')->isAllowed('sales/order/actions/unhold')) {
$this->getMassactionBlock()->addItem('unhold_order', array(
'label'=> Mage::helper('sales')->__('Unhold'),
'url' => $this->getUrl('*/sales_order/massUnhold'),
));
}
$this->getMassactionBlock()->addItem('pdfinvoices_order', array(
'label'=> Mage::helper('sales')->__('Print Invoices'),
'url' => $this->getUrl('*/sales_order/pdfinvoices'),
));
$this->getMassactionBlock()->addItem('pdfshipments_order', array(
'label'=> Mage::helper('sales')->__('Print Packingslips'),
'url' => $this->getUrl('*/sales_order/pdfshipments'),
));
$this->getMassactionBlock()->addItem('pdfcreditmemos_order', array(
'label'=> Mage::helper('sales')->__('Print Credit Memos'),
'url' => $this->getUrl('*/sales_order/pdfcreditmemos'),
));
$this->getMassactionBlock()->addItem('pdfdocs_order', array(
'label'=> Mage::helper('sales')->__('Print All'),
'url' => $this->getUrl('*/sales_order/pdfdocs'),
));
return $this;
}
public function getRowUrl($row)
{
if (Mage::getSingleton('admin/session')->isAllowed('sales/order/actions/view')) {
return $this->getUrl('*/sales_order/view', array('order_id' => $row->getId()));
}
return false;
}
public function getGridUrl()
{
return $this->getUrl('*/*/grid', array('_current'=>true));
}
}Note that we only added method “protected function _addColumnFilterToCollection($column){…}” and we also modified store_id and status columns. We’ll apply our desired behavior only if we have our desired pattern with in/nin at the beginning of the string + csv.
Now place several orders in different stores and change some order statuses to pending_paypal, processing,…
If you now visit something like http://example.loc/index.php/admin/sales_order/ you should see similar grid as you had before, but now instead of drop-down menus for stores and order statuses you have plain text fields there.
You can now type in those columns something like:
“Purchased From (Store)” = “nin,3”
“Status” = “in, pending, pending_payment, pending_paypal”
as you can see for those two columns you can set whatever functionality you need (in or nin) at the beginning of your string with comma separated values after that. Note also that I added: “Zend_Debug::dump((string)$this->getCollection()->getSelect(), ‘Filter column step: ‘);” so you can see your query that will be sent to MySQL server.
From the previous example where we were using “IN” and “NIN” you could get result something like:
Note that Magento has similar functionality implemented for attributes. One of the examples is using property ‘filter_condition_callback’ that you can find in grids in $this->addColumn() method. See app/code/core/Mage/Adminhtml/Block/Cms/Block/Grid.php for more info.
