Skip to content

sales_order_grid

Jump to bottom
ProcessEight edited this page Nov 26, 2020 · 4 revisions

Adding a column to the sales order grid

Tested against Magento 2.3.5.

Questions

  • How to add a new column to any and all grids in the admin?
    • Add a new column
    • Different methods of populating it
    • Making it searchable/filterable
    • Making it sortable

Overview

Basically, to add a column to sales order grid you have to perform 3 simple steps:

  • Add a 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’s configuration to display the column in the grid

Sales order grid in Magento 2 can be accessed in the 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.

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

Basically, to add a column to sales order grid you have to perform 3 simple steps:

  • Add a 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’s configuration to display the column in the grid

Let’s go through this implementation step by step.

Preconditions

First of all, ensure you have your column in the database table, and it is mapped to any field of sales_order table. For my example, let’s assume that there is the pavingdirect_ordertypes_order_to_order_type table consisting of two columns: order_id and order_type_id. This table is used to store some order type related to the particular order. It would be nice to display this information in the “Order Type” column in the 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, this script should be updated in the same module, where the pavingdirect_ordertypes_order_to_order_type table was added.

The following code fragment will do the trick:

$setup->getConnection()->addColumn(
    $setup->getTable('sales_order_grid'),
    'order_type_id',
    [
        'type' => Table::TYPE_TEXT,
        'comment' => 'Order Type'
    ]
);

Just add it to app/code/<your_namespace>/<your_module>/Setup/InstallSchema.php file and it will create a column named order_type_id, of type text, with a comment “Order Type” to sales_order_grid during installation.

Don’t worry about the 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 the database, a 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, order_type_id 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.

At this stage, it would be good to understand how sales_order_grid table is populated.

When an 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 the \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 the mentioned insert from select, we have to extend di configuration creating the app/code/<Namespace>/<Module>/etc/adminhtml/di.xml file.

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

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

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

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

Configure UI grid component to display the column

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

It is possible to extend UI configuration for sales order grid introducing the 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 the 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="order_type_id">
            <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">Order Type</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

This will extend sales_order_columns and add a column based on order_type_id field, of type text, with a translatable label “Order Type”.

Ensuring sales_order_grid is populated after a value is inserted into the source table.

There are two approaches to ensure that the correct value is present in the 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.

The first approach is necessary if a value should be saved to source table right on place order action. One of the solutions will be to create an observer on the event that is dispatched when an order is saved, but the grid is still not refreshed. That event is sales_order_save_after.

The second approach is applicable when the source table is updated sometime after an order is placed, or asynchronously. Here you have to call the \Magento\Sales\Model\ResourceModel\Grid::refresh method after a value is saved to the source table.

Populating created sales_order_grid column for existing order

For upgrading existing data either install (for the first release of your module) or upgrade script should be created.

Here is an example of an upgrade script that is populating the sales_order_grid.order_type_id column from the pavingdirect_ordertypes_order_to_order_type.order_type_id column for 1.0.1 version of the module.

Upgrade script example

For this upgrade script to be executed, you have to increase module version in module.xml (from 1.0.0 to 1.0.1) and run the bin/magento setup:upgrade command.

Final Tips

Be sure to refresh config cache after editing XML files.

The settings described here are only the basics, actually, there are much more parameters available for install script, DI and UI configuration to customize content and appearance of the added column, take time to observe existing install scripts, DI and UI configuration files and try adding more parameters to your implementation.

Originally published at www.sivaschenko.com.

Refreshing the data in sales_order_grid

  • On order submit in admin, sales_order_process_relation event is triggered
  • Which calls \Magento\Sales\Model\ResourceModel\Grid::refresh.
  • Which generates an INSERT INTO sales_order_grid SELECT ... SQL query, which populates the sales_order_grid table
  • The SELECT part of that query looks like this:
SELECT sales_order.entity_id                                     AS `entity_id`,
       sales_order.status                                        AS `status`,
       sales_order.store_id                                      AS `store_id`,
       sales_order.store_name                                    AS `store_name`,
       sales_order.customer_id                                   AS `customer_id`,
       sales_order.base_grand_total                              AS `base_grand_total`,
       sales_order.base_total_paid                               AS `base_total_paid`,
       sales_order.grand_total                                   AS `grand_total`,
       sales_order.total_paid                                    AS `total_paid`,
       sales_order.increment_id                                  AS `increment_id`,
       sales_order.base_currency_code                            AS `base_currency_code`,
       sales_order.order_currency_code                           AS `order_currency_code`,
       TRIM(CONCAT_WS(' ', IF(`sales_shipping_address`.`firstname` <> '', `sales_shipping_address`.`firstname`, NULL),
                      IF(`sales_shipping_address`.`lastname` <> '', `sales_shipping_address`.`lastname`,
                         NULL)))                                 AS `shipping_name`,
       TRIM(CONCAT_WS(' ', IF(`sales_billing_address`.`firstname` <> '', `sales_billing_address`.`firstname`, NULL),
                      IF(`sales_billing_address`.`lastname` <> '', `sales_billing_address`.`lastname`,
                         NULL)))                                 AS `billing_name`,
       sales_order.created_at                                    AS `created_at`,
       sales_order.updated_at                                    AS `updated_at`,
       TRIM(CONCAT_WS(' ', IF(`sales_billing_address`.`company` <> '', `sales_billing_address`.`company`, NULL),
                      IF(`sales_billing_address`.`street` <> '', `sales_billing_address`.`street`, NULL),
                      IF(`sales_billing_address`.`city` <> '', `sales_billing_address`.`city`, NULL),
                      IF(`sales_billing_address`.`region` <> '', `sales_billing_address`.`region`, NULL),
                      IF(`sales_billing_address`.`postcode` <> '', `sales_billing_address`.`postcode`,
                         NULL)))                                 AS `billing_address`,
       TRIM(CONCAT_WS(' ', IF(`sales_shipping_address`.`company` <> '', `sales_shipping_address`.`company`, NULL),
                      IF(`sales_shipping_address`.`street` <> '', `sales_shipping_address`.`street`, NULL),
                      IF(`sales_shipping_address`.`city` <> '', `sales_shipping_address`.`city`, NULL),
                      IF(`sales_shipping_address`.`region` <> '', `sales_shipping_address`.`region`, NULL),
                      IF(`sales_shipping_address`.`postcode` <> '', `sales_shipping_address`.`postcode`,
                         NULL)))                                 AS `shipping_address`,
       sales_order.shipping_description                          AS `shipping_information`,
       sales_order.customer_email                                AS `customer_email`,
       sales_order.customer_group_id                             AS `customer_group`,
       sales_order.base_subtotal                                 AS `subtotal`,
       sales_order.base_shipping_amount                          AS `shipping_and_handling`,
       TRIM(CONCAT_WS(' ', IF(`sales_order`.`customer_firstname` <> '', `sales_order`.`customer_firstname`, NULL),
                      IF(`sales_order`.`customer_lastname` <> '', `sales_order`.`customer_lastname`,
                         NULL)))                                 AS `customer_name`,
       sales_order_payment.method                                AS `payment_method`,
       sales_order.total_refunded                                AS `total_refunded`,
       sales_order.admin_user                                    AS `admin_user`,
       sales_order.delivery_date                                 AS `delivery_date`,
       sales_order.delivery_slot                                 AS `delivery_slot`,
       pavingdirect_ordertypes_order_to_order_type.order_type_id AS `order_type_id`
FROM `sales_order`
         LEFT JOIN `sales_order_address` AS `sales_shipping_address`
                   ON sales_order.shipping_address_id = sales_shipping_address.entity_id
         LEFT JOIN `sales_order_address` AS `sales_billing_address`
                   ON sales_order.billing_address_id = sales_billing_address.entity_id
         LEFT JOIN `sales_order_payment` ON sales_order.entity_id = sales_order_payment.parent_id
         LEFT JOIN `pavingdirect_ordertypes_order_to_order_type` AS `order_type_id`
                   ON sales_order.entity_id = order_type_id.order_id

Which is pretty gnarly.

  • The LEFT JOINS in there are defined in XML (of course). Most of them are defined in the core file <Magento_Sales>/etc/di.xml
  • This file must be in the global area (i.e. etc/di.xml) because that is where the version in Magento_Sales module is defined and which our XML will be merged into. If it is located in etc/adminhtml/di.xml, then the XML from <Magento_Sales>/etc/di.xml won't be merged in (different app area!). When creating an order, the sales_order_process_relation event is dispatched, which tries to update the sales_order_grid by generating an SQL query to do so. Running this query will trigger an SQL error because the data from the other joins needed to populate the sales_order_grid table won't be present.
  • The column_alias (order_type_id in this example) must be the exact name of the column in sales_order_grid, otherwise the SQL query generated to populate that table will fail, because the INSERT INTO sales_order_grid (order_type_id) and ON DUPLICATE KEY UPDATE order_type_id = VALUES(order_type_id) parts of the query do not accept aliases, only the original column names for the destination table (sales_order_grid in this example). It doesn't matter whether MySQL actually supports this, because the M2 code (which generates this part of the query) doesn't. So a query like this will work, but cannot be generated by M2:
# Manually corrected query, confirmed working:
INSERT INTO `sales_order_grid` (`order_type_id`)
SELECT order_type_alias.order_type_id AS `order_type_id_alias`
FROM `sales_order`
         LEFT JOIN `pavingdirect_ordertypes_order_to_order_type` AS `order_type_alias`
                   ON sales_order.entity_id = order_type_alias.order_id
WHERE (sales_order.entity_id = '4384')
ON DUPLICATE KEY UPDATE order_type_id = VALUES(`order_type_id`);

Magento instead generates something like this, which is broken:

INSERT INTO `sales_order_grid` (`order_type_id_alias`)
SELECT order_type_alias.order_type_id AS `order_type_id_alias`
FROM `sales_order`
         LEFT JOIN `pavingdirect_ordertypes_order_to_order_type` AS `order_type_alias`
                   ON sales_order.entity_id = order_type_alias.order_id
WHERE (sales_order.entity_id = '4384')
ON DUPLICATE KEY UPDATE order_type_id_alias = VALUES(`order_type_id_alias`);
# Column order_type_id_alias does not exist in sales_order_grid, so this query will fail with SQLSTATE[42S22]: Column not found: 1054 Unknown column 'order_type_id_alias' in 'field list' error
  • sales_order_place_after
  • sales_model_service_quote_submit_success
$this->eventManager->dispatch('checkout_submit_all_after', ['order' => $order, 'quote' => $quote]);
$this->eventManager->dispatch('sales_model_service_quote_submit_success',['order' => $order, 'quote' => $quote]);
$this->_eventManager->dispatch('sales_order_place_after', ['order' => $this]);
$this->eventManager->dispatch('sales_order_process_relation',['object' => $object]);

Questions

  • What is the role of the cron job sales_grid_order_async_insert?

Further reading

Copyright

© 2021 ProcessEight

Clone this wiki locally