Magento 2 – Create Admin Grid with Two Tables Using UI Component

admingridmagento-2.2.5magento2uicomponent

Need to create an admin grid on Magento 2 with data of two custom tables of different structures.

FYI, unable to proceed with joins and union.

Can you please anyone provide the best solution to achieve this?.

Best Answer

Follow the instructions mentioned in this article to create the grid.

Now, follow these steps:

  1. To join, you have to add the following codes in di.xml file:

    <?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. Then you have to update Collection.php file in app/code/Webkul/Grid/Model/ResourceModel/Grid/Collection.php with the following codes:

    /**
 * 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', 'Webkul\Grid\Model\ResourceModel\Grid');
    //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 _initSelect()
{
    parent::_initSelect();

    $this->getSelect()->joinLeft(
        ['secondTable' => $this->getTable('wk_record_temp')], //2nd table name by which you want to join mail table
        'main_table.record_id = secondTable.record_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']
    );
    }

    }
  3. Create Collection.php file in app/code/Webkul/Grid/Model/ResourceModel/Grid/Grid.
  4. Now, you can add 2nd table columns in grid_record_grid_list.xml in app/code/Webkul/Grid/view/adminhtml/ui_component folder.

You can find more information about these steps right here.

Related Topic