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