Magento 2 Filter not working while Custom column added on Sales Grid Using Ui Component

Question

I have added Custom column track_number into sales>>Order grid at Backend.

<vendor_name>/Sales/view/adminhtml/ui_component/sales_order_grid.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="sales_order_columns">
        <column name="track_number" class="<vendor_name>\Sales\Ui\Component\Listing\Column\OrderGrid">
            <argument name="data" xsi:type="array">
                <item name="js_config" xsi:type="array">
                    <item name="component" xsi:type="string">Magento_Ui/js/grid/columns/column</item>
                </item>
                <item name="config" xsi:type="array">
                    <item name="visible" xsi:type="boolean">true</item>
                    <item name="dataType" xsi:type="string">text</item>
                    <item name="align" xsi:type="string">left</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Tracking #</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

Create <vendor_name>\Sales\Ui\Component\Listing\Column\OrderGrid

<?php
/**
 * Copyright © 2013-2017 Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
namespace <vendor_name>\Sales\Ui\Component\Listing\Column;

use Magento\Ui\Component\Listing\Columns\Column;
use Magento\Framework\View\Element\UiComponent\ContextInterface;
use Magento\Framework\View\Element\UiComponentFactory;
use Magento\Sales\Model\ResourceModel\Order\CollectionFactory;
use Magento\Framework\Escaper;

/**
 * Cart crosssell list
 *
 * @author      Magento Core Team <core@magentocommerce.com>
 */
class OrderGrid extends Column
{
    /** @var \Magento\Sales\Api\Data\OrderInterface $order **/
    protected $_orderCollectionFactory;

    protected $_objectManager;

    /**
     * @var Escaper
     */
    protected $escaper;

    public function __construct(
        ContextInterface $context,
        UiComponentFactory $uiComponentFactory,
        Escaper $escaper,
        CollectionFactory $orderCollection,
        \Magento\Framework\ObjectManagerInterface $objectManager,
        array $components = [],
        array $data = []
    ) {
        $this->_orderCollectionFactory = $orderCollection;
        $this->_objectManager = $objectManager;
        $this->escaper = $escaper;

        parent::__construct($context, $uiComponentFactory, $components, $data);
    }

    public function getOrderTracking($id)
    {
        $order = $this->_objectManager->create('\Magento\Sales\Model\Order')->loadByIncrementId($id);
        $trackNumber = "";
        foreach ($order->getTracksCollection() as $_track) {
           $trackNumber = $_track->getNumber();
        }

        return $trackNumber;
    }

    public function prepareDataSource(array $dataSource)
    {      
        if (isset($dataSource['data']['items'])) {

            foreach ($dataSource['data']['items'] as &$item) {
                $trackNumber = $this->getOrderTracking($item['increment_id']);
                $item[$this->getData('name')] = $this->escaper->escapeHtml($trackNumber);
            }
        }

        return $dataSource;
    }

}

Above code helps to retrieve track data and append column into Order grid but for sorting or filter on this custom column I need to override grid collection model \Magento\Sales\Model\ResourceModel\Order\Grid\Collection.php

<?php
/**
 * Copyright © 2013-2017 Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
namespace <vendor_name>\Sales\Model\ResourceModel\Order\Grid;

use Magento\Framework\Data\Collection\Db\FetchStrategyInterface as FetchStrategy;
use Magento\Framework\Data\Collection\EntityFactoryInterface as EntityFactory;
use Magento\Framework\Event\ManagerInterface as EventManager;
use Psr\Log\LoggerInterface as Logger;

/**
 * Order grid collection
 */
class Collection extends \Magento\Sales\Model\ResourceModel\Order\Grid\Collection
{
    protected function _renderFiltersBefore() 
    {
        $joinTable = $this->getTable('sales_shipment_track');

        $this->getSelect()->JoinInner($joinTable.' as ordertable','main_table.entity_id = ordertable.order_id', array('track_number', 'tacking_created_at'=> 'ordertable.created_at'));

        parent::_renderFiltersBefore();
   }
}

All goes well but because of this collection join with sales_shipment_track table It creates integrity violation error on default filter while we have same field like created_at or status because we will get same column/field on both table.

So Problem is How to apply filter on custom column without affecting default column?

Any help would be appreciated.

Answer 1

I have the same issue and $this->addFilterToMap() fix my issue..

check this answer Magento 2 grid uiComponent: include a table alias in a column filter