Menu Close
    Searching for an experienced Magento Development Company ?

    Join two table and create grid in admin section using ui component in magento2

    Here we learn how to Join two table and create grid in admin section using ui component in magento2

    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', '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# 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\Affiliate\Model\ResourceModel\User\Grid;
    
    use Magento\Framework\Api\Search\SearchResultInterface;
    use Magento\Framework\Search\AggregationInterface;
    // your mane table collection
    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
    {
        /**
         * Resource initialization
         * @param EntityFactoryInterface   $entityFactory,
         * @param LoggerInterface          $logger,
         * @param FetchStrategyInterface   $fetchStrategy,
         * @param ManagerInterface         $eventManager,
         * @param StoreManagerInterface    $storeManager,
         * @param String                   $mainTable,
         * @param String                   $eventPrefix,
         * @param String                   $eventObject,
         * @param String                   $resourceModel,
         * @param $model = 'Magento\Framework\View\Element\UiComponent\DataProvider\Document',
         * @param $connection = null,
         * @param AbstractDb              $resource = null
         * @return $this
         */
        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.

    Thanks

    . . .
    Discuss on Helpdesk

    Leave a Comment

    Your email address will not be published. Required fields are marked*


    2 comments

  • rvl
    Thanks for the comprehensive examples , there are not many magento2 documentation with examles available, so this helps a lot.

    I have however some suggestions. Being new to Magento and PHP with DI and namespaces and so on, it is very confusing that in the above example you have named your module Grid, and the Magento directory structure and namespace as part of Magento2 also is named Grid.

    You end up with things like WebkulGridModelResourceModelGridGridGrid.php

    At least name *your* Module MyGrid then, so it stays clear without having to resort to things like explaining the namespace to directory structure mappings everytime. It’s very confusing, especially when starting to find out how it works.

    But again, thanks for the blog-posts, it helps a lot.

  • Jakob Meissner
    Displaying the joined colums works fine. However, filtering on the joined columns does not work. Do you hav a solution for this?

    Turns out it doesn’t work when the column names in the grid contain dots, eg which my coulumns had because the sql field name are ambiguous.

    But: If you replace the dots with underscore, auto filtering also doesn’t work because the column than can not be found in sql query. Fix for this is explained here: http://magento.stackexchange.com/questions/126207/magento-2-custom-admin-grid-field-error-when-sorting-or-filtering.

    If someone has a simple solution I will be glad to hear.

  • Back to Top