Magento 2: adding a column to the admin grid

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

Data structure

I solved the problem of creating a referral tree of clients (the client-parent attracts the client-descendant), so I created an additional table tied to customer_entity. In short, the additional table contains the parent-child relationship and information on the tree (the "depth" of the client and the 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 client grid containing information about the parent of the current client and the depth of the client in the tree. The client grid is described in an XML file vendor/magento/module-customer/view/adminhtml/ui_component/customer_listing.xml. We are interested in the node dataSource, and specifically, the name of the data source ( customer_listing_data_source):

customer_listing_data_source
        ...
    

(which of this is the name of the data source - the name attribute or argument node with the name name , it’s 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 the collection, no matter how trite it may sound. Here is a description of the data source with the name customer_listing_data_sourcein the file vendor/magento/module-customer/etc/di.xml:

Magento\Customer\Model\ResourceModel\Grid\Collection
            ...
        

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

Collection modification

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

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

This is another good tradition in Magento - to overcome the increased sluggishness of the application associated with increased flexibility by using these "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 JOIN's ability only in the method \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection::_beforeLoad:

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

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

And he created a class that responds to this event, in which he modified the initial request:

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 the 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

Because I use aliases for data from my own table (prxgtDwnlCustomerDepth and prxgtDwnlParentId), then I can’t be very afraid that some other developer, using this approach, will match me by the name of additional fields (hardly anyone will start calling their data from prxgt ), but this also led to the fact that filtering from the grid stopped working.

Add Column

To redefine the columns in the grid, you need to create an XML file in your module 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 aliases as data field names:

textRangeParent IDtextRangeDepth

Result

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

"Dirty hack" for the filter

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

To make filters for new columns work, I didn’t think of anything better than how to do the inverse transformation "alias" => "table.field" all in the same class by adding JOIN to the initial 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;
}