{"id":10456,"date":"2020-02-03T10:08:47","date_gmt":"2020-02-03T10:08:47","guid":{"rendered":"https:\/\/www.mageworx.com\/blog\/?p=10456"},"modified":"2023-03-17T15:05:04","modified_gmt":"2023-03-17T15:05:04","slug":"how-to-add-column-with-filter-to-magento-2-orders-grid","status":"publish","type":"post","link":"https:\/\/www.mageworx.com\/blog\/how-to-add-column-with-filter-to-magento-2-orders-grid","title":{"rendered":"How to Add Column with Filter to Magento 2 Orders Grid?"},"content":{"rendered":"\n<!-- SEO Ultimate (http:\/\/www.seodesignsolutions.com\/wordpress-seo\/) - Code Inserter module -->\n<!-- Google Tag Manager (noscript) -->\r\n<noscript><iframe src=\"https:\/\/www.googletagmanager.com\/ns.html?id=GTM-5DTCW7B8\"\r\nheight=\"0\" width=\"0\" style=\"display:none;visibility:hidden\"><\/iframe><\/noscript>\r\n<!-- End Google Tag Manager (noscript) -->\n<!-- \/SEO Ultimate -->\n\n<span class=\"span-reading-time rt-reading-time\" style=\"display: block;\"><span class=\"rt-label rt-prefix\">Reading Time: <\/span> <span class=\"rt-time\"> 9<\/span> <span class=\"rt-label rt-postfix\">minutes<\/span><\/span>\n<p>Often, Magento 2 stores administrators require extra options when it comes to using and customizing the orders grid \u2015 filtering based on a specific out-of-the-box parameter can become a real challenge.<br><\/p>\n\n\n\n<p>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\u2019t help out \u2015 naturally, much has changed in Magento since last two years.<br><\/p>\n\n\n\n<p>Based on his question, I offer you to take da look at the solution for a specific case:<br><\/p>\n\n\n\n<p>We need to add a column \u2015 with some regional code of a customer who completed a purchase \u2015 to the orders grid. Additionally, a store administrator must have a possibility to filter orders by this newly added column.<br><\/p>\n\n\n\n<p>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 \u2015 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\u2019s assume:<br><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li> virtual products won\u2019t have a delivery region (\u2018null\u2019). This will help you choose them based on this parameter, <\/li><li> regions will be listed and have a look of codes without their transformation into labels, just as it is in the default Magento 2. <\/li><\/ul>\n\n\n\n<p>*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:<a href=\"https:\/\/github.com\/SiarheyUchukhlebau\/ExtendedOrdersGrid\"> <\/a><a aria-label=\" (opens in a new tab)\" href=\"https:\/\/github.com\/mageworx\/articles-extended-orders-grid\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/github.com\/mageworx\/articles-extended-orders-grid<\/a>.<br><\/p>\n\n\n\n<p>However, despite the lack of time, I do encourage you to read on.? <br><\/p>\n\n\n\n<p>Thus, to add a new column to the orders grid, you need to:<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">1. Create a new module<br><\/h2>\n\n\n\n<p>First, let\u2019s decide on the module and vendor names. Well, no need for me to choose a vendor name \u2015 it\u2019s MageWorx (as if I have a choice, just kidding). I still get to choose the module name. Let it be <em>ExtendedOrdersGrid <\/em>(btw we have a<a href=\"https:\/\/www.mageworx.com\/magento-2-orders-grid.html\"> same-name extension<\/a> 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.? <\/p>\n\n\n\n<p>Let\u2019s create the following directory: `app\/code\/MageWorx\/ExtendedOrdersGrid`. To register a module, we will need some standard files:<br><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&gt; registration.php\n\nphp\n&lt;?php\n\\Magento\\Framework\\Component\\ComponentRegistrar::register(\n    \\Magento\\Framework\\Component\\ComponentRegistrar::MODULE,\n    'MageWorx_ExtendedOrdersGrid',\n    __DIR__\n);\n\n\n&gt; composer.json\n\njson\n{\n    \"name\": \"mageworx\/module-extended-orders-grid\",\n    \"description\": \"Extended Orders Grid Extension\",\n    \"require\": {\n        \"magento\/module-ui\" : \"&gt;=100.1.0 &lt; 102\",\n        \"magento\/module-sales\" : \"&gt;=100.0.0 &lt;103\"\n    },\n    \"type\": \"magento2-module\",\n    \"version\": \"1.0.0\",\n    \"license\": &#91;\n        \"OSL-3.0\",\n        \"AFL-3.0\"\n    ],\n    \"autoload\": {\n        \"files\": &#91; \"registration.php\" ],\n        \"psr-4\": {\n            \"MageWorx\\\\ExtendedOrdersGrid\\\\\": \"\"\n        }\n    }\n}\n\n\n&gt; etc\/module.xml\n\nxml\n&lt;?xml version=\"1.0\"?&gt;\n&lt;config xmlns:xsi=\"http:\/\/www.w3.org\/2001\/XMLSchema-instance\" xsi:noNamespaceSchemaLocation=\"urn:magento:framework:Module\/etc\/module.xsd\"&gt;\n    &lt;module name=\"MageWorx_ExtendedOrdersGrid\" setup_version=\"1.0.0\"&gt;\n        &lt;sequence&gt;\n            &lt;module name=\"Magento_Sales\"\/&gt;\n            &lt;module name=\"Magento_Ui\"\/&gt;\n        &lt;\/sequence&gt;\n    &lt;\/module&gt;\n&lt;\/config&gt;<\/code><\/pre>\n\n\n\n<p>It is important to note that I\u2019ve cut the copyright that gets added in my IDE automatically. Thus, you get to use this code with no trouble. ? <br><\/p>\n\n\n\n<p>Then, run a few commands:<br><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&gt; sudo -u www-data php bin\/magento module:enable MageWorx_ExtendedOrdersGrid\n&gt; sudo -u www-data php bin\/magento setup:upgrade\n<\/code><\/pre>\n\n\n\n<p>And voila! Now, our module can be seen in Magento 2! Oh, if you work on a remote <a href=\"https:\/\/www.mageworx.com\/blog\/how-to-transfer-magento-2-site-from-localhost-to-server\" data-type=\"URL\" data-id=\"https:\/\/www.mageworx.com\/blog\/how-to-transfer-magento-2-site-from-localhost-to-server\" target=\"_blank\" rel=\"noreferrer noopener\">server<\/a>, do not forget to transfer the files before testing.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">2. Add a column to the grid<\/h2>\n\n\n\n<p>Then, using Magento 2 UI, let\u2019s add a new column to the standard grid. For that, create a file:<br><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&gt; view\/adminhtml\/ui_component\/sales_order_grid.xml<\/code><\/pre>\n\n\n\n<p>with the following content (its meaning we will discuss further):<br><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>xml\n&lt;?xml version=\"1.0\" encoding=\"UTF-8\"?&gt;\n&lt;listing xmlns:xsi=\"http:\/\/www.w3.org\/2001\/XMLSchema-instance\" xsi:noNamespaceSchemaLocation=\"urn:magento:module:Magento_Ui:etc\/ui_configuration.xsd\"&gt;\n    &lt;columns name=\"sales_order_columns\"&gt;\n        &lt;column name=\"code\"&gt;\n            &lt;argument name=\"data\" xsi:type=\"array\"&gt;\n                &lt;item name=\"config\" xsi:type=\"array\"&gt;\n                    &lt;item name=\"component\" xsi:type=\"string\"&gt;Magento_Ui\/js\/grid\/columns\/column&lt;\/item&gt;\n                    &lt;item name=\"label\" xsi:type=\"string\" translate=\"true\"&gt;Region Code&lt;\/item&gt;\n                    &lt;item name=\"sortOrder\" xsi:type=\"number\"&gt;60&lt;\/item&gt;\n                    &lt;item name=\"align\" xsi:type=\"string\"&gt;left&lt;\/item&gt;\n                    &lt;item name=\"dataType\" xsi:type=\"string\"&gt;text&lt;\/item&gt;\n                    &lt;item name=\"visible\" xsi:type=\"boolean\"&gt;true&lt;\/item&gt;\n                    &lt;item name=\"filter\" xsi:type=\"string\"&gt;text&lt;\/item&gt;\n                &lt;\/item&gt;\n            &lt;\/argument&gt;\n        &lt;\/column&gt;\n    &lt;\/columns&gt;\n&lt;\/listing&gt;<\/code><\/pre>\n\n\n\n<p>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\u2019ll add some explanations at the end of this paragraph.<br><\/p>\n\n\n\n<p>We used a standard `columns` node to add columns to the grid, where a new column under \u2018code\u2019 name shall be added as well. Next, let\u2019s write the attributes of our column in the grid:<\/p>\n\n\n\n<p>1) <strong>Component<\/strong>. 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:<\/p>\n\n\n\n<p>`vendor\/magento\/module-ui\/view\/base\/web\/js\/grid\/columns\/column.js`<br><\/p>\n\n\n\n<p>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.<br><\/p>\n\n\n\n<p>2) <strong>Label. <\/strong>This is a line with a column name, which will be displayed to an end user. Don\u2019t forget to add it to the i18n localization file, if the `translate` property has been established. ? <br><\/p>\n\n\n\n<p>3) <strong>SortOrder. <\/strong>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.<br><\/p>\n\n\n\n<p>4) <strong>Align. <\/strong>This means aligning the column content. I guess that\u2019s clear.<br><\/p>\n\n\n\n<p>5) <strong>DataType. <\/strong>This is a data type we will manipulate. In our case, that\u2019s 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.<br><\/p>\n\n\n\n<p>6) <strong>Visible. <\/strong>This is nothing else but the column visibility though much depends on whether the grid was previously used or not.<br><\/p>\n\n\n\n<p>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\u2019t expect any miracle.<br><\/p>\n\n\n\n<p>7) <strong>Filter<\/strong>. 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.<\/p>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n<p><a href=\"https:\/\/www.mageworx.com\/magento2-extensions.html?utm_source=MW_blog&amp;utm_medium=banner&amp;utm_campaign=SU_add_column\"><img loading=\"lazy\" decoding=\"async\" width=\"1060\" height=\"200\" class=\"alignnone wp-image-11550 size-full\" alt=\"MageWorx Magento 2 Extensions\" src=\"https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/01\/banner-3-1.png\" srcset=\"https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/01\/banner-3-1.png 1060w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/01\/banner-3-1-600x113.png 600w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/01\/banner-3-1-768x145.png 768w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/01\/banner-3-1-250x47.png 250w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/01\/banner-3-1-696x131.png 696w\" sizes=\"auto, (max-width: 1060px) 100vw, 1060px\" \/><\/a><\/p>\n\n\n<h3 class=\"wp-block-heading\"><strong>Explanations<\/strong><br><\/h3>\n\n\n\n<p>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\u2019s calculated from the `view\/adminhtml\/ui_component\/sales_order_grid.xml` module root.<\/p>\n\n\n\n<p>UI grid itself gets added to the required controller using `vendor\/magento\/module-sales\/view\/adminhtml\/layout\/sales_order_index.xml` layout &nbsp;just as follows:<br><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>xml\n&lt;?xml version=\"1.0\"?&gt;\n&lt;!--\n\/**\n * Copyright \u00a9 Magento, Inc. All rights reserved.\n * See COPYING.txt for license details.\n *\/\n--&gt;\n&lt;page xmlns:xsi=\"http:\/\/www.w3.org\/2001\/XMLSchema-instance\" xsi:noNamespaceSchemaLocation=\"urn:magento:framework:View\/Layout\/etc\/page_configuration.xsd\"&gt;\n    &lt;update handle=\"styles\"\/&gt;\n    &lt;body&gt;\n        &lt;referenceContainer name=\"content\"&gt;\n            &lt;uiComponent name=\"sales_order_grid\"\/&gt;\n        &lt;\/referenceContainer&gt;\n    &lt;\/body&gt;\n&lt;\/page&gt;<\/code><\/pre>\n\n\n\n<p>This means *add this UI component under `sales_order_grid` name to the page content* \u2015 just as in case of blocks. However, instead of blocks, we\u2019ve got `uiComponent` with its attributes and limitations.<br><\/p>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<p>Now, clear Magento 2 cache using the following command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&gt; sudo -u www-data php bin\/magento cache:clean config<\/code><\/pre>\n\n\n\n<p>and check the results in the orders grid:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1782\" height=\"682\" src=\"https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/1-6.png\" alt=\"How to Add Column with Filter to Magento 2 Orders Grid? | MageWorx Magento Blog\" class=\"wp-image-10458\" srcset=\"https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/1-6.png 1782w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/1-6-600x230.png 600w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/1-6-1200x459.png 1200w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/1-6-768x294.png 768w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/1-6-250x96.png 250w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/1-6-696x266.png 696w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/1-6-1068x409.png 1068w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/1-6-1097x420.png 1097w\" sizes=\"auto, (max-width: 1782px) 100vw, 1782px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">3. Add data to the column<\/h2>\n\n\n\n<p>You might have noticed that the column has appeared, but its content looks not just as expected\u2026there is no data displayed there at all. Fair enough, we didn\u2019t add anything, thus no content there.<br><\/p>\n\n\n\n<p>Let\u2019s get down to writing a simple plugin that will help you fill in our column. For that, let\u2019s catch the grid collection query, make a join within the required table and grid. The plugin will look just as follows:<br><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&gt; app\/code\/MageWorx\/ExtendedOrdersGrid\/Plugin\/AddDataToOrdersGrid.php<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>php\n&lt;?php\nnamespace MageWorx\\ExtendedOrdersGrid\\Plugin;\n\n\/**\n * Class AddDataToOrdersGrid\n *\/\nclass AddDataToOrdersGrid\n{\n    \/**\n     * @var \\Psr\\Log\\LoggerInterface\n     *\/\n    private $logger;\n\n    \/**\n     * AddDataToOrdersGrid constructor.\n     *\n     * @param \\Psr\\Log\\LoggerInterface $customLogger\n     * @param array $data\n     *\/\n    public function __construct(\n        \\Psr\\Log\\LoggerInterface $customLogger,\n        array $data = &#91;]\n    ) {\n        $this-&gt;logger   = $customLogger;\n    }\n\n    \/**\n     * @param \\Magento\\Framework\\View\\Element\\UiComponent\\DataProvider\\CollectionFactory $subject\n     * @param \\Magento\\Sales\\Model\\ResourceModel\\Order\\Grid\\Collection $collection\n     * @param $requestName\n     * @return mixed\n     *\/\n    public function afterGetReport($subject, $collection, $requestName)\n    {\n        if ($requestName !== 'sales_order_grid_data_source') {\n            return $collection;\n        }\n\n        if ($collection-&gt;getMainTable() === $collection-&gt;getResource()-&gt;getTable('sales_order_grid')) {\n            try {\n                $orderAddressTableName = $collection-&gt;getResource()-&gt;getTable('sales_order_address');\n                $directoryCountryRegionTableName = $collection-&gt;getResource()-&gt;getTable('directory_country_region');\n                $collection-&gt;getSelect()-&gt;joinLeft(\n                    &#91;'soa' =&gt; $orderAddressTableName],\n                    'soa.parent_id = main_table.entity_id AND soa.address_type = \\'shipping\\'',\n                    null\n                );\n                $collection-&gt;getSelect()-&gt;joinLeft(\n                    &#91;'dcrt' =&gt; $directoryCountryRegionTableName],\n                    'soa.region_id = dcrt.region_id',\n                    &#91;'code']\n                );\n            } catch (\\Zend_Db_Select_Exception $selectException) {\n                \/\/ Do nothing in that case\n                $this-&gt;logger-&gt;log(100, $selectException);\n            }\n        }\n\n        return $collection;\n    }\n}<\/code><\/pre>\n\n\n\n<p>It will refer to the following class:<br><\/p>\n\n\n\n<p>`Magento\\Framework\\View\\Element\\UiComponent\\DataProvider\\CollectionFactory`<br><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&gt; etc\/adminhtml\/di.xml\n \nxml\n&lt;?xml version=\"1.0\"?&gt;\n&lt;config xmlns:xsi=\"http:\/\/www.w3.org\/2001\/XMLSchema-instance\"\n        xsi:noNamespaceSchemaLocation=\"urn:magento:framework:ObjectManager\/etc\/config.xsd\"&gt;\n\t&lt;!-- Plugins --&gt;\n\t&lt;!-- Adds additional data to the orders grid collection --&gt;\n\t&lt;type name=\"Magento\\Framework\\View\\Element\\UiComponent\\DataProvider\\CollectionFactory\"&gt;\n    \t&lt;plugin name=\"mageworx_extended_orders_grid_add_data_to_orders_grid\"\n                type=\"MageWorx\\ExtendedOrdersGrid\\Plugin\\AddDataToOrdersGrid\"\n                sortOrder=\"10\"\n                disabled=\"false\"\/&gt;\n\t&lt;\/type&gt;\n&lt;\/config&gt;\n<\/code><\/pre>\n\n\n\n<p>The plugin runs the following way:<\/p>\n\n\n\n<p>As it will catch absolutely all collections, let\u2019s 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\u2019ve got access to the customer\u2019s order <a href=\"https:\/\/www.mageworx.com\/delivery-date-magento-2.html\" data-type=\"URL\" data-id=\"https:\/\/www.mageworx.com\/delivery-date-magento-2.html\">delivery address<\/a> 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\u2019s connect the \u2018code\u2019 column. This precise column will get output (corresponds to the `column` value in `sales_order_grid.xml`).<\/p>\n\n\n\n<p>Then, clear cache:<br><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&gt; sudo -u www-data php bin\/magento cache:clean config\n<\/code><\/pre>\n\n\n\n<p>Log in the admin panel to the orders grid. If everything has been done correctly, we will see our regional codes displayed:<br><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1807\" height=\"848\" src=\"https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/2-8.png\" alt=\"How to Add Column with Filter to Magento 2 Orders Grid? | MageWorx Magento Blog\" class=\"wp-image-10459\" srcset=\"https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/2-8.png 1807w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/2-8-600x282.png 600w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/2-8-1200x563.png 1200w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/2-8-768x360.png 768w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/2-8-250x117.png 250w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/2-8-696x327.png 696w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/2-8-1068x501.png 1068w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/06\/2-8-895x420.png 895w\" sizes=\"auto, (max-width: 1807px) 100vw, 1807px\" \/><\/figure>\n\n\n\n<p>Free access to the module is available on GitHub: <a aria-label=\" (opens in a new tab)\" href=\"https:\/\/github.com\/mageworx\/articles-extended-orders-grid\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/github.com\/mageworx\/articles-extended-orders-grid<\/a>.<br><\/p>\n\n\n\n<p><strong>Important sidenote!<\/strong><br><\/p>\n\n\n\n<p>To add any data to this column, it is required to get \u2018your_column_name\u2019 column and all the needed data added when creating a collection. That is, you need to do something different \u2015 replace the table name and write your own join. Also, don\u2019t forget to edit the column name in the `sales_order_grid.xml` file if required.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How to Add an Extra Column?<\/h2>\n\n\n\n<p>If you decide to add one more column, please, follow the guidelines described in the following Git commit: <a href=\"https:\/\/github.com\/mageworx\/articles-extended-orders-grid\/commit\/d31c364a25ce493ab64731c5ca0481e146dbbac3\">https:\/\/github.com\/mageworx\/articles-extended-orders-grid\/commit\/d31c364a25ce493ab64731c5ca0481e146dbbac3<\/a><\/p>\n\n\n\n<p>There, we&#8217;ve added the  <code>telephone<\/code> column to the grid from the  <code>sales_order_address<\/code> table for the address of the <code>shipping<\/code>  type. As you can see in the commit code, no significant code modifications were required.<\/p>\n\n\n\n<p>Additionally, these columns can be successfully exported from the standard &#8216;order grid&#8217; interface.<\/p>\n\n\n\n<p>Here is how the grid with the &#8216;telephone&#8217; column looks like:   <\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1375\" height=\"542\" src=\"https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.40.37.png\" alt=\" How to Add Column with Filter to Magento 2 Orders Grid? | MageWorx Magento Blog\" class=\"wp-image-11360\" srcset=\"https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.40.37.png 1375w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.40.37-600x237.png 600w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.40.37-1200x473.png 1200w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.40.37-768x303.png 768w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.40.37-250x99.png 250w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.40.37-696x274.png 696w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.40.37-1068x421.png 1068w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.40.37-1065x420.png 1065w\" sizes=\"auto, (max-width: 1375px) 100vw, 1375px\" \/><\/figure>\n\n\n\n<p>In the screenshot below, you can see the same orders exported to a .csv file:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1284\" height=\"247\" src=\"https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.41.56.png\" alt=\" How to Add Column with Filter to Magento 2 Orders Grid? | MageWorx Magento Blog\" class=\"wp-image-11361\" srcset=\"https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.41.56.png 1284w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.41.56-600x115.png 600w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.41.56-1200x231.png 1200w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.41.56-768x148.png 768w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.41.56-250x48.png 250w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.41.56-696x134.png 696w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2019\/12\/Screen-Shot-2019-12-04-at-16.41.56-1068x205.png 1068w\" sizes=\"auto, (max-width: 1284px) 100vw, 1284px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">[Update] How to Add a Column with Order Items Info?<\/h2>\n\n\n\n<p>As you know, all orders have items qty, starting from one to out beyond number (yeah, merchants&#8217; dream). But how can we display that info in the orders grid to start a search or run an analysis more easily?<\/p>\n\n\n\n<p>We can&#8217;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\u2026 We don&#8217;t need that mess either. <\/p>\n\n\n\n<p>Thus, I&#8217;ll try to explain how we can do that the right way (in my opinion).<\/p>\n\n\n\n<p>Suppose we need a &#8216;Product Name&#8217; data column in the orders grid.<\/p>\n\n\n\n<p>First, let&#8217;s add a new column in the <code>sales_order_grid<\/code> definition, as we did it earlier:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\n    &lt;columns&gt;\n        ....\n        &lt;column name=\"name\"&gt;\n            &lt;argument name=\"data\" xsi:type=\"array\"&gt;\n                &lt;item name=\"config\" xsi:type=\"array\"&gt;\n                    &lt;item name=\"component\" xsi:type=\"string\"&gt;Magento_Ui\/js\/grid\/columns\/column&lt;\/item&gt;\n                    &lt;item name=\"filter\" xsi:type=\"string\"&gt;text&lt;\/item&gt;\n                    &lt;item name=\"label\" xsi:type=\"string\" translate=\"true\"&gt;Product Name&lt;\/item&gt;\n                    &lt;item name=\"visible\" xsi:type=\"boolean\"&gt;false&lt;\/item&gt;\n                    &lt;item name=\"sortOrder\" xsi:type=\"number\"&gt;70&lt;\/item&gt;\n                &lt;\/item&gt;\n            &lt;\/argument&gt;\n        &lt;\/column&gt;\n    &lt;\/columns&gt;\n<\/code><\/pre>\n\n\n\n<p>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&#8217;s create a new method in the <code>mageworx_extended_orders_grid_add_data_to_orders_grid<\/code> plugin:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\n    \/**\n     * Adds products name column to the orders grid collection\n     *\n     * @param OrderGridCollection $collection\n     * @return OrderGridCollection\n     *\/\n    private function addProductsNameColumn(OrderGridCollection $collection): OrderGridCollection\n    {\n        return $collection;\n    }\n<\/code><\/pre>\n\n\n\n<p>and name that method inside the original method body:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\nif ($collection-&gt;getMainTable() === $collection-&gt;getResource()-&gt;getTable('sales_order_grid')) {\n    try {\n        $orderAddressTableName = $collection-&gt;getResource()-&gt;getTable('sales_order_address');\n        ...\n\n        \/\/ Add product's name column\n        $this-&gt;addProductsNameColumn($collection);\n    } catch (\\Zend_Db_Select_Exception $selectException) {\n    ...        \n<\/code><\/pre>\n\n\n\n<p>To obtain the desired data in one column, we must create a sub-select with two columns: <code>order_id<\/code> and <code>name<\/code> (product name) in the individual select, which can be joined to the main collection later:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\n\/\/ Get original table name\n$orderItemsTableName = $collection-&gt;getResource()-&gt;getTable('sales_order_item');\n\/\/ Create new select instance\n$itemsTableSelectGrouped = $collection-&gt;getConnection()-&gt;select();\n\/\/ Add table with columns which must be selected (skip useless columns)\n$itemsTableSelectGrouped-&gt;from(\n    $orderItemsTableName,\n    &#91;\n        'name'     =&gt; new \\Zend_Db_Expr('GROUP_CONCAT(DISTINCT name SEPARATOR \\',\\')'),\n        'order_id' =&gt; 'order_id'\n    ]\n);\n\/\/ Group our select to make only one column for one order\n$itemsTableSelectGrouped-&gt;group('order_id');\n<\/code><\/pre>\n\n\n\n<p><strong>Clarifications:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><code>$collection-&gt;getConnection()-&gt;select()<\/code> line will create a new <code>Magento\\Framework\\Db\\Select<\/code> instance. <br> This is required because we can&#8217;t use the original select from the collection as it has its own data inside, and any modifications will lead to errors.<\/li><li><code>name<\/code> column must have all product names for the specified order, i.e., it must be grouped using the<code>\\Zend_Db_Expr('GROUP_CONCAT(DISTINCT name SEPARATOR \\',\\')')<\/code> expression. For that purpose, we are adding <code>group('order_id')<\/code> to the select later. Without grouping, we can&#8217;t use the <code>GROUP_CONCAT<\/code> function.<\/li><\/ul>\n\n\n\n<p>Now, we can add our sub-select to the main collection and it will be the logical end of the <code>addProductsNameColumn<\/code> method:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\n\/\/ Add our sub-select to main collection with only one column: name\n$collection-&gt;getSelect()\n        -&gt;joinLeft(\n            &#91;'soi' =&gt; $itemsTableSelectGrouped],\n            'soi.order_id = main_table.entity_id',\n            &#91;'name']\n        );\n\nreturn $collection;<\/code><\/pre>\n\n\n\n<p><strong>Clarifications:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><code>soi<\/code> is an alias for our pseudo-table.<\/li><li><code>order_id<\/code> is a key, which we use to link our main table (grid) to the order items data.<\/li><li><code>['name']<\/code> is the only column, which gets added to the result, because we don&#8217;t need other information.<\/li><\/ul>\n\n\n\n<p>The final result is available in the official repository of that example. <br> Here&#8217;s the link to the specific commit: <a aria-label=\"https:\/\/github.com\/mageworx\/articles-extended-orders-grid\/commit\/0cdffcd4ba66cacb2fd857ba7626fdbcfc0d6fe3 (opens in a new tab)\" href=\"https:\/\/github.com\/mageworx\/articles-extended-orders-grid\/commit\/0cdffcd4ba66cacb2fd857ba7626fdbcfc0d6fe3\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/github.com\/mageworx\/articles-extended-orders-grid\/commit\/0cdffcd4ba66cacb2fd857ba7626fdbcfc0d6fe3<\/a><\/p>\n\n\n\n<p>Here&#8217;s how that column looks on our staging host:<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"1229\" height=\"944\" src=\"https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.22.05.png\" alt=\"?\u200d? How to Add Column with Filter to Magento 2 Orders Grid? | MageWorx Magento Blog\" class=\"wp-image-11606\" srcset=\"https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.22.05.png 1229w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.22.05-600x461.png 600w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.22.05-1200x922.png 1200w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.22.05-768x590.png 768w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.22.05-250x192.png 250w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.22.05-80x60.png 80w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.22.05-696x535.png 696w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.22.05-1068x820.png 1068w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.22.05-547x420.png 547w\" sizes=\"auto, (max-width: 1229px) 100vw, 1229px\" \/><\/figure><\/div>\n\n\n\n<p>And here is the result of the export (CSV):<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"1591\" height=\"281\" src=\"https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.24.56.png\" alt=\"And here is the result of the export (CSV):\" class=\"wp-image-11607\" srcset=\"https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.24.56.png 1591w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.24.56-600x106.png 600w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.24.56-1200x212.png 1200w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.24.56-768x136.png 768w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.24.56-250x44.png 250w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.24.56-696x123.png 696w, https:\/\/www.mageworx.com\/blog\/wp-content\/uploads\/2020\/02\/Screen-Shot-2020-02-03-at-15.24.56-1068x189.png 1068w\" sizes=\"auto, (max-width: 1591px) 100vw, 1591px\" \/><\/figure><\/div>\n\n\n\n<p>Here&#8217;s how the query looks like when trying to search orders with &#8220;Black&#8221; products (on our dev host):<br>SELECT <code>main_table<\/code>.*, <code>soat<\/code>.<code>telephone<\/code>, <code>dcrt<\/code>.<code>code<\/code>, <code>soi<\/code>.<code>name<\/code> FROM <code>sales_order_grid<\/code> AS <code>main_table<\/code><br>LEFT JOIN <code>sales_order_address<\/code> AS <code>soat<\/code> ON soat.parent_id = main_table.entity_id AND soat.address_type = &#8216;shipping&#8217;<br>LEFT JOIN <code>directory_country_region<\/code> AS <code>dcrt<\/code> ON soat.region_id = dcrt.region_id<br>LEFT JOIN (SELECT GROUP_CONCAT(DISTINCT name SEPARATOR &#8216;,&#8217;) AS <code>name<\/code>, <code>sales_order_item<\/code>.<code>order_id<\/code> FROM <code>sales_order_item<\/code> GROUP BY <code>order_id<\/code>) AS <code>soi<\/code> ON soi.order_id = main_table.entity_id <br>WHERE <code>soi<\/code>.<code>name<\/code> LIKE &#8216;%Black%&#8217;<\/p>\n\n\n\n<p>Evidently, this is not the fastest way to output data. However, that&#8217;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. <br><br>For that purpose, we add the group (&#8216;order_id&#8217;) to our select  (at the end of the method code).<br>The select means an element. <br>The group means the select method.<\/p>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<p class=\"has-text-align-center\"><strong><em>I guess that\u2019s it. Should you have any questions or requests, please leave a comment in the comment field.<\/em><\/strong><br><\/p>\n","protected":false},"excerpt":{"rendered":"<p><span class=\"span-reading-time rt-reading-time\" style=\"display: block;\"><span class=\"rt-label rt-prefix\">Reading Time: <\/span> <span class=\"rt-time\"> 9<\/span> <span class=\"rt-label rt-postfix\">minutes<\/span><\/span>Often, Magento 2 stores administrators require extra options when it comes to using and customizing the orders grid \u2015 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 [&hellip;]<\/p>\n","protected":false},"author":15,"featured_media":12384,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[255,425],"tags":[436],"class_list":{"0":"post-10456","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-magento-2","8":"category-magento-how-tos","9":"tag-developer-diaries"},"_links":{"self":[{"href":"https:\/\/www.mageworx.com\/blog\/wp-json\/wp\/v2\/posts\/10456","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mageworx.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mageworx.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mageworx.com\/blog\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mageworx.com\/blog\/wp-json\/wp\/v2\/comments?post=10456"}],"version-history":[{"count":13,"href":"https:\/\/www.mageworx.com\/blog\/wp-json\/wp\/v2\/posts\/10456\/revisions"}],"predecessor-version":[{"id":16662,"href":"https:\/\/www.mageworx.com\/blog\/wp-json\/wp\/v2\/posts\/10456\/revisions\/16662"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.mageworx.com\/blog\/wp-json\/wp\/v2\/media\/12384"}],"wp:attachment":[{"href":"https:\/\/www.mageworx.com\/blog\/wp-json\/wp\/v2\/media?parent=10456"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mageworx.com\/blog\/wp-json\/wp\/v2\/categories?post=10456"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mageworx.com\/blog\/wp-json\/wp\/v2\/tags?post=10456"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}