Join two table and create grid in admin section using ui component in Adobe Commerce ( Magento 2)

Here we learn how to Join two table and create grid in admin section using ui component in Adobe Commerce ( Magento 2 )

For create grid you can follow  our previous post how to create grid and insert data

For join you need to edit following files
app/code/Webkul/Grid/etc/di.xml
app/code/NameSpace/ModuleName/etc/di.xml
app/code/Webkul/Grid/Model/ResourceModel/Grid/Collection.php
app/code/NameSpace/ModuleName/Model/ResourceModel/Grid/Collection.php

# For join we need to update di.xml file as following

<?xml version="1.0"?>
<!--
    /**
     * Webkul Grid DI
     *
     * @category    Webkul
     * @package     Webkul_Grid
     * @author      Webkul Software Private Limited
     *
     */
 -->
<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="Webkul\Grid\Model\ResourceModel\Grid\Grid\Collection">
        <arguments>
            <argument name="mainTable" xsi:type="string">wk_grid_records</argument>
            <argument name="eventPrefix" xsi:type="string">wk_records_grid_collection</argument>
            <argument name="eventObject" xsi:type="string">wk_grid_records_collection</argument>
            <argument name="resourceModel" xsi:type="string">Webkul\Grid\Model\ResourceModel\Grid</argument>
        </arguments>
    </type>
    <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
        <arguments>
            <argument name="collections" xsi:type="array">
                <!--data provider name which used in grid ui component file -->
                <item name="grid_record_grid_list_data_source" xsi:type="string">Webkul\Grid\Model\ResourceModel\Grid\Grid\Collection</item>
            </argument>
        </arguments>
    </type>
</config>

2# Now we update app/code/Webkul/Grid/Model/ResourceModel/Grid/Collection.php
folder structure app/code/NameSpace/ModuleName/Model/ResourceModel/ModelName/Collection.php

<?php

    /**
     * Webkul Grid collection
     *
     * @category    Webkul
     * @package     Webkul_Grid
     * @author      Webkul Software Private Limited
     *
     */

namespace Webkul\Grid\Model\ResourceModel\Grid;

/* use required classes */
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
{
    /**
     * @var string
     */
    protected $_idFieldName = 'entity_id';

    /**
     * @param EntityFactoryInterface $entityFactory,
     * @param LoggerInterface        $logger,
     * @param FetchStrategyInterface $fetchStrategy,
     * @param ManagerInterface       $eventManager,
     * @param StoreManagerInterface  $storeManager,
     * @param AdapterInterface       $connection,
     * @param AbstractDb             $resource
     */
    public function __construct(
        EntityFactoryInterface $entityFactory,
        LoggerInterface $logger,
        FetchStrategyInterface $fetchStrategy,
        ManagerInterface $eventManager,
        StoreManagerInterface $storeManager,
        AdapterInterface $connection = null,
        AbstractDb $resource = null
    ) {
        $this->_init(
            \Webkul\Grid\Model\Grid::class,
            \Webkul\Grid\Model\ResourceModel\Grid::class
        );
        //Class naming structure 
        // 'NameSpace\ModuleName\Model\ModelName', 'NameSpace\ModuleName\Model\ResourceModel\ModelName'
        parent::__construct($entityFactory, $logger, $fetchStrategy, $eventManager, $connection, $resource);
        $this->storeManager = $storeManager;
    }
    
    protected function _renderFiltersBefore()
    {
        $table = $this->getTable('wk_record_temp');
        $this->getSelect()->joinLeft(
            ['secondTable' => $table], //2nd table name by which you want to join mail table
            'main_table.entity_id = secondTable.entity_id', // common column which available in both table 
            "*" // '*' define that you want all column of 2nd table. if you want some particular column then you can define as ['column1','column2']
        );
        parent::_renderFiltersBefore();
    }
}

3# now we create Collection.php in app/code/Webkul/Grid/Model/ResourceModel/Grid/Grid
path structure : app/code/NameSpace/ModuleName/Model/ResourceModel/ModelName/Grid

<?php
    /**
     * Webkul Grid 
     *
     * @category    Webkul
     * @package     Webkul_Grid
     * @author      Webkul Software Private Limited
     *
     */

namespace Webkul\Grid\Model\ResourceModel\Grid\Grid;

use Magento\Framework\Api\Search\SearchResultInterface;
use Magento\Framework\Search\AggregationInterface;
// Your ResourceModel Collection File Path
use Webkul\Grid\Model\ResourceModel\Grid\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
 * Collection for displaying grid
 */
class Collection extends GridCollection implements SearchResultInterface
{ 
    /**
     * @var Attribute
    */
    protected $eavAttribute;
    /**
     * @var StoreManagerInterface
    */
    protected $storeManager;

    /**
     * Resource initialization
     */
    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);
    }

    /**
     * @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;
    }
}

# now you can add 2nd table columns in grid_record_grid_list.xml in  app/code/Webkul/Grid/view/adminhtml/ui_component folder and then 2nd table column include in grid.

For your Magento 2 store , hire Magento developers who can dedicatedly work on your customised e-commerce projects.

Thanks