The most given task for all Magento programmers should be adding more columns with custom data to the Magento base grid. In this blog, we are going to learn how to add columns to the grid by joining tables. Thanks to these custom columns, data will be rendered from a custom joined table.

In this blog, we will create a custom table whose data will be added to the Sales order grid. I assume we all know how to create a basic module, so let’s just go straight to the main point, shall we? Here are three simple steps to add another column to Magento sales order grid.

Step 1: Create a table

In order to show whichever data we want, we’ll store it in our own table. Here, we’ll simply call it magenest_custom_column  with only two columns, id and value.

$installer->getTable('magenest_custom_column')
)
   ->addColumn(
       'id',
       Table::TYPE_INTEGER,
       null,
       [
           'identity' => true,
           'nullable' => false,
           'primary' => true,
           'unsigned' => true,
       ],
       'Increment ID'
   )
  ->addColumn(
       'value',
       Table::TYPE_TEXT,
       100,
       ['nullable => false'],
       'Custom Column Value'
   )
   ->setComment('Custom Column Table');
$installer->getConnection()->createTable($table);

Step 2: Join custom table with our wishlist one

As we already know, listing grid display data from table, which is why in order to add our column(s) we must join two tables with each other.

How to do it, you may ask. A simple plugin file will do the trick.

<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
   <plugin name="grid_custom_column" type="Magenest\Grids\Plugin\GridJoinCollection" sortOrder="5" />
</type>

Before getting to know what this plugin does, we’ll take a quick peek at what getReport function does inside CollectionFactory class.

/**
* Get report collection
*
* @param string $requestName
* @return Collection
* @throws \Exception
*/
public function getReport($requestName)
{
   if (!isset($this->collections[$requestName])) {
       throw new \Exception(sprintf('Not registered handle %s', $requestName));
   }
   $collection = $this->objectManager->create($this->collections[$requestName]);
   if (!$collection instanceof Collection) {
       throw new \Exception(sprintf('%s is not of Collection type.', $requestName));
   }
   return $collection;
}

As we can see here, this function takes in a $requestName as a parameter and return collection of given request. Consequently, we’ll modify this function so that it will join table based on given table requested.

public function afterGetReport(
   CollectionFactory $subject,
   $collection,
   $requestName
) {
   if ($requestName == 'sales_order_grid_data_source') {
           $select = $collection->getSelect();
           $select->joinLeft(
               ["secondTable" => $collection->getTable("magenest_custom_column")],
               'main_table.increment_id = secondTable.id',
               array('value')
           );
   }   return $collection;
}

Step 3: Add column using ui_component

After all the hard work, the last thing we need to do now is to add our column to the grid. Based on whichever grid you want, you can add your custom column just by creating a UI component file with the exact same name as ui grid file. Particularly, to execute this, we only need to place these ui component files inside our module view/adminhtml/ui_component folder.

Order grid will be written under the file sales_order_grid.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="value">
           <argument name="data" xsi:type="array">
               <item name="config" xsi:type="array">
                   <item name="sortOrder" xsi:type="number">999</item>
                   <item name="filter" xsi:type="string">text</item>
                   <item name="label" translate="true" xsi:type="string">Order Custom Column</item>
               </item>
           </argument>
       </column>
   </columns>
</listing>

After that, clear cache and go to the admin site and see the magic for yourself. The result should look something like this.

Add custom columns

CONCLUSION

In this blog, I’ve shown you a simple way to add more columns to Sales order grid by joining a custom table with your own data.

Happy coding!