Magento2 Need to build Grid with multiple table collection using UI Components

Question

Started on building custom module, it has feature to display the GRID view for which need to join multiple tables in collection and render them on GRID and filters.

I have used UI Component from magento2 but I m not able to complete the tasks of joining the multiple tables in collection and prepare the grid view.

Anyone can help me on this.

Answer 1

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.

• Vigna S: @Ekta Puri, I used this but displays blank page
• Ekta Puri: @vigna you can download working module from ekvitech.com/blog/…
• Nidhi: @Ekta Puri, how can i get url paramaters in _initSelect() method of collection

Answer 2

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']
 );
 }
}

• Savoo: IT's working on intital stage but we try to filter out it's give me error .Column not found ..Please check Column not found: 1054 Unknown column 'designation_title' in 'where clause', query was: SELECT COUNT(*) .........
• Waqar Ali: i have done that but its not working :(
• Key Shang: @Savoo Your collection need extends the Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult

Answer 3

When you join multiple tables and try to retrieve the data from both and if you get error like Error Code: 1052. Column 'id' in where clause is ambiguous

It means your both tables have a column with name id. So you should specify in your ui component the correct primary key to use.

For example in a ui form

<dataSource name="vendor_mymodule_bworders_form_data_source">
 <argument name="dataProvider" xsi:type="configurableObject">
 <argument name="class" xsi:type="string">Vendor\MyModule\Model\Grid\Orders\BwOrdersFormDataProvider</argument>
 <argument name="name" xsi:type="string">vendor_mymodule_bworders_form_data_source</argument>
 <argument name="primaryFieldName" xsi:type="string">main_table.id</argument>
 <argument name="requestFieldName" xsi:type="string">id</argument>
 <argument name="data" xsi:type="array">
 <item name="config" xsi:type="array">
 <item name="submit_url" xsi:type="url" path="*/*/save"/>
 </item>
 </argument>
 </argument>
 <argument name="data" xsi:type="array">
 <item name="js_config" xsi:type="array">
 <item name="component" xsi:type="string">Magento_Ui/js/form/provider</item>
 </item>
 </argument>
</dataSource>

The following field

<argument name="primaryFieldName" xsi:type="string">main_table.id</argument>

should have the correct primary key identifier like main_table.id, not just id