How to add a custom column in customer grid in Magento 2

In the last post, we have seen how to create customer attribute programmatically in Magento2. That attribute will be directly available to the customer grid. You can able to filter out the data for that specific attribute. But sometimes we want to show the data in the customer grid from another custom table. In that situation, we have to add a custom column in the customer grid.

In this post, we are going to create one custom module which will use ui_component to add a custom column in the customer grid. This custom column will render data from a custom database table using JOIN. We will highlight only the important file, not the whole module.

Getting Started: Development

Step 1: Create module.xml file under app/code/Codextblog/Customercolumn/etc directory and registration.php file under app/code/Codextblog/Customercolumn directory.

Step 2: Create customer_listing.xml file under app/code/Codextblog/Customercolumn/view/adminhtml/ui_component directory with below code

<?xml version="1.0" encoding="UTF-8"?>
<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">
		<column name="pan_number" class="Codextblog\Customercolumn\Ui\Component\Listing\Column\Pancolumn">
			<argument name="data" xsi:type="array">
				<item name="config" xsi:type="array">
					<item name="filter" xsi:type="string">text</item>
					<item name="label" translate="true" xsi:type="string">Pan Number</item>
				</item>
			</argument>
		</column>
    </columns>
</listing>

Step 3: Create Pancolumn.php ui component class file under app/code/Codextblog/Customercolumn/Ui/Component/Listing/Column directory with below code

<?php
namespace Codextblog\Customercolumn\Ui\Component\Listing\Column;

use Codextblog\Customercolumn\Model\PannumberFactory;
use Magento\Framework\Api\SearchCriteriaBuilder;
use Magento\Framework\View\Element\UiComponent\ContextInterface;
use Magento\Framework\View\Element\UiComponentFactory;
use Magento\Customer\Api\CustomerRepositoryInterface;
use Magento\Ui\Component\Listing\Columns\Column;

class Pancolumn extends Column
{
    protected $_customerRepository;
    protected $_searchCriteria;
    protected $_pannumberfactory;

    public function __construct(
        ContextInterface $context,
        UiComponentFactory $uiComponentFactory,
        CustomerRepositoryInterface $customerRepository,
        SearchCriteriaBuilder $criteria,
        PannumberFactory $pannumberFactory,
        array $components = [],
        array $data = []
    ) {
        $this->_customerRepository = $customerRepository;
        $this->_searchCriteria  = $criteria;
        $this->_pannumberfactory = $pannumberFactory;
        parent::__construct($context, $uiComponentFactory, $components, $data);
    }

    public function prepareDataSource(array $dataSource)
    {
        if (isset($dataSource['data']['items'])) {
            foreach ($dataSource['data']['items'] as & $item) {
                $customer  = $this->_customerRepository->getById($item["entity_id"]);

                $customer_id = $customer->getId();

                $collection = $this->_pannumberfactory->create()->getCollection();
                $collection->addFieldToFilter('customer_id', $customer_id);

                $data = $collection->getFirstItem();

                $item[$this->getData('name')] = $data->getPanNumber();
            }
        }
        return $dataSource;
    }
}

After following above steps you will get your custom column added in the customer grid.

Now if you filter the text in your custom column it will not work. To make it work we need to create a plugin which will filter the custom column using JOIN between ‘customer_grid_flat’ and custom database table ‘codextblog_customercolumn_pannumber’.

Step 4: Create di.xml file under app/code/Codextblog/Customercolumn/etc/adminhtml directory with below code.

<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\Reporting">
        <plugin name="customer_grid_collection" type="\Codextblog\Customercolumn\Model\Plugin\ResourceModel\Customer\Grid"/>
    </type>
</config>

Step 5: Create plugin model file Grid.php under app/code/Codextblog/Customercolumn/Model/Plugin/ResourceModel/Customer directory with below code.

<?php
namespace Codextblog\Customercolumn\Model\Plugin\ResourceModel\Customer;

class Grid
{
    public static $table = 'customer_grid_flat';
    public static $leftJoinTable = 'codextblog_customercolumn_pannumber';

    public function afterSearch($intercepter, $collection)
    {
        if ($collection->getMainTable() === $collection->getConnection()->getTableName(self::$table)) {
            $leftJoinTableName = $collection->getConnection()->getTableName(self::$leftJoinTable);

            $collection
                ->getSelect()
                ->joinLeft(
                    ['co'=>$leftJoinTableName],
                    "co.customer_id = main_table.entity_id",
                    [
                        'pan_number' => 'co.pan_number'
                    ]
                );

            $where = $collection->getSelect()->getPart(\Magento\Framework\DB\Select::WHERE);
            $collection->getSelect()->setPart(\Magento\Framework\DB\Select::WHERE, $where)->group('main_table.entity_id');
        }
        return $collection;
    }
}

We have left joined two tables on ‘customer_grid_flat’ table ‘entity_id’ and ‘codextblog_customercolumn_pannumber’ table ‘customer_id’. Once we applied this code our filter is started working.

 

If this post helps you, then please like us on Facebook and follow us on Twitter.