How to add filter to a already working custom sales order grid column module?

Question

I have the following from another post, and now I'm looking for a way to make these 4 extra columns filterable.

Thank you,

\app\code\Vendor\SalesOrderGrid\Ui\Component\Listing\Column\Address.php

    <?php

    namespace Vendor\SalesOrderGrid\Ui\Component\Listing\Column;

    use Magento\Sales\Api\OrderRepositoryInterface;
    use Magento\Ui\Component\Listing\Columns\Column;
    use Magento\Framework\View\Element\UiComponent\ContextInterface;
    use Magento\Framework\View\Element\UiComponentFactory;

    /**
     * Class Address
     */
    class Address extends Column
    {
    /**
    * @var OrderRepositoryInterface
    */
private $orderRepository;


/**
 * Address constructor.
 * @param ContextInterface $context
 * @param UiComponentFactory $uiComponentFactory
 * @param OrderRepositoryInterface $orderRepository
 * @param array $components
 * @param array $data
 */
public function __construct(
    ContextInterface $context,
    UiComponentFactory $uiComponentFactory,
    OrderRepositoryInterface $orderRepository,
    array $components = [],
    array $data = []
) {
    $this->orderRepository = $orderRepository;
    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) {
            $column = $this->getData('name');
            $orderId = $item['entity_id'];
            $order = $this->orderRepository->get($orderId);

            if ($column == 'street') {
                $streetArr = $order->getShippingAddress()->getStreet();
                $data = implode(',', $streetArr);
            } elseif ($column == 'zip') {
                $data = $order->getShippingAddress()->getPostcode();
            } elseif ($column == 'city') {
                $data = $order->getShippingAddress()->getCity();
            } elseif ($column == 'phone') {
                $data = $order->getShippingAddress()->getTelephone();
            }

            $item[$this->getData('name')] = $data;
        }
    }

    return $dataSource;
    }
    }

\app\code\Vendor\SalesOrderGrid\view\adminhtml\ui_component\sales_order_grid.xml

    <column name="street" class="Vendor\SalesOrderGrid\Ui\Component\Listing\Column\Address">
        <settings>
            <filter>text</filter>
            <label translate="true">Street</label>
            <bodyTmpl>ui/grid/cells/html</bodyTmpl>
            <visible>true</visible>
        </settings>
    </column>

    <column name="zip" class="Vendor\SalesOrderGrid\Ui\Component\Listing\Column\Address">
        <settings>
            <filter>text</filter>
            <label translate="true">Zip</label>
            <bodyTmpl>ui/grid/cells/html</bodyTmpl>
            <visible>true</visible>
        </settings>
    </column>

    <column name="city" class="Vendor\SalesOrderGrid\Ui\Component\Listing\Column\Address">
        <settings>
            <filter>text</filter>
            <label translate="true">City</label>
            <bodyTmpl>ui/grid/cells/html</bodyTmpl>
            <visible>true</visible>
        </settings>
    </column>

    <column name="phone" class="Vendor\SalesOrderGrid\Ui\Component\Listing\Column\Address">
        <settings>
            <filter>text</filter>
            <label translate="true">Phone</label>
            <bodyTmpl>ui/grid/cells/html</bodyTmpl>
            <visible>true</visible>
        </settings>
    </column>

Answer 1

The filter will not be working with the data source class. For filter, we need to join sales_order_address table to sales_order table.

Follow these steps to make it work with the filter

1. Remove datasource class Vendor\SalesOrderGrid\Ui\Component\Listing\Column\Address from columns

app/code/Vendor/Module/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">
        <settings>
            <childDefaults>
                <param name="fieldAction" xsi:type="array">
                    <item name="provider" xsi:type="string">false</item>
                </param>
            </childDefaults>
        </settings>
        <column name="street">
            <settings>
                <filter>text</filter>
                <label translate="true">Street</label>
                <bodyTmpl>ui/grid/cells/html</bodyTmpl>
                <visible>true</visible>
            </settings>
        </column>
        <column name="postcode">
            <settings>
                <filter>text</filter>
                <label translate="true">Zip</label>
                <bodyTmpl>ui/grid/cells/html</bodyTmpl>
                <visible>true</visible>
            </settings>
        </column>
        <column name="city">
            <settings>
                <filter>text</filter>
                <label translate="true">City</label>
                <bodyTmpl>ui/grid/cells/html</bodyTmpl>
                <visible>true</visible>
            </settings>
        </column>
        <column name="telephone">
            <settings>
                <filter>text</filter>
                <label translate="true">Phone</label>
                <bodyTmpl>ui/grid/cells/html</bodyTmpl>
                <visible>true</visible>
            </settings>
        </column>
    </columns>
</listing>

2. Create plugin for order collection data provider

app/code/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="mageprince_custom_orders_grid" type="Vendor\Module\Plugin\OrdersGrid" sortOrder="10"/>
    </type>
</config>

app/code/Vendor/Module/Plugin/OrdersGrid.php

<?php

namespace Vendor\Module\Plugin;

class OrdersGrid
{
    public function afterGetReport($subject, $collection, $requestName)
    {
        if ($requestName !== 'sales_order_grid_data_source') {
            return $collection;
        }

        if ($collection->getMainTable() === $collection->getResource()->getTable('sales_order_grid')) {
            $orderAddressTable  = $collection->getResource()->getTable('sales_order_address');

            $collection->getSelect()->joinLeft(
                ['oat' => $orderAddressTable],
                'oat.parent_id = main_table.entity_id AND oat.address_type = \'shipping\'',
                ['telephone', 'city', 'postcode', 'street']
            );
        }

        return $collection;
    }
}

That's it you can now filter the new columns City, Street, Zip, Phone in order grid

Answer 2

Try below code :- It can be helpful for you for adding custom column with select filter.

I have used below code:

vendor/module/view/adminhtml/ui_component/sales_order_grid.xml

<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="newcolumn" class="Vendor\Module\Ui\Component\Listing\Column\OrderNewColumn">
            <settings>
                <filter>select</filter>
                <sortable>false</sortable>
                <options class="Vendor\Module\Model\Source\OptionTypeStatus"/>
                <dataType>select</dataType>
                <label translate="true">New Column</label>
            </settings>
        </column>
    </columns>
</listing>

Vendor\Moduel\Ui\Component\Listing\Column

<?php
namespace Vendor\Module\Ui\Component\Listing\Column;

use \Magento\Sales\Api\OrderRepositoryInterface;
use \Magento\Framework\View\Element\UiComponent\ContextInterface;
use \Magento\Framework\View\Element\UiComponentFactory;
use \Magento\Ui\Component\Listing\Columns\Column;
use \Magento\Framework\Api\SearchCriteriaBuilder;

class OrderNewColumn extends Column {

    protected $_orderRepository;
    protected $_searchCriteria;
    protected $_customfactory;

    public function __construct(
        ContextInterface $context,
        UiComponentFactory $uiComponentFactory,
        OrderRepositoryInterface $orderRepository,
        SearchCriteriaBuilder $criteria,
        array $components = [], array $data = []
    ){
        $this->_orderRepository = $orderRepository;
        $this->_searchCriteria = $criteria;
        parent::__construct($context, $uiComponentFactory, $components, $data);
    }

    public function prepareDataSource(array $dataSource) {
        if (isset($dataSource['data']['items'])) {
            foreach ($dataSource['data']['items'] as & $item) {
                $order = $this->_orderRepository->get($item["entity_id"]);
                if ($order->getNewcolumn() > 0) {
                    $item['newcolumn'] = 'Yes';
                } else {
                    $item['newcolumn'] = 'No';
                }
            }
        }
        return $dataSource;
    }
}

Vendor\Moduel\Model\Source

<?php
namespace Vendor\Module\Model\Source;

use Magento\Framework\Data\OptionSourceInterface;

/**
  * Class OptionTypeStatus
  */
class OptionTypeStatus implements OptionSourceInterface
{
    /**
     * Array
     *
     * @var array
     */
    protected $options;
    /**
     * Array
     *
     * @return array
     */
    public function toOptionArray()
    {
        $typesOfStatus = [
            0 => 'No',
            1 => 'Yes'
        ];
        $options = [];
        foreach ($typesOfStatus as $key => $typeOfStatus) {
            $options[] = [
                'label' => $typeOfStatus,
                'value' => $key
            ];
        }
    
        return $options;
    }
}

Answer 3

Check out this link for create custom filter with column https://www.mageworx.com/blog/how-to-add-column-with-filter-to-magento-2-orders-grid/

Answer 4

Try below code :-

XML file code change below code your column name

<columns name="sales_order_columns">
        <column name="short_name" class="Vendor\ModuleName\Ui\Component\Listing\Column\Mycolumn">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">My Column</item>
                </item>
            </argument>
        </column>
    </columns>

Filter the text in your custom column below codes :-

/etc/adminhtml/di.xml

<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="sales_grid_collection" type="\Vendor\ModuleName\Model\Plugin\Sales\Order\Grid"/>
    </type>
</config>

And then last create plugin model file Grid.php under

/Model/Plugin/Sales/Order/Grid.php

<?php
namespace Vendor\ModuleName\Model\Plugin\Sales\Order;
 
 
class Grid
{
 
    public static $table = 'sales_order_grid';
    public static $leftJoinTable = 'custom_order';
 
    public function afterSearch($intercepter, $collection)
    {
        if ($collection->getMainTable() === $collection->getConnection()->getTableName(self::$table)) {
 
            $leftJoinTableName = $collection->getConnection()->getTableName(self::$leftJoinTable);
 
            $collection
                ->getSelect()
                ->joinLeft(
                    ['co'=>$leftJoinTableName],
                    "co.order_id = main_table.entity_id",
                    [
                        'short_name' => 'co.short_name'
                    ]
                );
 
            $where = $collection->getSelect()->getPart(\Magento\Framework\DB\Select::WHERE);
 
            $collection->getSelect()->setPart(\Magento\Framework\DB\Select::WHERE, $where);
 
            //echo $collection->getSelect()->__toString();die;
 
 
        }
        return $collection;
 
 
    }
 
 
}

And above code you can add multiple filter columns like :-

$collection
 ->getSelect()
 ->joinLeft(
     ['co'=>$leftJoinTableName],
       "co.order_id = main_table.entity_id",
     [
       'short_name1' => 'co.short_name1',
       'short_name2' => 'co.short_name2'
     ]
  );

THANKS.