How to Join custom table to customer grid collection and show new column in Grid in Magento2

Question

I want to add new column to customer grid and that new column is from my custom new table

how to do it in Magento2

Answer 1

to show custom column in customer grid i followed steps

1. view/adminhtml/ui/customer_listing.xml

        <columns name="customer_columns" class="Magento\Customer\Ui\Component\Listing\Columns">
   
           <column name="custom_fk">
               <argument name="data" xsi:type="array">
                   <item name="config" xsi:type="array">
                       <item name="filter" xsi:type="string">text</item>
                       <item name="editor" xsi:type="string">text</item>
                       <item name="label" xsi:type="string" translate="true">custom_fk</item>
                       <item name="visible" xsi:type="boolean">false</item>
                       <item name="sortOrder" xsi:type="number">53</item>
                   </item>
               </argument>
           </column>  
           <column name="custom_column" >
               <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">Custom Column</item>
                       <item name="sortOrder" xsi:type="number">53</item>
                   </item>
               </argument>
           </column>
   
       </columns>
   </listing>
   

Setp 2. add below code etc/Adminhtml/di.xml

<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
        <arguments>
            <argument name="collections" xsi:type="array">
                <item name="customer_listing_data_source" xsi:type="string">NAMESPACE\MODUENAME\Model\ResourceModel\Customer\Grid\Collection</item>
            </argument>
        </arguments>
    </type>

Step 3. Join the table in collection

<?php
/**
 * Copyright © 2013-2017 Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */

namespace NAMESPACE\MODUENAME\Model\ResourceModel\Customer\Grid;

use Magento\Customer\Ui\Component\DataProvider\Document;
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 Psr\Log\LoggerInterface as Logger;

class Collection extends \Magento\Customer\Model\ResourceModel\Grid\Collection
{
    /**
     * @inheritdoc
     */
    protected $document = Document::class;

    /**
     * Initialize dependencies.
     *
     * @param EntityFactory $entityFactory
     * @param Logger $logger
     * @param FetchStrategy $fetchStrategy
     * @param EventManager $eventManager
     * @param string $mainTable
     * @param string $resourceModel
     */
    public function __construct(
        EntityFactory $entityFactory,
        Logger $logger,
        FetchStrategy $fetchStrategy,
        EventManager $eventManager,
        $mainTable = 'customer_grid_flat',
        $resourceModel = '\Magento\Customer\Model\ResourceModel\Customer'
    ) {
        parent::__construct($entityFactory, $logger, $fetchStrategy, $eventManager, $mainTable, $resourceModel);
    }


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

        $this->getSelect()->joinLeft(
                ['secondTable' => $this->getTable('tablename')],
                'main_table.custom_fk = secondTable.id',
                ['columnname1 as custom_column']
            );
    } 
     /**
     * Modify for website_id code same in location type and location list
     */
    public function addFieldToFilter($field, $condition = null)
    {
        if ($field === 'custom_column') {
            $field = 'secondTable.columnname1';
        }

        return parent::addFieldToFilter($field, $condition);
    }

}

Note :- make sure custom_fk should set to is_used_in_grid=1 in customer_eav_attribute

if not set it to 1 and do the reindex

Answer 2

Add _initSelect() to Collection.php file.

Location: Vendorname\Modulename\Model\ResourceModel\Employee\collection.php With below code:

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