Prepared filter for Magento admin grids

screen1

Maybe you can ask yourself why do I need prepared data for filters in my collections – in Magento admin grids. Well, I can perhaps create new grid and action for some custom functionality and show such grid and then additionally do (filter by) whatever I need.

But notice that if you create such collection with some addFieldToFilter() method you always use specified filter for that collection.
If you are interested how to use only one collection for showing different results for that collection (prepared filter), keep reading.

To make this more clear I’ll tell you my example where I implemented this approach. So imagine that you’ve already created grid for showing all items, table sales_flat_order_items, in Magento administration. Then perhaps you’ve created some custom actions so you can change status of your items by selecting them in grids. Later, you’ve created some custom and complex relation for items, item statuses and order statuses, etc. So at the end you have grid that shows more than 10 columns for your grid with some specific data like what is the status of item, order status, when the item was shipped, when is created,…

Why is it important?

One example could be that you have several shipping methods in Magento app implemented and you know that those shipping distributions should deliver your items to customer in next 10-30 days. But then you’ve noticed that this isn’t always true. Maybe your customers started to complain that their items didn’t come in specified date range or so… So you’ve decided to create a grid for all of those unusual cases for your site. Notice that you are using one collection and every result you can get by filtering that grid.

So let’s start with similar implementation but instead of complicating the solution with item table let’s implement this for sales_order grid. Let’s create views for two cases:

a) Find all orders that are purchased on store_id = 1 and where order status is in processing status and “Purchased On” is from before 120 and up to before 1 day.
b) Find all orders that are purchased in other stores than store_id = 1 and where order status is in pending status.

I know that these a) and b) examples are not real life examples but you can see an idea behind them and use them for your real life examples. In a) you’ll see additionally how we can prepare date range using “From:” and “To:” filter option. Case b) I created for you just so you can see 3 different views of same collection, sales_order_collection. Note also that for b) we use my previous post “How to multiselect and filter in magento admin grids – IN and NOT IN” so if you didn’t already read it, it’s right time.

You might ask yourself why 3 different views if we have case a) and b)? Well, one is sales_order grid without any prepared filter and two more for a) and b) cases.

In my case I have more than 25 different views for only 1 collection. Imagine what pain would be if you must to create more than 25 different actions and grids for all of those cases. Not only that you’ll need to create more than 25 different actions/grids but what if you change something in your logic for your items or so? You’ll need then to modify all of those grids, then you should test all of those results to make sure your new logic is in your grids, etc. So if you are real ninja you should have only one collection so you can modify your logic ONLY once. Something like OOP vs procedural approach (spaghetti code) 🙂

I hope you agree and let’s start with our implementation for “prepared filters”.

1. Tell magento about our module
2. Create module config file and module structure
3. Create module adminhtml config file
4. Rewrite appropriate controller file
5. Rewrite Mage_Adminhtml_Block_Sales_Order_Grid

1. Create file app/etc/modules/Inchoo_PreparedFilter.xml with the following content

<?xml version="1.0" encoding="UTF-8"?>
<config>
	<modules>
		<Inchoo_PreparedFilter>
			<active>true</active>
			<codePool>local</codePool>
		</Inchoo_PreparedFilter>
	</modules>
</config>

2. Create file app/code/local/Inchoo/PreparedFilter/etc/config.xml with the following content

<?xml version="1.0"?>
 
<config>
 
	<modules>
		<Inchoo_PreparedFilter>
			<version>1.0.0.0</version>
		</Inchoo_PreparedFilter>
	</modules>
 
	<global>
		<blocks>
			<adminhtml>
				<rewrite>
					<sales_order_grid>Inchoo_PreparedFilter_Block_Adminhtml_Sales_Order_Grid</sales_order_grid>
				</rewrite>
			</adminhtml>
		</blocks>
	</global>
 
	<admin>
        <routers>
            <adminhtml>
                <args>
                    <modules>
                        <Inchoo_PreparedFilter before="Mage_Adminhtml">Inchoo_PreparedFilter_Mage_Adminhtml</Inchoo_PreparedFilter>
                    </modules>
                </args>
            </adminhtml>
        </routers>
    </admin>
</config>

3. Create file app/code/local/Inchoo/PreparedFilter/etc/adminhtml.xml with the following content

<?xml version="1.0"?>
 
<config>
	<menu>
		<sales translate="title" module="sales">
			<title>Sales 123</title>
			<sort_order>20</sort_order>
			<depends>
				<module>Mage_Sales</module>
			</depends>
			<children>
				<order translate="title" module="sales">
					<title>Orders 123</title>
					<action>adminhtml/sales_order</action>
					<sort_order>10</sort_order>
					<children>
						<order_default>
							<title>Orders 123</title>
							<action>adminhtml/sales_order</action>
							<sort_order>10</sort_order>
						</order_default>
						<order_a>
							<title>Orders a</title>
							<action>adminhtml/sales_order/index/prepared/a</action>
							<sort_order>20</sort_order>
						</order_a>
						<order_b>
							<title>Orders b</title>
							<action>adminhtml/sales_order/index/prepared/b</action>
							<sort_order>30</sort_order>
						</order_b>
					</children>
				</order>
			</children>
		</sales>
	</menu>
	<acl>
		<resources>
			<admin>
				<children>
					<sales translate="title" module="sales">
						<title>Sales</title>
						<children>
							<order translate="title" module="sales">
								<title>Orders 123</title>
								<sort_order>10</sort_order>
								<children>
									<order_default>
										<title>Orders 123</title>
										<sort_order>10</sort_order>
									</order_default>
									<order_a>
										<title>Orders a</title>
										<sort_order>20</sort_order>
									</order_a>
									<order_b>
										<title>Orders b</title>
										<sort_order>30</sort_order>
									</order_b>
								</children>
							</order>
						</children>
					</sales>
				</children>
			</admin>
		</resources>
	</acl>
</config>

As you can see we’ve rewritten Magento core config for sales_order menu link and add those links in menu in separate group. Additionally I added 123 in title so you can see changes.

4. Create file app/code/local/Inchoo/PreparedFilter/controllers/ Mage/Adminhtml/Sales/OrderController.php with the following content

<?php
 
$defController = Mage::getBaseDir()
	. DS . 'app' . DS . 'code' . DS . 'core'
	. DS . 'Mage' . DS . 'Adminhtml' . DS . 'controllers'
	. DS . 'Sales' . DS . 'OrderController.php';
require_once $defController;
 
/**
 * Adminhtml sales(123) orders controller
 *
 * @author      Inchoo <ivan.galambos@inchoo.net>
 */
class Inchoo_PreparedFilter_Mage_Adminhtml_Sales_OrderController extends Mage_Adminhtml_Sales_OrderController
{
 
    /**
     * Orders grid
     */
    public function indexAction()
    {
 
        if ($this->getRequest()->getParam('prepared') === 'a') {
 
        	Mage::getSingleton('adminhtml/session')->addSuccess($this->__('You are viewing a) case of order grid.'));
 
        	$this->_title($this->__('Sales'))->_title($this->__('Orders'));
 
        	$from = date("Y-m-d", strtotime('-120 day'));
        	$to = date("Y-m-d", strtotime('-1 day'));
        	$locale = Mage::app()->getLocale()->getLocaleCode();
 
        	Mage::register('preparedFilter', array(
	        	'store_id' => '1',
	        	'status' => 'processing',
	        	'created_at' => array(
		        	'from'=> new Zend_Date($from, null, $locale),
		        	'to'=> new Zend_Date($to, null, $locale),
		        	'locale' => $locale,
		        	'orig_to' => Mage::helper('core')->formatDate($to),
		        	'orig_from' => Mage::helper('core')->formatDate($from),
		        	'datetime' => true
	        	)
        	));
 
        } else if ($this->getRequest()->getParam('prepared') === 'b') {
 
        	Mage::getSingleton('adminhtml/session')->addSuccess($this->__('You are viewing b) case of order grid.'));
 
        	$this->_title($this->__('Sales b'))->_title($this->__('Orders b)'));
 
        	Mage::register('preparedFilter', array(
	        	'store_id' => 'nin, 1',
	        	'status' => 'pending', //note that it could by default also be in pending_peyment and pending_paypal
        	));
 
        } else {
 
        	Mage::getSingleton('adminhtml/session')->addSuccess($this->__('You are viewing default sales order grid.'));
 
        	$this->_title($this->__('Sales'))->_title($this->__('Orders'));
 
        }
        $this->_initAction()->renderLayout();
    }
}

Here we created preparedFilter in registry as array and we’ll use that value in our grid.

5. Create file app/code/local/Inchoo/PreparedFilter/Block/ Adminhtml/Sales/Order/Grid.php with the following content

<?php
 
/**
 * Adminhtml sales orders grid
 *
 * @author      Inchoo <ivan.galambos@inchoo.net>
 */
class Inchoo_PreparedFilter_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->setDefaultSort('entity_id');
        $this->setDefaultDir('DESC');
        $this->setSaveParametersInSession(true);
 
        if (Mage::registry('preparedFilter')) {
        	$this->setDefaultFilter( Mage::registry('preparedFilter') );
        }
    }
 
    /**
     * Modify column filter if needed by custom implementation of IN() and NOT IN() MySQL statement + prepared filter functionality
     *
     */
    protected function _addColumnFilterToCollection($column)
    {
        $filterArr = Mage::registry('preparedFilter');
 
    	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);
 
    		}
    	} elseif (is_array($filterArr) && array_key_exists($column->getId(), $filterArr) && isset($filterArr[$column->getId()])) {
    		$this->getCollection()->addFieldToFilter($column->getId(), $filterArr[$column->getId()]);
 
    	} else {
    		parent::_addColumnFilterToCollection($column);
 
    	}
    	Zend_Debug::dump((string)$this->getCollection()->getSelect(), 'Prepared filter:');
    	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));
    }
}

And visual representation:

To summarize everything, we use prepared filters as our templates for a collection.

I hope this will help someone!


6 comments

  1. Nice tutorial! Is there a tutorial for the observer method instead of the rewrite method?

  2. Hey,i m new in magento ,please help me to filter order grid by only particular store purchased order , that means when a Store-A admin login in his account he can only view his Store Website purchased orders also if Store-B admin login he can view only Store B website purchased orders and if Main admin login he can view all purchased order (same as default order grid).

  3. Thanks for posting this. It doesn’t seem to work with the Ajax pagination though. Any ideas on how to resolve? Cheers, Chris

  4. Hi,

    Could you please help to understand, how grid loads the data from collection.

    When grid is loaded, does it loads whole data from database into the JSON and retrieve the data from JSON object on local machine while pagination?

    If above is yes, then don’t you think it is a bad practice, as browser may be frozen if data is too much.

    waiting for a quick response on the same.

    Thanks,
    Pankaj Gupta,

  5. Hi Ivan. Great post, thank you. But I have a question.
    I need 2 views. First to all orders and second to orders with status ‘pending’.
    I have 88 orders and 62 orders in status ‘pending’. When I go to my second view, system say me that
    I’m on 1 of 4 page| view 20| total 62 records found. If i try to go on the 2-nd page, system say me that
    I’m on 2 of 5 page| view 20| total 88 records found- my filter broken,despite the fact that URI is still
    admin/sales_order/index/prepared/a/.
    I don’t know if collection in magento save number of pages and view(20 on page) somewhere, and call collection again without any filters…
    Thank you.

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>.