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

add_customer_column_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.

custom_customer_column

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.

customer_column_filter
customer_column_filter

 

Not getting success? Purchase ready to use module

What you will get in purchase?
  • Working source code of this tutorial
  • Email support


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

Leave a Comment

(0 Comments)

All the comments are goes into moderation before approval. Please do not spam. Your email address will not be published. Required fields are marked *

Enjoy this post? Please support Us!

Follow us on twitter for daily new updates..

 

Want to become
a Magento 2 Expert?

If Yes! Then Subscribe to our newsletter and get weekly article to you email id.
Subscribe Here
SUBSCRIBE NOW
close-link

To Avoid Spam Downloads, We Want Your Email

We will send you download link right
away. Please submit form below.
SEND ME DOWNLOAD LINK
Close