Magento 2 Convert backend admin grid to use joint table

Question

What are the step I need to take to convert my backend admin grid to use a joint table I have my grid example with column id and name

id comes from the mainTable and name from the secondtable:

I have added to my collection:

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

This works well on the Frontend but not sure what I am missing to make it work on the admin grid?

Answer 1

You may did not add the custom column from 2nd tables in you modules Adminhtml layout XML files.

we assumed your custom extension name is Company_MyModule.

step 1: Assumed your admin grid XML file location is: Company/MyModule/view/adminhtml/ui/customer_listing.xml

You can edit the customer_listing.xml file as below.

<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="customer_columns" class="Magento\Customer\Ui\Component\Listing\Columns">
         <columns name="customer_columns" class="Magento\Customer\Ui\Component\Listing\Columns">

        <column name="custom_id">
           -----------
        </column>  
        <column name="name" >
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Name</item>
                    <item name="sortOrder" xsi:type="number">10</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

step 2: Assumed you collection model File location is: Company\MyModule\Model\ResourceModel\MyModule\collection.php

you can edit the collection model With below code

protected function _initSelect()
{
    parent::_initSelect();
     $this->getSelect()->joinLeft(
        ['secondTable' => $this->getTable('second_table')], 
        'main_table.entity_id = secondTable.main_table_entity_id', 
        '*' 
    );
    return $this;
}

step 3: Please run following CLI command from you Magento root directory

sudo rm -rf pub/static/admingtml/*
sudo php bin/magento cache:flush
sudo php bin/magento indexer:reindex

You can check this link How to Join custom table to customer grid collection and show new column in Grid in Magento2