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 this post helps you, then please like us on Facebook and follow us on Twitter.

Want to ask a question or leave a comment?

Leave a Comment

(24 Comments)

  • Prasanth

    @Chirag I want to show the custom customer text attribute in the filter. Now I can able to show the values on the sales order grid but can you tell me how to filter the same on the sales order grid. My customer attribute name is instituion_details I’m collecting this data during the customer registration.

  • Madzmar Ullang

    Hi I’m trying to do the same thing. I’m using customer_entity and joining it with customer_address_entity. But when I try to filter I’m getting a Unknown column ‘shipping_city’ in ‘where clause’. So shipping_city is not in any of these table it’s a custom name I added to refer to customer_address_entity.city. How can I fix this issue? Here’s my stackoverflow question

    https://stackoverflow.com/questions/62701729/filter-grid-using-custom-column

    • Chirag

      Try to save the shipping city in custom table along with order id while placing order and then using join you can display that field in order grid.

      • Madzmar Ullang

        HI Chirag thanks for the reply. Does it mean that I really need to save the field into a custom table? Because I would prefer to use customer_address_entity primarily because it’s already there and to reduce the number of custom table I create and maintain. Is there no other way for the shipping_city (alias column) be included in the query? I thought that maybe I can wrap the code you provided above into a subquery. for the custom alias table work. Right now the query above produce this kind of query

        SELECT `main_table`.*, `co`.`city` AS `shipping_city` FROM `customer_entity` AS `main_table`
        LEFT JOIN `customer_address_entity` AS `co` ON co.entity_id = main_table.default_shipping
        WHERE (`shipping_city` LIKE ‘%HIGHBRIDGE%’)

        But how can we make it something like this

        select * from (SELECT `main_table`.*, `co`.`city` AS `shipping_city` FROM `customer_entity` AS `main_table`
        LEFT JOIN `customer_address_entity` AS `co` ON co.entity_id = main_table.default_shipping ) as a WHERE (`shipping_city` LIKE ‘%HIGHBRIDGE%’)

        So basically the WHERE query is located outside but the main query is wrap in a subquery and I will do another query at the top level. That way the unknown column will be fix. Do you have any idea on how to solve this?

        • Chirag

          I never tried sub query in order grid.

  • akhilesh

    Do you have any extension for the same because we are getting error as “Class Codextblog\Ordercolumn\Model\CustomFactory does not exist”, please explain and share the file

    • Chirag

      NO. we don’t have extension. Have you check Custom class exist under model directory? and have you run setup:upgrade command?

  • Forman

    Thanks for your time… All seem to work, although, it will not allow sort option. Example. if i click on the MyColumn to sort it will give error “Something went wrong with processing the default view and we have restored the filter to its original state.” I know its a blanket statement but what do you think could be the problem.

    • Chirag

      I think it’s due to join. Are you getting any error of SQL query?

  • shravan

    i want to display tracking number in order grid i need someone to help

  • 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. Irrelevant comment with links directly goes to spam. Your email address will not be published.

    Was this post helpful? Please support Us!

    Follow us on twitter or Like us on facebook.

     


    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

    Increase Your
    Magento 2
    Knowledge

    Get Weekly Tutorial
    to your Inbox
    Subscribe Me
    close-link
    Subscribe Here