How to Add Column with Filter to Magento 2 Orders Grid?

Reading Time: 9 minutes

Often, Magento 2 stores administrators require extra options when it comes to using and customizing the orders grid ― filtering based on a specific out-of-the-box parameter can become a real challenge.

Recently, a Magento Developer reached me out with a question. He tried to expand the orders grid on Magento 2.3.1. The old posts available on the web didn’t help out ― naturally, much has changed in Magento since last two years.

Based on his question, I offer you to take da look at the solution for a specific case:

We need to add a column ― with some regional code of a customer who completed a purchase ― to the orders grid. Additionally, a store administrator must have a possibility to filter orders by this newly added column.

It might seem easy, but there are a couple of things worth paying attention to. For example, a shopper who made a purchase may not have any delivery address ― in case of a virtual order. Or, how shall we list the regions? All this should be taken into consideration when getting down to development. Based on the questions mentioned earlier, let’s assume:

• virtual products won’t have a delivery region (‘null’). This will help you choose them based on this parameter,
• regions will be listed and have a look of codes without their transformation into labels, just as it is in the default Magento 2.

*Please note that at the end of this article there will be a link to the module with open access on GitHub. Though, I do include the link in this paragraph if you do not manage to finish this article: https://github.com/mageworx/articles-extended-orders-grid.

However, despite the lack of time, I do encourage you to read on.?

Thus, to add a new column to the orders grid, you need to:

1. Create a new module

First, let’s decide on the module and vendor names. Well, no need for me to choose a vendor name ― it’s MageWorx (as if I have a choice, just kidding). I still get to choose the module name. Let it be ExtendedOrdersGrid (btw we have a same-name extension for Magento 2). In fact, using MageWorx as a vendor name in the namespace does not give you the right to request free support. Anyways, if our support team members had a good weekend, you can still give it a try on Monday.?

Let’s create the following directory: `app/code/MageWorx/ExtendedOrdersGrid`. To register a module, we will need some standard files:

> registration.php

php
<?php
\Magento\Framework\Component\ComponentRegistrar::register(
    \Magento\Framework\Component\ComponentRegistrar::MODULE,
    'MageWorx_ExtendedOrdersGrid',
    __DIR__
);


> composer.json

json
{
    "name": "mageworx/module-extended-orders-grid",
    "description": "Extended Orders Grid Extension",
    "require": {
        "magento/module-ui" : ">=100.1.0 < 102",
        "magento/module-sales" : ">=100.0.0 <103"
    },
    "type": "magento2-module",
    "version": "1.0.0",
    "license": [
        "OSL-3.0",
        "AFL-3.0"
    ],
    "autoload": {
        "files": [ "registration.php" ],
        "psr-4": {
            "MageWorx\\ExtendedOrdersGrid\\": ""
        }
    }
}


> etc/module.xml

xml
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="MageWorx_ExtendedOrdersGrid" setup_version="1.0.0">
        <sequence>
            <module name="Magento_Sales"/>
            <module name="Magento_Ui"/>
        </sequence>
    </module>
</config>

It is important to note that I’ve cut the copyright that gets added in my IDE automatically. Thus, you get to use this code with no trouble. ?

Then, run a few commands:

> sudo -u www-data php bin/magento module:enable MageWorx_ExtendedOrdersGrid
> sudo -u www-data php bin/magento setup:upgrade

And voila! Now, our module can be seen in Magento 2! Oh, if you work on a remote server, do not forget to transfer the files before testing.

2. Add a column to the grid

Then, using Magento 2 UI, let’s add a new column to the standard grid. For that, create a file:

> view/adminhtml/ui_component/sales_order_grid.xml

with the following content (its meaning we will discuss further):

xml
<?xml version="1.0" encoding="UTF-8"?>
<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="code">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="component" xsi:type="string">Magento_Ui/js/grid/columns/column</item>
                    <item name="label" xsi:type="string" translate="true">Region Code</item>
                    <item name="sortOrder" xsi:type="number">60</item>
                    <item name="align" xsi:type="string">left</item>
                    <item name="dataType" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">true</item>
                    <item name="filter" xsi:type="string">text</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

Here, you need to specify that the name is not taken out of anywhere. For experienced developers, this is clear, but for those who only get to know Magento 2, I’ll add some explanations at the end of this paragraph.

We used a standard `columns` node to add columns to the grid, where a new column under ‘code’ name shall be added as well. Next, let’s write the attributes of our column in the grid:

1) Component. This is a JS class that is responsible for the creation and processing of this column. It is located in the Magento_Ui module at the following address:

`vendor/magento/module-ui/view/base/web/js/grid/columns/column.js`

In fact, if you are a curious developer, take a look at other realizations as well. There is a lot of exciting stuff out there.

2) Label. This is a line with a column name, which will be displayed to an end user. Don’t forget to add it to the i18n localization file, if the `translate` property has been established. ?

3) SortOrder. This is the column position in the grid. If the module has been installed on Magento 2 that has never been managed by a store administrator, it will have an effect. Otherwise, on Magento 2 that is being used, our column will get added to the end of the list no matter what we do.

4) Align. This means aligning the column content. I guess that’s clear.

5) DataType. This is a data type we will manipulate. In our case, that’s simply a line of text. However, it can be a list or a Boolean value, as well as numbers if we talk about products quantity, for instance.

6) Visible. This is nothing else but the column visibility though much depends on whether the grid was previously used or not.

All data about the grid is stored in the Magento database, in the `ui_bookmark` table. Thus, if there is no record about our column for the modified grid at the time of our module installation, don’t expect any miracle.

7) Filter. This is the filter type. Here, we specified that filtering should be done as with regular text. While running code, it will transform into the MySQL `LIKE %value%` condition.

---

Explanations

To make the long story short, the name of the extended UI component must correspond to the name of the original. In our case, it is `vendor/magento/module-sales/view/adminhtml/ui_component/sales_order_grid.xml`, and it’s calculated from the `view/adminhtml/ui_component/sales_order_grid.xml` module root.

UI grid itself gets added to the required controller using `vendor/magento/module-sales/view/adminhtml/layout/sales_order_index.xml` layout  just as follows:

xml
<?xml version="1.0"?>
<!--
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
-->
<page xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:View/Layout/etc/page_configuration.xsd">
    <update handle="styles"/>
    <body>
        <referenceContainer name="content">
            <uiComponent name="sales_order_grid"/>
        </referenceContainer>
    </body>
</page>

This means *add this UI component under `sales_order_grid` name to the page content* ― just as in case of blocks. However, instead of blocks, we’ve got `uiComponent` with its attributes and limitations.

---

Now, clear Magento 2 cache using the following command:

> sudo -u www-data php bin/magento cache:clean config

and check the results in the orders grid:

3. Add data to the column

You might have noticed that the column has appeared, but its content looks not just as expected…there is no data displayed there at all. Fair enough, we didn’t add anything, thus no content there.

Let’s get down to writing a simple plugin that will help you fill in our column. For that, let’s catch the grid collection query, make a join within the required table and grid. The plugin will look just as follows:

> app/code/MageWorx/ExtendedOrdersGrid/Plugin/AddDataToOrdersGrid.php

php
<?php
namespace MageWorx\ExtendedOrdersGrid\Plugin;

/**
 * Class AddDataToOrdersGrid
 */
class AddDataToOrdersGrid
{
    /**
     * @var \Psr\Log\LoggerInterface
     */
    private $logger;

    /**
     * AddDataToOrdersGrid constructor.
     *
     * @param \Psr\Log\LoggerInterface $customLogger
     * @param array $data
     */
    public function __construct(
        \Psr\Log\LoggerInterface $customLogger,
        array $data = []
    ) {
        $this->logger   = $customLogger;
    }

    /**
     * @param \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject
     * @param \Magento\Sales\Model\ResourceModel\Order\Grid\Collection $collection
     * @param $requestName
     * @return mixed
     */
    public function afterGetReport($subject, $collection, $requestName)
    {
        if ($requestName !== 'sales_order_grid_data_source') {
            return $collection;
        }

        if ($collection->getMainTable() === $collection->getResource()->getTable('sales_order_grid')) {
            try {
                $orderAddressTableName = $collection->getResource()->getTable('sales_order_address');
                $directoryCountryRegionTableName = $collection->getResource()->getTable('directory_country_region');
                $collection->getSelect()->joinLeft(
                    ['soa' => $orderAddressTableName],
                    'soa.parent_id = main_table.entity_id AND soa.address_type = \'shipping\'',
                    null
                );
                $collection->getSelect()->joinLeft(
                    ['dcrt' => $directoryCountryRegionTableName],
                    'soa.region_id = dcrt.region_id',
                    ['code']
                );
            } catch (\Zend_Db_Select_Exception $selectException) {
                // Do nothing in that case
                $this->logger->log(100, $selectException);
            }
        }

        return $collection;
    }
}

It will refer to the following class:

`Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory`

> etc/adminhtml/di.xml
 
xml
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
	<!-- Plugins -->
	<!-- Adds additional data to the orders grid collection -->
	<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
    	<plugin name="mageworx_extended_orders_grid_add_data_to_orders_grid"
                type="MageWorx\ExtendedOrdersGrid\Plugin\AddDataToOrdersGrid"
                sortOrder="10"
                disabled="false"/>
	</type>
</config>

The plugin runs the following way:

As it will catch absolutely all collections, let’s add validation to the required `sales_order_grid` table. When it gets found and Magento attempts to obtain data on this, we make a join of the table with `sales_order_address` address by`order_id` (`entity_id` in the `sales_order_grid` table and `parent_id` in the `sales_order_address` table (`soa` alias)). Now, we’ve got access to the customer’s order delivery address data, which we can use to determine `region_id`. It is located as a number in the `sales_order_address` table. This number corresponds to the index in the `directory_country_region` table (`dcrt` alias). As we need this regional code from the table as mentioned above, let’s connect the ‘code’ column. This precise column will get output (corresponds to the `column` value in `sales_order_grid.xml`).

Then, clear cache:

> sudo -u www-data php bin/magento cache:clean config

Log in the admin panel to the orders grid. If everything has been done correctly, we will see our regional codes displayed:

Free access to the module is available on GitHub: https://github.com/mageworx/articles-extended-orders-grid.

Important sidenote!

To add any data to this column, it is required to get ‘your_column_name’ column and all the needed data added when creating a collection. That is, you need to do something different ― replace the table name and write your own join. Also, don’t forget to edit the column name in the `sales_order_grid.xml` file if required.

How to Add an Extra Column?

If you decide to add one more column, please, follow the guidelines described in the following Git commit: https://github.com/mageworx/articles-extended-orders-grid/commit/d31c364a25ce493ab64731c5ca0481e146dbbac3

There, we’ve added the telephone column to the grid from the sales_order_address table for the address of the shipping type. As you can see in the commit code, no significant code modifications were required.

Additionally, these columns can be successfully exported from the standard ‘order grid’ interface.

Here is how the grid with the ‘telephone’ column looks like:

In the screenshot below, you can see the same orders exported to a .csv file:

[Update] How to Add a Column with Order Items Info?

As you know, all orders have items qty, starting from one to out beyond number (yeah, merchants’ dream). But how can we display that info in the orders grid to start a search or run an analysis more easily?

We can’t just add a column as we did it earlier because we get only one product name or SKU on each row in that case. Or, order records will be duplicated… We don’t need that mess either.

Thus, I’ll try to explain how we can do that the right way (in my opinion).

Suppose we need a ‘Product Name’ data column in the orders grid.

First, let’s add a new column in the sales_order_grid definition, as we did it earlier:

    <columns>
        ....
        <column name="name">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="component" xsi:type="string">Magento_Ui/js/grid/columns/column</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Product Name</item>
                    <item name="visible" xsi:type="boolean">false</item>
                    <item name="sortOrder" xsi:type="number">70</item>
                </item>
            </argument>
        </column>
    </columns>

Then, all we need is to build the right query for our new column. It must contain the name of each product bought in that order, separated by a comma, with the ability to search by that column values, etc. Let’s create a new method in the mageworx_extended_orders_grid_add_data_to_orders_grid plugin:

    /**
     * Adds products name column to the orders grid collection
     *
     * @param OrderGridCollection $collection
     * @return OrderGridCollection
     */
    private function addProductsNameColumn(OrderGridCollection $collection): OrderGridCollection
    {
        return $collection;
    }

and name that method inside the original method body:

if ($collection->getMainTable() === $collection->getResource()->getTable('sales_order_grid')) {
    try {
        $orderAddressTableName = $collection->getResource()->getTable('sales_order_address');
        ...

        // Add product's name column
        $this->addProductsNameColumn($collection);
    } catch (\Zend_Db_Select_Exception $selectException) {
    ...        

To obtain the desired data in one column, we must create a sub-select with two columns: order_id and name (product name) in the individual select, which can be joined to the main collection later:

// Get original table name
$orderItemsTableName = $collection->getResource()->getTable('sales_order_item');
// Create new select instance
$itemsTableSelectGrouped = $collection->getConnection()->select();
// Add table with columns which must be selected (skip useless columns)
$itemsTableSelectGrouped->from(
    $orderItemsTableName,
    [
        'name'     => new \Zend_Db_Expr('GROUP_CONCAT(DISTINCT name SEPARATOR \',\')'),
        'order_id' => 'order_id'
    ]
);
// Group our select to make only one column for one order
$itemsTableSelectGrouped->group('order_id');

Clarifications:

• $collection->getConnection()->select() line will create a new Magento\Framework\Db\Select instance.
  This is required because we can’t use the original select from the collection as it has its own data inside, and any modifications will lead to errors.
• name column must have all product names for the specified order, i.e., it must be grouped using the\Zend_Db_Expr('GROUP_CONCAT(DISTINCT name SEPARATOR \',\')') expression. For that purpose, we are adding group('order_id') to the select later. Without grouping, we can’t use the GROUP_CONCAT function.

Now, we can add our sub-select to the main collection and it will be the logical end of the addProductsNameColumn method:

// Add our sub-select to main collection with only one column: name
$collection->getSelect()
        ->joinLeft(
            ['soi' => $itemsTableSelectGrouped],
            'soi.order_id = main_table.entity_id',
            ['name']
        );

return $collection;

Clarifications:

• soi is an alias for our pseudo-table.
• order_id is a key, which we use to link our main table (grid) to the order items data.
• ['name'] is the only column, which gets added to the result, because we don’t need other information.

The final result is available in the official repository of that example.
Here’s the link to the specific commit: https://github.com/mageworx/articles-extended-orders-grid/commit/0cdffcd4ba66cacb2fd857ba7626fdbcfc0d6fe3

Here’s how that column looks on our staging host:

And here is the result of the export (CSV):

Here’s how the query looks like when trying to search orders with “Black” products (on our dev host):
SELECT main_table.*, soat.telephone, dcrt.code, soi.name FROM sales_order_grid AS main_table
LEFT JOIN sales_order_address AS soat ON soat.parent_id = main_table.entity_id AND soat.address_type = ‘shipping’
LEFT JOIN directory_country_region AS dcrt ON soat.region_id = dcrt.region_id
LEFT JOIN (SELECT GROUP_CONCAT(DISTINCT name SEPARATOR ‘,’) AS name, sales_order_item.order_id FROM sales_order_item GROUP BY order_id) AS soi ON soi.order_id = main_table.entity_id
WHERE soi.name LIKE ‘%Black%’

Evidently, this is not the fastest way to output data. However, that’s probably the easiest one. The best way is to accumulate data about product names in a separate column of a separate table (order_id, products_name) and add this table without extra grouping and any sub-selection.

For that purpose, we add the group (‘order_id’) to our select (at the end of the method code).
The select means an element.
The group means the select method.

---

I guess that’s it. Should you have any questions or requests, please leave a comment in the comment field.