Magento 2: Adding a column to the admin grid

Under the cut, there is an example of adding an additional column in the grid of the Magento 2 admin panel with data from the table associated with the main grid table and a dirty hack for the filter to work on the additional column. I admit that this is not quite "Magento 2 way", but it somehow works, and therefore - has the right to exist.

Data structure

I solved the problem of forming a referral client tree (the client client attracts a descendant client), so I created an additional table tied to the customer_entity . In short, the additional table contains the parent-child relationship and information on the tree (client’s “depth” and path to the client in the tree).

 CREATE TABLE prxgt_dwnl_customer ( customer_id int(10) UNSIGNED NOT NULL COMMENT 'Reference to the customer.', parent_id int(10) UNSIGNED NOT NULL COMMENT 'Reference to the customer''s parent.', depth int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Depth of the node in the tree.', path varchar(255) NOT NULL COMMENT 'Path to the node - /1/2/3/.../' PRIMARY KEY (customer_id), CONSTRAINT FK_CUSTOMER FOREIGN KEY (customer_id) REFERENCES customer_entity (entity_id) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT FK_PARENT FOREIGN KEY (parent_id) REFERENCES prxgt_dwnl_customer (customer_id) ON DELETE RESTRICT ON UPDATE RESTRICT ) 

UI Component

My goal was 2 additional columns to the grid of clients, containing information about the parent of the current client and the depth of the client in the tree. The client grid is described in the vendor/magento/module-customer/view/adminhtml/ui_component/customer_listing.xml XML file. We are interested in the dataSource node, and specifically in the data source name ( customer_listing_data_source ):

 <dataSource name="customer_listing_data_source"> <argument name="dataProvider" xsi:type="configurableObject"> <argument name="name" xsi:type="string">customer_listing_data_source</argument> ... </argument> </dataSource> 

(what of this is the name of the data source - the name attribute or the argument node with the name name is hard to say, since Magento since the first version there is a good tradition to use the same names for different types of elements to keep developers in good shape)

Data provider

The data source for the grid is a collection, no matter how trite it sounds. Here is a description of a data source named customer_listing_data_source in the file vendor/magento/module-customer/etc/di.xml :

 <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory"> <arguments> <argument name="collections" xsi:type="array"> <item name="customer_listing_data_source" xsi:type="string">Magento\Customer\Model\ResourceModel\Grid\Collection</item> ... </argument> </arguments> </type> 

That is, the class that supplies data for the client grid is \Magento\Customer\Model\ResourceModel\Grid\Collection .

Modification of the collection

If you get into the collection with a debugger, you can see that the SQL query for retrieving data looks like this:

 SELECT `main_table`.* FROM `customer_grid_flat` AS `main_table` 

This is another good tradition in Magento - to overcome the increased slowness of the application associated with increased flexibility, the ways of using such "index tables". In the case of clients, there is a flat-table, it is quite possible that one could integrate into it, but I was looking for a more universal way. I needed a JOIN.

I found the JOIN option only in the \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection::_beforeLoad :

 protected function _beforeLoad() { ... $this->_eventManager->dispatch('core_collection_abstract_load_before', ['collection' => $this]); ... } 

I subscribed in my modules to the core_collection_abstract_load_before event ( etc/events.xml ):

 <event name="core_collection_abstract_load_before"> <!-- Add additional attributes to the Customer Grid in adminhtml. --> <observer name="praxigento_donwlilne_on_core_collection_abstract_load_before" instance="Praxigento\Downline\Observer\CoreCollectionAbstractLoadBefore"/> </event> 

And I created a class that responds to this event, in which I modified the original query:

 class CoreCollectionAbstractLoadBefore implements ObserverInterface { const AS_FLD_CUSTOMER_DEPTH = 'prxgtDwnlCustomerDepth'; const AS_FLD_PARENT_ID = 'prxgtDwnlParentId'; const AS_TBL_CUST = 'prxgtDwnlCust'; public function execute(\Magento\Framework\Event\Observer $observer) { $collection = $observer->getData('collection'); if ($collection instanceof \Magento\Customer\Model\ResourceModel\Grid\Collection) { $query = $collection->getSelect(); $conn = $query->getConnection(); /* LEFT JOIN `prxgt_dwnl_customer` AS `prxgtDwnlCust` */ $tbl = [self::AS_TBL_CUST => $conn->getTableName('prxgt_dwnl_customer')]; $on = self::AS_TBL_CUST . 'customer_id.=main_table.entity_id'; $cols = [ self::AS_FLD_CUSTOMER_DEPTH => 'depth', self::AS_FLD_PARENT_ID => 'parent_id' ]; $query->joinLeft($tbl, $on, $cols); $sql = (string)$query; /* dirty hack for filters goes here ... */ } return; } } 

As a result, after modification, the SQL query began to look something like this:

 SELECT `main_table`.*, `prxgtDwnlCust`.`depth` AS `prxgtDwnlCustomerDepth` `prxgtDwnlCust`.`parent_id` AS `prxgtDwnlParentId` FROM `customer_grid_flat` AS `main_table` LEFT JOIN `prxgt_dwnl_customer` AS `prxgtDwnlCust` ON prxgtDwnlCust.customer_id = main_table.entity_id 

Since I use aliases for the data from "my" table (prxgtDwnlCustomerDepth and prxgtDwnlParentId), then I can not be very afraid that some other developer, using a similar approach, will coincide with me by naming additional fields (hardly anyone starts calling his data from prxgt ), but this also caused filtering from the grid to stop working.

Adding a column

To add columns to the grid, you need to create in your module an XML file with the same name as the one that describes the original UI component ( view/adminhtml/ui_component/customer_listing.xml ), and create additional columns in it using the data field names aliases:

 <?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="customer_columns"> <column name="prxgtDwnlParentId"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">textRange</item> <item name="label" xsi:type="string" translate="true">Parent ID</item> </item> </argument> </column> <column name="prxgtDwnlCustomerDepth"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">textRange</item> <item name="label" xsi:type="string" translate="true">Depth</item> </item> </argument> </column> </columns> </listing> 

Result

(I moved the speakers with my hands and hid too much - a great feature in the new Magento)

Dirty hack for filter

EDITED : A more direct solution is through plugins using the $collection->addFilterToMap(...) method. In this case, the collection is changed immediately settled its creation, and not immediately prior to its use.

To make filters on new columns work, I didn’t think of anything better than how to do the reverse alias conversion => table.field, all in the same class by adding JOIN to the original query ( CoreCollectionAbstractLoadBefore ):

 public function execute(\Magento\Framework\Event\Observer $observer) { ... /* the dirty hack */ $where = $query->getPart('where'); $replaced = $this->_replaceAllAliasesInWhere($where); $query->setPart('where', $replaced); ... } protected function _replaceAllAliasesInWhere($where) { $result = []; foreach ($where as $item) { $item = $this->_replaceAliaseInWhere($item, self::AS_FLD_CUSTOMER_DEPTH, self::AS_TBL_CUST, 'depth'); $item = $this->_replaceAliaseInWhere($item, self::AS_FLD_PARENT_ID, self::AS_TBL_CUST, 'parent_id'); $result[] = $item; } return $result; } protected function _replaceAliaseInWhere($where, $fieldAlias, $tableAlias, $fieldName) { $search = "`$fieldAlias`"; $replace = "`$tableAlias`.`$fieldName`"; $result = str_replace($search, $replace, $where); return $result; }