Magento2 Need to build Grid with multiple table collection using UI Components
I created admin grid which has join of two custom tables. you cannot do this by using virtual type in di.xml, so you need to follow these steps and update your
etc/di.xml,
Model/Resource/Modulename/Collection.php add join in this file,
Model/Resource/Modulename/Grid/Collection.php,
IN your etc/di.xml
<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
<arguments>
<argument name="collections" xsi:type="array">
<item name="namespace_modulename_listing_data_source" xsi:type="string">Namespace\Modulename\Model\Resource\Modulename\Grid\Collection</item>
</argument>
</arguments>
</type>
<type name="Namespace\Modulename\Model\Resource\Modulename\Grid\Collection">
<arguments>
<argument name="mainTable" xsi:type="string">tablename</argument>
<argument name="eventPrefix" xsi:type="string">namespace_modulename_grid_collection</argument>
<argument name="eventObject" xsi:type="string">namespace_grid_collection</argument>
<argument name="resourceModel" xsi:type="string">Namespace\Modulename\Model\Resource\Modulename</argument>
</arguments>
</type>
IN your Model/Resource/Modulename/Collection.php
<?php
namespace Namespace\Modulename\Model\Resource\Modulename;
use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;
class Collection extends AbstractCollection
{
/**
* Define model & resource model
*/
const YOUR_TABLE = 'tablename';
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,
\Magento\Framework\DB\Adapter\AdapterInterface $connection = null,
\Magento\Framework\Model\ResourceModel\Db\AbstractDb $resource = null
) {
$this->_init(
'Namespace\Modulename\Model\Modulename',
'Namespace\Modulename\Model\Resource\Modulename'
);
parent::__construct(
$entityFactory, $logger, $fetchStrategy, $eventManager, $connection,
$resource
);
$this->storeManager = $storeManager;
}
protected function _initSelect()
{
parent::_initSelect();
$this->getSelect()->joinLeft(
['secondTable' => $this->getTable('tablename')],
'main_table.columnname = secondTable.columnname',
['columnname1','columnname2','columnname3']
);
}
}
?>
IN your Model/Resource/Modulename/Grid/Collection.php
<?php
namespace Namespace\Modulename\Model\Resource\Modulename\Grid;
use Magento\Framework\Api\Search\SearchResultInterface;
use Magento\Framework\Search\AggregationInterface;
use Namespace\Modulename\Model\Resource\Modulename\Collection as ModulenameCollection;
/**
* Class Collection
* Collection for displaying grid
*/
class Collection extends ModulenameCollection implements SearchResultInterface
{
/**
* Resource initialization
* @return $this
*/
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,
$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;
}
}
?>
Hope that helps.
Join 2 tables
In your Vendor\Module\Model\ResourceModel\ModelName\Grid\Collection, add the _initSelect() function like below
protected function _initSelect()
{
parent::_initSelect();
$this->getSelect()->joinLeft(
['secondTable' => $this->getTable('admin_user')], //2nd table name by which you want to join
'main_table.user_id= secondTable.user_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']
);
}
Join 3 tables and More
Use thirdTable, fourthTable to join more tables like below:
protected function _initSelect()
{
parent::_initSelect();
$this->getSelect()->joinLeft(
['secondTable' => $this->getTable('admin_user')],
'main_table.user_id = secondTable.user_id',
['username']
)->joinLeft(
['thirdTable' => $this->getTable('catalog_product_entity')],
'main_table.product_id = thirdTable.entity_id',
['sku']
);//use fourthTable, fifthTable to join more tables
}
Example
File app/code/SAdmin/Cart/etc/di.xml.
The item name is the name of data_source used in your UI Components.
<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
<arguments>
<argument name="collections" xsi:type="array">
<item name="sadmin_cart_index_index_listing_data_source" xsi:type="string">SAdmin\Cart\Model\ResourceModel\Quote\Grid\Collection</item>
</argument>
</arguments>
</type>
</config>
File app/code/SAdmin/Cart/Model/ResourceModel/Quote/Grid/Collection.php
Please notice extends the Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult and set the parameters $mainTable and $resourceModel.
namespace SAdmin\Cart\Model\ResourceModel\Quote\Grid;
use Magento\Framework\Data\Collection\Db\FetchStrategyInterface as FetchStrategy;
use Magento\Framework\Data\Collection\EntityFactoryInterface as EntityFactory;
use Magento\Framework\Event\ManagerInterface as EventManager;
use Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult;
use Psr\Log\LoggerInterface as Logger;
class Collection extends SearchResult
{
public function __construct(
EntityFactory $entityFactory, Logger $logger, FetchStrategy $fetchStrategy, EventManager $eventManager,
$mainTable = 'quote',
$resourceModel = 'Magento\Quote\Model\ResourceModel\Quote',
$identifierName = null, $connectionName = null
)
{
parent::__construct($entityFactory, $logger, $fetchStrategy, $eventManager, $mainTable, $resourceModel, $identifierName, $connectionName);
}
public function _initSelect()
{
parent::_initSelect();
return $this->getSelect()->joinLeft(
['secondTable' => $this->getTable('customer_group')], //2nd table name by which you want to join
'main_table.customer_group_id= secondTable.customer_group_id', // common column which available in both table
['customer_group_code']// '*' define that you want all column of 2nd table. if you want some particular column then you can define as ['column1','column2']
);
}
}