Tuesday, May 3, 2016

Magento 2 Guide. Sales Order Grid Column.

Please see the updated and improved instructions here
Sales order grid in Magento 2 can be accessed in admin panel under "Sales" -> "Orders" menu.
By default only several main columns are visible in grid, but there are additional columns, that can be enabled from "Columns" dropdown on the top-right side.

Magento 2 Sales Order Grid Columns
Magento 2 Sales Order Grid Columns

However, if you are creating a module that provides additional useful information about orders, it is a good idea to add corresponding columns to sales order grid.

Basically, to add a column to sales order grid you have to perform 3 simple steps:
  • Add column to sales_order_grid database table
  • Add DI configuration to populate the column in sales_order_grid table with your value
  • Add UI component configuration to display the column in grid
Let's go through this implementation step by step.


First of all, ensure you have your column in database table, and it is mapped to any field of sales_order table. For my example, let's assume that there is affiliate table consisting of two columns: order_id and affiliate_information. This table is used to store some affiliate information related to particular order. It would be nice to display this information in "Affiliate Information" column in sales order grid. Let's do this!

Adding column to sales_order_grid database table

Columns are added to database tables using InstallSchema script. To be consistent, it this script should be updated in the same module, where affiliate table was added.

InstallScema path:

The following code fragment will do the trick:

        'type' => Table::TYPE_TEXT,
        'comment' => 'Affiliate Information'

Just add it to "app/code/<Namespace>/<Module>/Setup/InstallSchema.php" file and it will create a column named affiliate_information, of type text, with comment "Affiliate Information" to sales_order_grid during installation.

Don't worry about implications of core table modification. sales_order_grid is index table and is used for order grid rendering speed up. It is designed to store all information required for sales order grid rendering, so custom columns are required to be added to this table.

To reflect changes in database magento reinstallation is required. Optionally, deleting module entry from setup_module table and running bin/magento setup:upgrade command will be also ok.

After this step affiliate_information column is present in sales_order_grid table, but is remaining empty as it is not mapped to any data source.

DI configuration to populate the column is sales_order_grid table

On this stage, it would be good to understand how sales_order_grid table is populated.
When order is placed (according to default configuration), data related to this order is selected from sales_order table joining several additional tables and inserted to sales_order_grid. This operation is initiated by \Magento\Sales\Model\ResourceModel\Grid::refresh function and the default select is declared in "<Magento Sales module>/etc/di.xml" file.

So to include our table in mentioned insert from select, we have to extend di configuration creating "app/code/<Namespace>/<Module>/etc/adminhtml/di.xml" file.

The following xml snippet should be added to di configuration inside config node.

<config ...>
    <virtualType name="Magento\Sales\Model\ResourceModel\Order\Grid">
            <argument name="joins" xsi:type="array">
                <item name="affiliate" xsi:type="array">
                    <item name="table" xsi:type="string">affiliate</item>
                    <item name="origin_column" xsi:type="string">entity_id</item>
                    <item name="target_column" xsi:type="string">order_id</item>
            <argument name="columns" xsi:type="array">
                <item name="affiliate_information" xsi:type="string">affiliate.affiliate_information</item>

This configuration is specifying that affiliate table will be joined to select from sales_order on sales_order.entity_id = affiliate.order_id and will populate sales_order_grid.affiliate_information column with corresponding value from affiliate.affiliate_information.

After this step, our affiliate_information column in sales_order_grid table is populated with value from affilate table each time order is placed. Still, column will exist only in database, and will not be visible in admin panel.

Configure UI grid component to display the column

Finally, to reflect the column on admin panel grid, we have to extend sales_order_grid ui component by adding a ui configuration file in our module.

It is possible to extend ui configuration fo sales order grid introducing "app/code/<Namespace>/<Module>/view/adminhtml/ui_component/sales_order_grid.xml" file.
Basically it should have the same name and path in relation to module directory as main sales order grid ui component file: "app/code/Magento/Sales/view/adminhtml/ui_component/sales_order_grid.xml"

Put the following xml snippet to the ui configuration file:

<listing ...>
    <columns name="sales_order_columns">
        <column name="affiliate_information">
            <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">Affiliate Information</item>

This will extend sales_order_columns and add a column based on affiliate_information filed, of type text, with translatable label "Affiliate Information".

Bonus: How to ensure sales_order_grid is populated after value is inserted to source table.

There are two approaches to ensure that correct value is present in source table when sales_order_grid is populated:
  • Save value to source table before sales_order_grid refresh is triggered.
  • Trigger sales_order_grid refresh after saving value.
First approach is necessary if value should be saved to source table right on place order action. One of solutions will be to create observer on event that is dispatched when order is saved, but grid is still not refreshed. Such event is "sales_order_save_after".

Second approach is applicable when source table is updated some time after order is placed, or asynchronously. Here you have to call \Magento\Sales\Model\ResourceModel\Grid::refresh method after value is saved to source table.

Final Tips

Be sure to refresh config cache after editing xml files.
For sure provided setting are basic, and there are much more parameters available for install script, di and ui configuration to customize content and look of added column, take time to observe existing install scripts, di and ui configuration files and try adding more parameters for your implementation.

Hope this tutorial was useful for you. Please feel free to provide any feedback in comments or contact me directly.


  1. Thank you!

    Your article is very helpful. Save a lot of time for me!

    1. Thank you for your feedback! Hope you managed to resolve the issue with filter :)

  2. Thanks for the article, it works perfectly here.

    But, I have a doubt. I added 2 columns to the Sales Order Grid, but they appear at the end of the table, after all the other columns. Do you know how can I change the position (order) of this columns that I have added? (I want they to be the second and third column of my grid)
    I searched a lot, found something about sortOrder item in the xml, but didn't work as expected.

    See you.

  3. Hi Gabriel,

    sortOrder does work, however there is additional per-user source of positions.

    There are two ways to see correct ordering based on sortOrder:
    1. Create a new user
    2. Remove entries from ui_bookmark table (values from this table are overriding global sortOrder)

    1. Thank you, this worked.

      But I had some trouble following your tutorial.

      I created my observer, I get the order id and stored it in my table using save_sale_after event as you suggested, but I think the join of my table with sales_order_grid do not work, because the fields updated in my table but not in the sales_order_grid

      I will try to use the 'refresh' method as you said, but I don't want to call it statically, because in his class ( https://github.com/magento/magento2/blob/2.0/app/code/Magento/Sales/Model/ResourceModel/Grid.php ) it have some parameters in the constructor and in the method too.

      If you have any consideration our help, I will be glad (:

    2. Gabriel, sure refresh method is not static, "\Magento\Sales\Model\ResourceModel\Grid::refresh" this is just a reference to method :)

      Go ahead and try to call it. Hope that will resolve your problem!

    3. Not worked and I'm in the first scenario you talked ("Save value to source table before sales_order_grid refresh is triggered"), that is why I think the join of my table with sales_order_grid don't work.

      The di.xml code is correct in your example? I don't have to add columns to sales_order, only to sales_order_grid table, right?

      Because in my observer I inserted data in my table and it is did before the sales_order_grid is refreshed/updated.

      Thanks for the help. (:

    4. yes, you shouldn't add columns to sales_order

  4. Hey, thanks for nice article. Can you explain about position of columns once again as we got stuck with this issue. Every time our newly added fields coming on the last.

  5. HI Column is rendered but i need filter option also where we have to show drop down. is this possible how to load options for select attribute along with the column


  6. I have read your blog and I gathered some needful information from your blog. Keep update your blog. Awaiting for your next update.
    zuan education

  7. This comment has been removed by the author.

  8. Thanks for the article, it works perfectly using "sales_order_save_after".

    But, How to call this method \Magento\Sales\Model\ResourceModel\Grid::refresh


  9. Really cool post, highly informative and professionally written and I am glad to be a visitor of this perfect blog, thank you for this rare info!
    Magento course in chennai

  10. Marvelous and fascinating article. Incredible things you've generally imparted to us. Much obliged. Simply keep making this kind out of the post.
    custom magento development