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

Question

I am using below code to add a column in customer grid in admin.

/app/code/Namespace/Module/view/adminhtml/ui_component/customer_listing.xml

<?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" class="Magento\Customer\Ui\Component\Listing\Columns">
 <column name="magcustomer_customer_approve" class="Namespace\Module\Ui\Component\Listing\Column\Showisapproved">
 <argument name="data" xsi:type="array">
 <item name="config" xsi:type="array">
 <item name="filter" xsi:type="string">select</item>
 <item name="editor" xsi:type="string">select</item>
 <item name="component" xsi:type="string">Magento_Ui/js/grid/columns/select</item>
 <item name="dataType" xsi:type="string">select</item>
 <item name="label" xsi:type="string" translate="true">Is Approved</item>
 <item name="sortOrder" xsi:type="number">51</item>
 </item>
 </argument>
 </column>
 </columns>
</listing>

/app/code/Namespace/Module/Ui/Component/Listing/Column/Showisapproved.php

<?php
namespace Namespace\Module\Ui\Component\Listing\Column;
use Magento\Framework\View\Element\UiComponent\ContextInterface;
use Magento\Framework\View\Element\UiComponentFactory;
use Magento\Ui\Component\Listing\Columns\Column;
class Showisapproved extends Column
{
 /**
 * 
 * @param ContextInterface $context 
 * @param UiComponentFactory $uiComponentFactory 
 * @param array $components 
 * @param array $data 
 */
 public function __construct(
 ContextInterface $context,
 UiComponentFactory $uiComponentFactory,
 array $components = [],
 array $data = []
 ) {
 parent::__construct($context, $uiComponentFactory, $components, $data);
 }
 /**
 * Prepare Data Source
 *
 * @param array $dataSource
 * @return array
 */
 public function prepareDataSource(array $dataSource)
 {
 if (isset($dataSource['data']['items'])) {
 foreach ($dataSource['data']['items'] as & $item) {
 $item[$this->getData('name')] = 0;//Value which you want to display
 }
 }
 return $dataSource;
 }
}

magcustomer_customer_approve is customer attribute created using below code.

public function install(ModuleDataSetupInterface $setup, ModuleContextInterface $context)
 {
 /** @var CustomerSetup $customerSetup */
 $customerSetup = $this->customerSetupFactory->create(['setup' => $setup]);
 $customerEntity = $customerSetup->getEavConfig()->getEntityType('customer');
 $attributeSetId = $customerEntity->getDefaultAttributeSetId();
 /** @var $attributeSet AttributeSet */
 $attributeSet = $this->attributeSetFactory->create();
 $attributeGroupId = $attributeSet->getDefaultGroupId($attributeSetId);
 $customerSetup->addAttribute(Customer::ENTITY, 'magcustomer_customer_approve', [
 'type' => 'int',
 'label' => 'Is Approved',
 'input' => 'select',
 'required' => false,
 'visible' => true,
 'user_defined' => true,
 'sort_order' => 1001,
 'position' => 1001,
 'system' => 0,
 'source' => 'Magento\Eav\Model\Entity\Attribute\Source\Boolean',
 'adminhtml_only'=>1,
 'default'=>0
 ]);
 $attribute = $customerSetup->getEavConfig()->getAttribute(Customer::ENTITY, 'magcustomer_customer_approve')
 ->addData([
 'attribute_set_id' => $attributeSetId,
 'attribute_group_id' => $attributeGroupId,
 'used_in_forms' => ['adminhtml_customer'],
 ]);
 $attribute->save();
 }

1. If I don't use Magecoder\Magcustomer\Ui\Component\Listing\Column\Showisapproved, the custom column comes blank.

enter image description here

But if I use then I need to fetch using customer model in function prepareDataSource because variable $dataSource does not contain value of custom column. Is it possible to show values without fetching data again?

2. Also when filter is used a SQL error is generated.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'magcustomer_customer_approve' in 'where clause'
, query was: SELECT COUNT(*) FROM `customer_grid_flat` AS `main_table` WHERE (`magcustomer_customer_approve
` = '1') AND (`magcustomer_customer_approve` = '1') AND (`magcustomer_customer_approve` = '1') AND (
`magcustomer_customer_approve` = '1') AND (`magcustomer_customer_approve` = '1')

• Ekta Puri: have a look here magento.stackexchange.com/questions/126534/… may be this can help

Answer 1

Solution for Magento 2.1

• Add the attribute to the customer grid index:

  etc/indexer.xml

  <config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Indexer/etc/indexer.xsd">
   <indexer id="customer_grid">
   <fieldset name="customer">
   <field name="magcustomer_customer_approve" xsi:type="filterable" dataType="int"/>
   </fieldset>
   </indexer>
  </config>
  

  Note that the "indexer" and "fieldset" elements only have the "id" and "name" attributes respectively. The structure is merged into the existing indexer.

• Mark the attribute as "used in grid"

  In the installer:

   'is_used_in_grid' => true,
  

  otherwise you will get another SQL error during indexing, because the indexer looks for the column in the customer_entity main table:

  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.magcustomer_customer_approve' in 'field list'

• Amit Bera: what is the solution for magento 2.2? same?
• Naveenbos: I have a varchar attribute in the customer field,in this method there is no error comes, but the sorting have some problem.

Answer 2

Finally got the solution : We need to create indexer.xml file in /etc/di folder.

Main thing is view_id in view_id we need to pass "coloumn_name" which we need to show in grid.

For example here i need to display nickname so i have passed "nickname" in view_id.

<?xml version="1.0"?>
<!--
/**
 * Copyright © 2016 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */
-->
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:noNamespaceSchemaLocation="urn:magento:framework:Indexer/etc/indexer.xsd">
 <indexer id="customer_grid" view_id="nickname" class="Magento\Framework\Indexer\Action\Entity" primary="customer">
 <fieldset name="customer" source="Magento\Customer\Model\ResourceModel\Customer\Collection"
 provider="Magento\Customer\Model\Indexer\AttributeProvider">
 <field name="nickname" xsi:type="filterable" dataType="varchar"/>
 </fieldset>
 </indexer>
</config>

It works on Magento 2.1.4

• Chirag Patel: Solution works fine but while export getting error.
• Manthan Dave: @ChiragPatel - Yes that is correct , because i haven't amended/work around on any export functionality so , you need to override order export functionality in this custom module and need to work around for the same
• Chirag Patel: Thanks, if you have any idea or example how to override export functionality then please share.

Answer 3

Yes you need to add following code.. Step1:Create file your_module/etc/indexer.xml

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Indexer/etc/indexer.xsd">
<indexer id="customer_grid" view_id="dummy" class="Magento\Framework\Indexer\Action\Entity" primary="customer">
 <fieldset name="customer" source="Magento\Customer\Model\ResourceModel\Customer\Collection" provider="Magento\Customer\Model\Indexer\AttributeProvider">
 <field name="<your_attribute>" xsi:type="filterable" dataType="int"/>
 </fieldset>
</indexer>

Now you need to below command:

run command : php bin/magento indexer:reindex

good luck

Answer 4

Add a custom column in the customer grid Using the below way.

==> please create the first module and follow the below step ==> Now we used to the plugin, please create the file at the below location.

Vendor/Module/etc/di.xml

<?xml version="1.0"?>
<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\CollectionFactory">
 <plugin name="grid_customs_column" type="Vendor\Module\Plugin\GridCustomerJoinCollection" sortOrder="5" />
 </type>
</config>

==>Now create the plugin to join the table with your collection and return the collection and create the file at below location

Vendor/Module/Plugin/GridCustomerJoinCollection.php

<?php
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
namespace Vendor\Module\Plugin;
use Magento\Framework\Data\Collection;
use Magento\Framework\ObjectManagerInterface;
/**
 * Class CollectionPool
 */
class GridCustomerJoinCollection
{
 public static $table = 'customer_grid_flat';
 public static $leftJoinTable = 'sales_order'; // My custom table
 /**
 * Get report collection
 *
 * @param string $requestName
 * @return Collection
 * @throws \Exception
 */
 public function afterGetReport(
 \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject,
 $collection,
 $requestName
 ) {
 if($requestName == 'customer_listing_data_source')
 {
 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",
 [
 'customer_id' => 'co.customer_id',
 'custom_filed'=> 'co.custom_filed'
 ]
 );
 /* return data with left join customer_id from sales_order and custom_filed*/
 $where = $collection->getSelect()->getPart(\Magento\Framework\DB\Select::WHERE);
 $collection->getSelect()->setPart(\Magento\Framework\DB\Select::WHERE, $where)->group('main_table.entity_id');;
 /*echo $collection->getSelect()->__toString();die;*/
 }
 }
 return $collection;
 }
}

==> Now create the below folder and file and add the columns at the Vendor/Module/view/adminhtml/ui_component/customer_listing.xml

<?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" class="Magento\Customer\Ui\Component\Listing\Columns">
 <column name="custom_filed">
 <argument name="data" xsi:type="array">
 <item name="config" xsi:type="array">
 <item name="sortOrder" xsi:type="number">999</item>
 <item name="filter" xsi:type="string">text</item>
 <item name="label" translate="true" xsi:type="string">Customer Custom Column</item>
 </item>
 </argument>
 </column>
 </columns>
</listing>

===> Now please chek and confirm.

Answer 5

I tried above solution but not working

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Indexer/etc/indexer.xsd">
<indexer id="customer_grid" view_id="dummy" class="Magento\Framework\Indexer\Action\Entity" primary="customer">
 <fieldset name="customer" source="Magento\Customer\Model\ResourceModel\Customer\Collection" provider="Magento\Customer\Model\Indexer\AttributeProvider">
 <field name="is_approved" xsi:type="filterable" dataType="int"/>
 </fieldset>
</indexer>

But not working So first my question is how can we override indexer.xml?

After I checked you the solution you source Magento\Customer\Model\ResourceModel\Customer\Collection and in Magento_Customer/etc/indexer.xml using Magento\Customer\Model\Indexer\Source and this showing error while indexing.

So I think we don't need any think only apply customer attribute

$customerSetup->addAttribute(Customer::ENTITY, 'is_approved', [
 'type' => 'int',
 'label' => 'Approved',
 'input' => 'select',
 'source' => 'Magento\Eav\Model\Entity\Attribute\Source\Boolean',
 'value' => 0,
 'default' => 0,
 'required' => false,
 'visible' => false,
 'user_defined' => true,
 'sort_order' => 90,
 'position' => 90,
 'system' => false,
 'is_used_in_grid' => true,
 'is_visible_in_grid' => true,
 'is_filterable_in_grid' => true,
 'is_searchable_in_grid' => true,
 ]);

So need 'is_used_in_grid' => true and in di.xml add

<preference for="Magento\Customer\Model\Indexer\Source" type="Magento\Customer\Model\ResourceModel\Customer\Collection" />

Or

<virtualType name="Magento\Customer\Model\Indexer\Source" type="Magento\Customer\Model\ResourceModel\Customer\Collection" />

And It's working

Answer 6

Here is Solution. Let me know if it works. It works for me like a charm. https://magento.stackexchange.com/a/237030/63460