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

customer-gridgridmagento2uicomponent

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

Best Answer

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

Related Topic