Magento – Magento 2 – Customize Customer Grid Collection Data

admincollection;customer-gridmagento2

I want to add a where clause in customer grid collection.
e.g.

$currentCollection->getSelect()->where('custom condition');

I tried to google it but got no success.
I have also tried to implement customization methods used for product and order grid but it seems that di.xml and ui component xml files are different.

e.g when I try to add code

<argument name="addFieldStrategies" xsi:type="array">
        <item name="title" xsi:type="object">Namespace\Module\Ui\DataProvider\Test\AddTitleFieldToCollection</item>
</argument>

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

I get

Element 'arguments': This element is not expected. Expected is ( argument ).

I have also tried modifying collection through observer but it seems event customer_grid_collection_load_before does not exist.

I don't want to modify core code and I have no clue from where to start. Please comment if any other information needed.

Best Answer

EDIT I was wrong - in Magento 2 grid is built other way, look at this: http://webkul.com/blog/create-grid-edit-add-grid-row-and-installer-in-magento2/

And here you can find way to add custom filter for grid collection To add default filter to grid in Magento2?

Related Solutions

Magento – Magento computed column value in ‘filter_index’

Please add below changes to your code.

You need to add GROUP BY entity_id.

Add select query on collection like below.

$collection->getSelect()->joinLeft(
    array('s' => new Zend_Db_Expr('(SELECT (o.grand_total - o.tax_amount) grand_excl_tax,'
        . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/invoice') . ' AS o'
        . ' GROUP BY entity_id)')),
    "s.entity_id = main_table.entity_id",
    array('grand_excl_tax')
);

After that add filter_index in addColumn:

$this->addColumn('grand_excl_tax', array(
    'header'    => Mage::helper('customer')->__('Amount Excl. Tax'),
    'index'     => 'grand_excl_tax',
    'filter_index' => 's.grand_excl_tax',
    'type'      => 'currency',
    'align'     => 'right',
    'currency'  => 'order_currency_code',
));

I am tested it and it's working fine form me, After applying this there is not any issue on grid filter.

Let me know still if you face any issue.

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

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'

Best Answer

Related Solutions

Magento – Magento computed column value in ‘filter_index’

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

Solution for Magento 2.1

Related Topic