How to add a custom column in order grid in Magento 2

Add custom column in order grid Magento 2

In this post, we are going to see how we can add a custom column in order grid in Magento 2. We are going to create one custom module which will use ui_component to add a custom column in order grid. This custom column will render a data from a custom database table using JOIN. We will highlight only the important file, not the whole module.

Getting Started: Development

Step 1: Create module.xml file under app/code/Codextblog/Ordercolumn/etc directory and registration.php file under app/code/Codextblog/Ordercolumn directory.

Step 2: Create sales_order_grid.xml file under app/code/Codextblog/Ordercolumn/view/adminhtml/ui_component directory with below code

<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="short_name" class="Codextblog\Ordercolumn\Ui\Component\Listing\Column\Mycolumn">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">My Column</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

In this code, we have added our custom column named ‘short_name’. This column is defined in our custom module database table ‘custom_order’ which looks like this.

Custom module database table
Custom module database table

Step 3: Create Mycolumn.php ui component class file under app/code/Codextblog/Ordercolumn/Ui/Component/Listing/Column directory with below code

<?php
namespace Codextblog\Ordercolumn\Ui\Component\Listing\Column;

use \Magento\Sales\Api\OrderRepositoryInterface;
use \Magento\Framework\View\Element\UiComponent\ContextInterface;
use \Magento\Framework\View\Element\UiComponentFactory;
use \Magento\Ui\Component\Listing\Columns\Column;
use \Magento\Framework\Api\SearchCriteriaBuilder;
use \Codextblog\Ordercolumn\Model\CustomFactory;

class Mycolumn extends Column
{

    protected $_orderRepository;
    protected $_searchCriteria;
    protected $_customfactory;

    public function __construct(
        ContextInterface $context,
        UiComponentFactory $uiComponentFactory,
        OrderRepositoryInterface $orderRepository,
        SearchCriteriaBuilder $criteria,
        CustomFactory $customFactory,
        array $components = [], array $data = [])
    {
        $this->_orderRepository = $orderRepository;
        $this->_searchCriteria  = $criteria;
        $this->_customfactory = $customFactory;
        parent::__construct($context, $uiComponentFactory, $components, $data);
    }

    public function prepareDataSource(array $dataSource)
    {
        if (isset($dataSource['data']['items'])) {
            foreach ($dataSource['data']['items'] as & $item) {
                $order  = $this->_orderRepository->get($item["entity_id"]);

                $order_id = $order->getEntityId();

                $collection = $this->_customfactory->create()->getCollection();
                $collection->addFieldToFilter('order_id',$order_id);

                $data = $collection->getFirstItem();



                $item[$this->getData('name')] = $data->getShortName();
            }
        }
        return $dataSource;
    }
}

In this code \Codextblog\Ordercolumn\Model\CustomFactory is the module in which we have stored the order id in a custom database table.

In prepareDataSource function, we get the current order id. Using this order id we are querying our custom module database table to fetch the appropriate short_name.

After following above steps you will get your custom column added in the order grid.

Custom column in order grid
Custom column in order grid

Now if you filter the text in your custom column it will not work. To make it work we need to create a plugin which will filter the custom column using JOIN between ‘sales_order_grid’ and custom database table ‘custom_order’.

Step 4: Create di.xml file under app/code/Codextblog/Ordercolumn/etc/adminhtml directory with below code.

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Framework\View\Element\UiComponent\DataProvider\Reporting">
        <plugin name="sales_grid_collection" type="\Codextblog\Ordercolumn\Model\Plugin\Sales\Order\Grid"/>
    </type>
</config>

Step 5: Create plugin model file Grid.php under app/code/Codextblog/Ordercolumn/Model/Plugin/Sales/Order directory with below code.

<?php
namespace Codextblog\Ordercolumn\Model\Plugin\Sales\Order;


class Grid
{

    public static $table = 'sales_order_grid';
    public static $leftJoinTable = 'custom_order';

    public function afterSearch($intercepter, $collection)
    {
        if ($collection->getMainTable() === $collection->getConnection()->getTableName(self::$table)) {

            $leftJoinTableName = $collection->getConnection()->getTableName(self::$leftJoinTable);

            $collection
                ->getSelect()
                ->joinLeft(
                    ['co'=>$leftJoinTableName],
                    "co.order_id = main_table.entity_id",
                    [
                        'short_name' => 'co.short_name'
                    ]
                );

            $where = $collection->getSelect()->getPart(\Magento\Framework\DB\Select::WHERE);

            $collection->getSelect()->setPart(\Magento\Framework\DB\Select::WHERE, $where);

            //echo $collection->getSelect()->__toString();die;


        }
        return $collection;


    }


}

We have left joined two tables on ‘sales_flat_order’ table ‘entity_id’ and ‘custom_order’ table ‘order_id’. Once we applied this code our filter is started working.

custom order column with filter
custom order column with filter

If you liked this post, then please like us on Facebook and follow us on Twitter.

Leave a Comment

(14 Comments)

  • Ask Xah

    Hi, I want to make a dropdown filter for my custom column, Is it possible to predefine the options on the xml. and also tell me how to create a dropdown filter in sale order grid.

  • Virang

    After applying this code, Order status filter is not working. Any solution?

  • Jayashree

    1.How and when the custom table get data ?
    2.How order id is saved in custom table ?
    3.After placing successful order , how this table gets the data?

    Please provide me a good solution

    • Virang

      You’ve to create custom table by install schema with your custom module and then you can insert data into that table using after place order event. That’s it..!

  • Tajveez

    is there a way from which we Filter our custom column with creating a custom table?

    • Chirag

      Yes, We have demonstrate the same thing here. Ordercolumn is a custom module and custom_order is a custom table. Using above code we have display custom column from custom_order table in order grid and then filter the column using plugin

      • Tajveez

        Oh, sorry i meant without creating a custom table.

      • Tajveez

        Actually, I have created a custom column in product_listing grid and I’m populating that column with some custom data, I’m showing parent’s SKU against each entity_id, i’m getting these sku via Magento methods, now the everything is fine and data is showing in it. but when I try to filter it, it gives error “something went wrong”. i haven’t created any custom table or anything. how can i filter my custom column ?

  • Imre Lados

    Hi,

    “Class Codextblog\Ordercolumn\Model\CustomFactory does not exist”

    • Chirag

      Customfactory is a factory class of custom module Ordercolumn.You have to use your module factory class.

  • jordan

    Hi, your blog posts are very helpful. I have made follow above code but have a problem when filter. I checked and found error query https://prnt.sc/lufhxf .how to fix this ? Thanks

    • Chirag

      That means you have a same column name billing_name in your join query. Please use alias and try again.

  • Rekha

    Your blog posts are really awesome and very helpful. Just thought to give one suggestion. If you give full module file through Github at end of every post will be helpful and will give a thorough understanding of every concept

    • Chirag

      Glad to hear my blog posts are helping you. Yes, will plan to upload each post code on GitHub very soon.

  • All the comments are goes into moderation before approval. Please do not spam. Your email address will not be published. Required fields are marked *

    Enjoy this post? Please support Us!

    Like our social page for daily new updates….


    Want to become
    a Magento 2 Expert?

    If Yes! Then Subscribe to our newsletter and get weekly article to you email id.
    Subscribe Here
    SUBSCRIBE NOW
    close-link

    To Avoid Spam Downloads, We Want Your Email

    We will send you download link right
    away. Please submit form below.
    SEND ME DOWNLOAD LINK
    Close