How to Join Two Tables and Create Admin Grid using UI Component in Magento 2?

Hello Magento Friends,

In this tutorial, I will explain How to Join Two Tables and Create Admin Grid using UI Component in Magento 2?

The Admin grid manages, sorts, and filters various data in Magento 2. Admin Grid is used to perform the update and delete actions. You can Create UI Component Admin Grid in Magento 2.

If you want to join two tables in Admin Grid using UI Component in Magento 2, you can use the below steps.

Steps to Join Two Tables and Create Admin Grid using UI Component in Magento 2:

Step 1: Firstly, you need to edit the di.xml file. For that go to the below path

app/code/Vendor/Extension/etc/di.xml

Now add the code as follows

<?xml version="1.0"?>

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <!-- here we remove virtualType and defile collection as follow-->
    <type name="Vendor\Extension\Model\ResourceModel\Custom\Grid\Collection">
        <arguments>
            <argument name="mainTable" xsi:type="string">magecomp_customtable</argument>
            <argument name="eventPrefix" xsi:type="string">magecomp_grid_collection</argument>
            <argument name="eventObject" xsi:type="string">magecomp_collection</argument>
            <argument name="resourceModel" xsi:type="string">Vendor\Extension\Model\ResourceModel\Custom</argument>
        </arguments>
    </type>
    <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
        <arguments>
            <argument name="collections" xsi:type="array">
                <item name="grid_record_grid_list_data_source" xsi:type="string">Vendor\Extension\Model\ResourceModel\Grid\Grid\Collection</item>
            </argument>
        </arguments>
    </type>
</config>

Step 2: Next, create the Collection.php file at the below file path

app/code/Vendor/Extension/Model/ResourceModel/Custom/Collection.php

Then add the below-mentioned code

<?php

namespace Vendor\Extension\Model\ResourceModel\Custom;

use Magento\Framework\Data\Collection\EntityFactoryInterface;
use Psr\Log\LoggerInterface;
use Magento\Framework\Data\Collection\Db\FetchStrategyInterface;
use Magento\Framework\Event\ManagerInterface;
use Magento\Store\Model\StoreManagerInterface;
use Magento\Framework\DB\Adapter\AdapterInterface;
use Magento\Framework\Model\ResourceModel\Db\AbstractDb;

class Collection extends \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection
{
    protected $_idFieldName = 'id';

    public function __construct(
        EntityFactoryInterface $entityFactory,
        LoggerInterface $logger,
        FetchStrategyInterface $fetchStrategy,
        ManagerInterface $eventManager,
        StoreManagerInterface $storeManager,
        AdapterInterface $connection = null,
        AbstractDb $resource = null
    )
    {
        $this->_init('Vendor\Extension\Model\Custom', 'Vendor\Extension\Model\ResourceModel\Custom');
        
        parent::__construct($entityFactory, $logger, $fetchStrategy, $eventManager, $connection, $resource);
        $this->storeManager = $storeManager;
    }
    
    protected function _initSelect()
    {
        parent::_initSelect();

        $this->getSelect()->joinLeft(
            ['secondTable' => $this->getTable('second_table_name')], 
            'main_table.record_id = secondTable.record_id', 
            '*' 
        );
        /* '*' define that you want all column of 2nd table. if you want some particular column then you can define as ['column1','column2'] */    }
}

Step 3: Then, create Collection.php at the below file path

Vendor\Extension\Model\ResourceModel\Custom\Grid\Collection

And add the following code snippet

<?php
   
namespace Vendor\Extension\Model\ResourceModel\Custom\Grid;

use Magento\Framework\Api\Search\SearchResultInterface;
use Magento\Framework\Search\AggregationInterface;
// Your ResourceModel Collection File Path
use Vendor\Extension\Model\ResourceModel\Custom\Collection as GridCollection;
use Magento\Framework\Data\Collection\EntityFactoryInterface;
use Psr\Log\LoggerInterface;
use Magento\Framework\Data\Collection\Db\FetchStrategyInterface;
use Magento\Framework\Event\ManagerInterface;
use Magento\Store\Model\StoreManagerInterface;
use Magento\Framework\Model\ResourceModel\Db\AbstractDb;

class Collection extends GridCollection implements SearchResultInterface
{ 
    public function __construct(
        EntityFactoryInterface $entityFactory,
        LoggerInterface $logger,
        FetchStrategyInterface $fetchStrategy,
        ManagerInterface $eventManager,
        StoreManagerInterface $storeManager,
        $mainTable,
        $eventPrefix,
        $eventObject,
        $resourceModel,
        $model = 'Magento\Framework\View\Element\UiComponent\DataProvider\Document',
        $connection = null,
        AbstractDb $resource = null
    )
    {
        parent::__construct(
            $entityFactory,
            $logger,
            $fetchStrategy,
            $eventManager,
            $storeManager,
            $connection,
            $resource
        );
        $this->_eventPrefix = $eventPrefix;
        $this->_eventObject = $eventObject;
        $this->_init($model, $resourceModel);
        $this->setMainTable($mainTable);
    }

    public function getAggregations()
    {
        return $this->aggregations;
    }

    public function setAggregations($aggregations)
    {
        $this->aggregations = $aggregations;
    }

    public function getSearchCriteria()
    {
        return null;
    }

    public function setSearchCriteria(
        \Magento\Framework\Api\SearchCriteriaInterface $searchCriteria = null
    ) {
        return $this;
    }
    
    public function getTotalCount()
    {
        return $this->getSize();
    }
  
    public function setTotalCount($totalCount)
    {
        return $this;
    }
   
    public function setItems(array $items = null)
    {
        return $this;
    }
}

Conclusion:

Hence, using the above steps, you can quickly join two tables and create an admin grid using the UI component in Magento 2. If you have any doubts, ask me through the comment section. Share the article with your friends and stay connected with us.

Happy Coding!

Subscribe to Notifications