How 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;

    }

}