Join Two Tables Using a UI Grid Component in Magento 2

Creating a fully functional grid using the data from your database table in the Magento admin is no easy task. In fact, it can become more complicated when you need to join two tables using a UI grid component in Magento 2.

If you added a grid component using the step by step guide, you’ve probably defined a <virtualType> grid collection class. The configuration for this within the module’s di.xml file might look like the following.

<virtualType name="[Vendor]\[Module]\Model\ResourceModel\Entity\Grid\Collection" type="Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult">
    <arguments>
        <argument name="mainTable" xsi:type="string">[vendor]_entity_table</argument>
        <argument name="resourceModel" xsi:type="string">[Vendor]\[Module]\Model\ResourceModel\Entity</argument>
    </arguments>
</virtualType>

In order to join multiple tables, the <virtualType> needs to be changed to a <type> and contain a few more arguments.

<type name="[Vendor]\[Module]\Model\ResourceModel\Entity\Grid\Collection">
    <arguments>
        <argument name="mainTable" xsi:type="string">[vendor]_entity_table</argument>
        <argument name="eventPrefix" xsi:type="string">[vendor]_[module]__grid_collection</argument>
        <argument name="eventObject" xsi:type="string">[vendor]_[module]_collection</argument>
        <argument name="resourceModel" xsi:type="string">[Vendor]\[Module]\Model\ResourceModel\Entity</argument>
    </arguments>
</type>

This also means that the grid class should become a physical file. Therefore create this file in the directory specified in the di.xml file.

<?php
namespace [Vendor]\[Module]\Model\ResourceModel\Entity\Grid;

use Magento\Framework\Api\Search\SearchResultInterface;
use Magento\Framework\Api\Search\AggregationInterface;
use [Vendor]\[Module]\Model\ResourceModel\Entity\Collection as EntityCollection;

class Collection extends EntityCollection implements SearchResultInterface
{
    /**
     * @var AggregationInterface
     */
    protected $aggregations;

    /**
     * @param \Magento\Framework\Data\Collection\EntityFactoryInterface $entityFactory
     * @param \Psr\Log\LoggerInterface $logger
     * @param \Magento\Framework\Data\Collection\Db\FetchStrategyInterface $fetchStrategy
     * @param \Magento\Framework\Event\ManagerInterface $eventManager
     * @param \Magento\Store\Model\StoreManagerInterface $storeManager
     * @param \Magento\Framework\EntityManager\MetadataPool $metadataPool
     * @param string $mainTable
     * @param string $eventPrefix
     * @param string $eventObject
     * @param string $resourceModel
     * @param string $model
     * @param \Magento\Framework\DB\Adapter\AdapterInterface|string|null $connection
     * @param \Magento\Framework\Model\ResourceModel\Db\AbstractDb $resource
     *
     * @SuppressWarnings(PHPMD.ExcessiveParameterList)
     */
    public function __construct(
        \Magento\Framework\Data\Collection\EntityFactoryInterface $entityFactory,
        \Psr\Log\LoggerInterface $logger,
        \Magento\Framework\Data\Collection\Db\FetchStrategyInterface $fetchStrategy,
        \Magento\Framework\Event\ManagerInterface $eventManager,
        \Magento\Store\Model\StoreManagerInterface $storeManager,
        $mainTable,
        $eventPrefix,
        $eventObject,
        $resourceModel,
        $model = 'Magento\Framework\View\Element\UiComponent\DataProvider\Document',
        $connection = null,
        \Magento\Framework\Model\ResourceModel\Db\AbstractDb $resource = null
    ) {
        parent::__construct(
            $entityFactory,
            $logger,
            $fetchStrategy,
            $eventManager,
            $connection,
            $resource
        );
        $this->_eventPrefix = $eventPrefix;
        $this->_eventObject = $eventObject;
        $this->_init($model, $resourceModel);
        $this->setMainTable($mainTable);
    }

    /**
     * @return AggregationInterface
     */
    public function getAggregations()
    {
        return $this->aggregations;
    }

    /**
     * @param AggregationInterface $aggregations
     * @return $this
     */
    public function setAggregations($aggregations)
    {
        $this->aggregations = $aggregations;
    }

    /**
     * Get search criteria.
     *
     * @return \Magento\Framework\Api\SearchCriteriaInterface|null
     */
    public function getSearchCriteria()
    {
        return null;
    }

    /**
     * Set search criteria.
     *
     * @param \Magento\Framework\Api\SearchCriteriaInterface $searchCriteria
     * @return $this
     * @SuppressWarnings(PHPMD.UnusedFormalParameter)
     */
    public function setSearchCriteria(\Magento\Framework\Api\SearchCriteriaInterface $searchCriteria = null)
    {
        return $this;
    }

    /**
     * Get total count.
     *
     * @return int
     */
    public function getTotalCount()
    {
        return $this->getSize();
    }

    /**
     * Set total count.
     *
     * @param int $totalCount
     * @return $this
     * @SuppressWarnings(PHPMD.UnusedFormalParameter)
     */
    public function setTotalCount($totalCount)
    {
        return $this;
    }

    /**
     * Set items list.
     *
     * @param \Magento\Framework\Api\ExtensibleDataInterface[] $items
     * @return $this
     * @SuppressWarnings(PHPMD.UnusedFormalParameter)
     */
    public function setItems(array $items = null)
    {
        return $this;
    }
}

The important points to take away from creating the grid collection class is that it extends from the original [Vendor]\[Module]\Model\ResourceModel\Entity\Collection defined.

In addition, the $mainTable, $eventPrefix, $eventObject and $resourceModel arguments are injected from the [Vendor]\[Module]\Model\ResourceModel\Entity\Grid\Collection type defined in di.xml.

The actual joining takes place within the [Vendor]\[Module]\Model\ResourceModel\Entity\Collection class within an _initSelect() method.

If the _initSelect() method doesn’t exist in your collection class, create it.

As an example, let’s assume the original database table used to create the grid UI component contains additional information about products, and contains a product_id column which holds the unique identifier of the product.

Now let’s suppose that the product’s quantity level is also needed in the grid as a additional column. To do this, we can join the existing database table with Magento’s cataloginventory_stock_item table.

If you haven’t done so already, define an additional column in the UI component XML markup file.

<column name="qty">
    <settings>
        <filter>text</filter>
        <label translate="true">Current Qty</label>
    </settings>
</column>

Heading back to the _initSelect() method in the collection class, you can write the following code to join the cataloginventory_stock_item table.

protected function _initSelect()
{
    parent::_initSelect();

    $this->getSelect()
        ->columns('csi.qty')
        ->joinLeft(
        ['csi' => $this->getTable('cataloginventory_stock_item')],
        'csi.product_id = main_table.product_id',
        []
    );
}

You’ll notice a few things here.

  • The code gives cataloginventory_stock_item an alias of csi.
  • The qty part of csi.qty must match the column name defined in the UI component XML markup file.
  • main_table is the alias given to the main table used in the grid UI component.

Refresh the cache and you should notice the that the quantity for the specified product has returned with data within the grid.

Join Two Tables Using a UI Grid Component in Magento 2

Note: This article is based on Magento Open Source version 2.2.