8

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

enter image description here Thanks in advance.

15
  • what error are display? Jul 29 '17 at 6:26
  • not get any error but did not get second table filed Jul 29 '17 at 6:27
  • quote_customer is your custom table? Have you check value getting or not? Jul 29 '17 at 6:29
  • yes i am checking but not get value i have add screen shot please check it Jul 29 '17 at 6:30
  • is quote_customer your custom table? Jul 29 '17 at 6:32
10

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

5
  • hello ,i try but it is not working Jul 29 '17 at 6:14
  • hello,It's working on initial load page but when we try to filter out on that filed it's give me error Column not found..
    – Savoo
    Mar 9 '18 at 9:12
  • For this we have to use addFilterToMap() in initselect() for filtering otherwise Good solution +1
    – Savoo
    Mar 9 '18 at 9:49
  • This worked perfectly for me. Thanks! Jul 31 '19 at 20:40
  • it fetches both table column, is there anyway it fetch only one table.
    – Ali Raza
    Oct 20 '20 at 9:56
8

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

1
  • it fetches both table column, is there anyway it fetch only one table.
    – Ali Raza
    Oct 20 '20 at 9:56
2

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.

7
  • 1
    thanks it working
    – mohan
    Sep 26 '20 at 5:52
  • while filtering main_table column get error
    – mohan
    Sep 26 '20 at 6:32
  • 1
    @mohan, in the method _initSelect(), print out $this->getSelect(); and see what the SQL looks like. The main table my have a different alias in your query.
    – medina
    Sep 27 '20 at 22:40
  • yes your correct. i was fixed this issue.
    – mohan
    Sep 28 '20 at 4:32
  • 1
    @AliRaza, you can reset the columns and re-add them. $_updates->getSelect() ->reset(Zend_Db_Select::COLUMNS)->columns('col1')
    – medina
    Oct 13 '21 at 22:25
0

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();
   }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.