how to create grid using ui component with multi table(JOIN) in magento2?

Question

I have created a grid with UI component with one table now I want to join another table so how can I create??

i try but not working in Vendorname\Modulename\Model\ResourceModel\Quote\collection.php

protected function _initSelect()
    {
        parent::_initSelect();

        $this->getSelect()->joinLeft(
                ['secondTable' => $this->getTable('quote_customer')],
                'main_table.mcs_quote_id = secondTable.quote_request_id',
                array('*')
            );
    }

Thanks in advance.

Answer 1

You can display values from join two tables by using below model resource collection class function

Add below function to your model resource collection function

protected function _initSelect()
    {
        parent::_initSelect();

        $this->getSelect()->joinLeft(
                ['secondTable' => $this->getTable('tablename')],
                'main_table.columnname = secondTable.columnname',
                ['columnname1','columnname2','columnname3']
            );
    }

You can get more information from here magento2: admin grid showing joined table

Answer 2

Add _initSelect method to Collection.php file in Vendorname\Modulename\Model\ResourceModel\Quote\collection.php With below code

IMPORTANT: Please add addFilterToMap to initSelect Moethod Otherwise Sorting and Filtering not work with Join.

 protected function _initSelect()
{
    parent::_initSelect();

    $this->getSelect()->joinLeft(
            ['secondTable' => $this->getTable('tablename')],
            'main_table.columnname = secondTable.columnname',
            ['columnname1_alias'=>'columnname1','columnname2_clias' => 'columnname2']
        );
 $this->addFilterToMap('columnname1_alias', 'secondTable.columnname1');
 $this->addFilterToMap('columnname2_clias', 'secondTable.columnname2');
    return $this;
}

Thank you

Answer 3

Using the comments above, I've added the join to the Grid in a pretty simple and straightforward way. To have the joined tables, you cannot use Virtual type tables. So, that's how you make it happens

In your etc/di.xml add:

<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="vendor_module_yourtablemodel_listing_data_source" xsi:type="string">Vendor\Module\Model\ResourceModel\YourTableModel\Grid\Collection</item>
            </argument>
        </arguments>
    </type>
</config>

Then, create the Collection, Vendor\Module\Model\ResourceModel\YourTableModel\Grid\Collection.php

and add the content below:

<?php

namespace Vendor\Module\Model\ResourceModel\YourTableModel\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
{
    protected $_idFieldName = 'id';

    public function __construct(
        EntityFactory $entityFactory,
        Logger $logger,
        FetchStrategy $fetchStrategy,
        EventManager $eventManager,
        $mainTable = 'your_table',
        $resourceModel = 'Vendor\Module\Model\ResourceModel\YourTableModel',
        $identifierName = null,
        $connectionName = null
    ) {
        parent::__construct($entityFactory, $logger, $fetchStrategy, $eventManager, $mainTable, $resourceModel, $identifierName, $connectionName);
    }

    /**
     * @return Collection|void
     */
    protected function _initSelect()
    {
        parent::_initSelect();

        // Join the 2nd Table
        $this->getSelect()
            ->joinLeft(
                ['some_other_table_alias' => $this->getConnection()->getTableName('some_other_table')],
                'main_table.some_other_table_id = some_other_table_alias.id',
                ['some_other_table_column1', 'some_other_table_column2']
            );
    }
}

Flush the cache and it should work.

Answer 4

Try this:

protected function _renderFiltersBefore() {
        $joinTable = $this->getTable('quote_customer');

        $this->getSelect()->JoinLeft($joinTable.' as secondtable','main_table.id = secondtable.id', array('columnname'));
        parent::_renderFiltersBefore();
   }