Magento 2 – Add Custom Column and Filter to Customer Grid from Joined Table

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'

Leaving out view_id seems to work:

<indexer id="customer_grid">

This way, the XML will be merged into the existing indexer instead of defining a new indexer.