How to multiselect and filter in magento admin grids – IN and NOT IN

img11

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.


5 comments

  1. Can I add filters for products grid with custom attributes filter which is not sown in the grid???
    Example : description_test (custom attribute)
    I want to get list of products which has null value in description_test

    Regards
    Jatinder Saini

  2. Your tutorials are always awesome. I’ve learned a lot from your site. I was just able to apply this to creating an open order status to select status nin complete, canceled, closed. Works great – Thank you for the useful information

  3. Hi,

    Thanks for the post and also interesting.

    I want similar functionality to filter multiple order simultaneously in sales grid. Please help me to do the same as i m new to magento.

    Thanks

  4. An interesting post.

    Looking to do something similar, except to do a like and nlike search function.

    So you could look up say HardDrive as product name, but exclude all of the 5400rpm drives with the following search.

    like(harddrive), nlike(5400rpm)

    so that it would perform something like the following query.

    like %harddrive% AND nlike %5400rpm%

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.