7

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?

  1. 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')
5

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'

| improve this answer | |
  • what is the solution for magento 2.2? same? – Amit Bera Jun 26 '18 at 12:50
  • I have a varchar attribute in the customer field,in this method there is no error comes, but the sorting have some problem. – Naveenbos Nov 23 '18 at 6:14
5

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

| improve this answer | |
  • Solution works fine but while export getting error. – Chirag Patel Sep 11 '18 at 10:42
  • @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 – Manthan Dave Sep 11 '18 at 11:12
  • Thanks, if you have any idea or example how to override export functionality then please share. – Chirag Patel Sep 11 '18 at 12:02
1

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.

| improve this answer | |
0

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

| improve this answer | |
0

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

| improve this answer | |
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.