Magento 2 | customer grid error

Question

I added a custom customer attribute to the customer_grid and now it displays following error:

Something went wrong with processing the default view and we have restored the filter to its original state.

How can I fix that?

exception.log shows following error:

LIMIT 20 {"exception":"[object] (Zend_Db_Statement_Exception(code: 42): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'mediabasenummer' in 'order clause', query was: SELECT `main_table`.* FROM `customer_grid_flat` AS `main_table` WHERE (MATCH(`main_table`.name,`main_table`.email,`main_table`.created_in,`main_table`.taxvat,`main_table`.shipping_full,`main_table`.billing_full,`main_table`.billing_firstname,`main_table`.billing_lastname,`main_table`.billing_telephone,`main_table`.billing_postcode,`main_table`.billing_region,`main_table`.billing_city,`main_table`.billing_fax,`main_table`.billing_company) AGAINST('Leon')) ORDER BY mediabasenummer ASC

Answer 1

You have to add the plugin in for mediabasenummer column in Uicomponent data provider reporting file. You can do same as given below

1.create di.xml file app/code/Namespace/Modulename/etc/adminhtml and put below code in it.

<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="\Namespace\Modulename\Model\Plugin\ResourceModel\Customer\Grid"/>
    </type>
</config>

2.Create plugin model file Grid.php under app/code/Namespace/Modulename/Model/Plugin/ResourceModel/Customer and put below code in it.

<?php
namespace Namespace\Modulename\Plugin\ResourceModel\Customer;
 
class Grid
{
    public static $table = 'customer_grid_flat';
    public static $leftJoinTable = 'customer_entity_int';
 
    public function afterSearch($intercepter, $collection)
    {
        $attribute_id = '180'; //mediabasenummer attribute id

        if ($collection->getMainTable() === $collection->getConnection()->getTableName(self::$table)) {
            $leftJoinTableName = $collection->getConnection()->getTableName(self::$leftJoinTable);
 
            $collection
                ->getSelect()
                ->joinLeft(
                    ['cei'=>$leftJoinTableName],
                    "cei.entity_id = main_table.entity_id AND cei.attribute_id='".$attribute_id."'",
                    [
                        'mediabasenummer' => 'cei.value'
                    ]
                );
 
            $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;
    }
}

Hope this helps!!