Filter order grid by multiple ID’s

search-multiple-id-featured

We all know how great Magento grids can be – there is no alternative when it comes to displaying data in a efficient manner.

Our clients sometimes (almost always) have specific requirements when it comes to Magento. One of them had a request to be able to filter the order grid by multiple order ID’s.

This can be useful if you have a number of orders you’d like to track – filtering them one by one can be tedious and time-consuming task.

The Trickery

The trick I’m about to show you can be done in almost any grid, once you know how it’s done.
Firstly, rewrite your order grid block (app\code\core\Mage\Adminhtml\Block\Sales\Order\Grid.php) – I presume you’ll know how to do this. 🙂

In your _prepareColumns() method, add a element to the array as shown below (line 8)

protected function _prepareColumns()
{
	$this->addColumn('real_order_id', array(
		'header'=> Mage::helper('sales')->__('Order #'),
		'width' => '250px',
		'type'  => 'text',
		'index' => 'increment_id',
		'filter_condition_callback' => array($this, 'spaceSeparatedFilter')//calling spaceSeparatedFilter method
	));
....
}

Renderers are being called when a row is being rendered in our grid, and get passed the cell that’s being rendered as a parameter.

Filter callbacks, on the other hand, get called with the entire column and collection as parameters. As a result, we can create our own method, call it via filter_condition_callback element, and do custom queries to the database, catch the input from our grid, etc.

This is exactly what we’ll do.

Our filter callback will call the spaceSeparatedFilter() method and pass it the real_order_id column as a parameter. Let’s declare our method (inside the same Grid.php file):

protected function spaceSeparatedFilter($collection, $column)
{
	if (!$value = $column->getFilter()->getValue()) {
		return $this;
	}
	//if there was a space input
		else if(preg_match('/\s+/', $value))
	{
		//explode by space, getting array of IDs
		$val = explode(" ", $value);
		//filter the collection, where collection index (order_id) is present in $val array
		$this->getCollection()->addAttributeToFilter($column->getData('index'), array('in'=>$val));
	}
	else
	{
	//else use default grid filter functionality (like $value input)
	$this->getCollection()->addAttributeToFilter($column->getData('index'), array('like' => '%'.$value.'%'));
	}
	return $this;
}

These two pieces of code enable you to filter orders by multiple order id’s, as long as you separate them with a space. If you haven’t entered a space, it will work as default (i.e. filtering orders with an IDLIKE‘ your query).

You can also use all features of the grid: sorting, filtering, export, without any problems.

Hope this will be useful to some of you.

View this code snippet on GitHub.


11 comments

  1. $this->addColumn(‘lead_time’, array(
    ‘header’ => $helper->__(‘Days Lead Time’),
    ‘width’ => ’50px’,
    ‘index’ => ‘lead_time’
    ‘filter_condition_callback’ => array($this, ‘separateness’)

    when ever i am adding the ‘filter_condition_callback’ => array($this, ‘spaceSeparatedFilter’) line it leads me blank page
    though i have defined the ‘spaceSeparatedFilter’ function .

  2. HI
    i need to prepare admin grid for filter customers (where coustomer group=”something”), in a separate module and all actions like save , save and continue create order are all have to work in this module also , can Inchoo help me??

  3. I’d like to implement a similar filter, I need help with a question that I haveI’d like to implement a similar filter

    I need to get a specific thing and do a ‘filter_condition_callback’ in the grid of orders

    Here is my code:

    In Renderer/FinalCustomer.php i have to this function:

    public function render(Varien_Object $row)
    {
    //load first item of the order
    $orderItem = Mage::getResourceModel(‘sales/order_item_collection’)
    ->addFieldToFilter(‘order_id’, $row->getId())
    ->getFirstItem()
    ;
    $orderItemOptions = $orderItem->getProductOptions();
    //if product doesn’t have options stop with rendering
    if (!array_key_exists(‘options’, $orderItemOptions)) {
    return ”;
    }
    $orderItemOptions = $orderItemOptions[‘options’];
    //if product options isn’t array stop with rendering
    if (!is_array($orderItemOptions)) {
    return ”;
    }
    foreach ( $orderItemOptions as $orderItemOption) {
    $label = $orderItemOption[‘label’];
    if ($label === ‘Driver\’s Name and Surname:’ ||
    $label === ‘Nombres y Apellidos del Conductor:’ ||
    $label === ‘Nome e cognome del conducente:’ ||
    $label === ‘Nome e cognome del autista:’ ||
    $label === ‘1- Apellidos / Nombre(s):’ ||
    $label === ‘1- Surname / Name(s):’ ||
    $label === ‘1- Cognome / Nome(i):’ ||
    $label === ‘Name:’ ||
    $label === ‘Name’ ||
    $label === ‘Nombre(s) y Apellido(s):’ ||
    $label === ‘Apellidos / Nombre(s):’ ||
    $label === ‘Name(s) and Surname(s):’ ||
    $label === ‘Name and Surname:’ ||
    $label === ‘Surname / Name(s):’ ||
    $label === ‘Nombre:’) {
    if (array_key_exists(‘value’, $orderItemOption)) {
    return $orderItemOption[‘value’];
    }
    }
    }
    //if product options doesn’t have Delivery Date custom option return void
    return ”;

    ——————————————————————————————–
    In Order/Grid.php

    $this->addColumn(‘final_customer’, array(
    ‘header’ => Mage::helper(‘sales’)->__(‘Final Customer’),
    ‘index’ => ‘entity_id’,
    ‘type’ => ‘text’,
    ‘renderer’ => ‘FFV_Myadminhtml_Block_Sales_Order_Renderer_FinalCustomer’,
    //’filter_condition_callback’ => array($this, ‘_customerFilter’),
    ));

    ——————————————————————————————
    But i need to obtain data that is within a column of the table sales_flat_order_item.product_options

    column product_options is a json file.

    an my function _customerFilter is:

    protected function _customerFilter($collection, $column)
    {
    if (!$value = $column->getFilter()->getValue()) {
    return $this;
    }

    $collection->getSelect()->where(
    // “`sales_flat_order_item`.product_options like ?”, “here data is a json column”);

    return $this;
    }

    But it does not work,

    I can help someone.

  4. Thank you for your post, it was a great help into understanding how to create a filter. The only problem I get is that I can only filter for a maximum of 12 items. After that it just returns me to the homepage. Do you have the same issue? Any suggestions on that?

    1. Ok so I found out that the code works on a different server, with another installation of Magento. Does anyone have a suggestion that could lead to it working on my other server aswell?

  5. What if i want to filter my track_number and title in my admin sales/order grid ??? The track number and title comes from sales_flat_shipment_track in magento. I have used the same thing as you have done, But my filter grid doesnt works.

    This code goes in grid.php,

    protected function spaceSeparatedFilter($collection, $column)
    {
    $value = $column->getFilter()->getValue();
    if (!$value) {

    return $this;
    }
    //if there was a space input
    else if(preg_match(‘/s+/’, $value))
    {
    //explode by space, getting array of IDs
    $val = explode(” “, $value);
    //filter the collection, where collection index (order_id) is present in $val array
    $this->getCollection()->addAttributeToFilter($column->getData(‘index’), array(‘in’=>$val));
    }
    else
    {
    //else use default grid filter functionality (like $value input)
    $this->getCollection()->addAttributeToFilter($column->getData(‘index’), array(‘like’ => ‘%’.$value.’%’));
    }
    return $this;
    }

    Now i have added filter condition to my addcolumn,

    $this->addColumn(‘track_number’, array(
    ‘header’=> Mage::helper(‘sales’)->__(‘ Track Number’),
    ‘width’ => ’80px’,
    ‘type’ => ‘text’,
    ‘index’ => ‘track_number’,
    ‘filter_condition_callback’ => array($this, ‘spaceSeparatedFilter’),
    ));

    $this->addColumn(‘title’, array(
    ‘header’=> Mage::helper(‘sales’)->__(‘Title’),
    ‘width’ => ’80px’,
    ‘index’ => ‘title’,
    ‘filter_condition_callback’ => array($this, ‘spaceSeparatedFilter’),
    ));

    But i am not able to filter in my sales/order grid. Please let me know the solution for this.

    Thanks and Regards.

    1. public function filterTrackNumber($collection, $column)
      {
      if (!$value = $column->getFilter()->getValue()) {
      return $this;
      }
      $this->getCollection()->getSelect()
      ->join(array(‘sfoi’ => ‘sales_flat_shipment_track’), ‘main_table.entity_id = sfoi.order_id’, array(‘track_number’))
      ->where(
      “sfoi.track_number like ?”
      , “%$value%”)->group(‘main_table.entity_id’);

      //echo ‘

      '; print_r($test);exit('ssss');  $collection->getSelect()->assemble();
          return $this;
      }
      
      this code is working for me
  6. In regards to : $column->getData(‘index’)

    I think it should be filter_index instead of index. Just to be safe. I did it by checking first on filter_index if it exists. I use that as the field. if not, then I use index. It’s necessary in certain situations. and I think how Magento work with filters. filter_index meant for filters.

  7. I’d like to implement a similar filter in a different column (not the order IDs column), but I’d like to be able to perform both “OR” and “AND” queries.

    So my idea is to separate values by either “OR” or “AND” keywords

    1)
    For example, this query in the “Invoice to Name” column:

    Peter OR Joseph

    Would return all the orders whose “Invoice to Name” field is either “Peter” or “Joseph”

    While this other:

    2)
    Peter AND McArthur would return all the orders whose “Invoice to Name” field contained both the word “Peter” and “McArthur”. Probably from a customer called “Peter McArthur”

    How could I achieve that?

    1. Hi, pmurillo

      For the first question, all you need to do is modify the regex on line 7 to look for ‘OR’ instead of space, and delimiter in the explode method on line 10 to explode by ‘OR’.

      As for the second, Magento already searches the way you propose, just better (e.g. you can enter just a part of the string to get a result, for example, ‘Petar McAr’ would find a customer ‘Peter McArthur’.

      Frankly, I don’t see a point doing the latter, as the exact name search would get you the same result, unless you really, really want to have ‘AND’ as a delimiter. 🙂

  8. Hi, I have 4 tabs in a page (tab1, tab2, tab3, tab4).
    How can I place new product based on category only.(filter)
    Thanks

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