Filter is not working on Custom Values in Magento 2 Order?

Question

I have save my custom data in quote and sales_order table and show data in Admin grid but filter is not working in this as it is search in sales_order_grid table in Magento 2.

I have found out a solution using virtualType in di.xml but still it doesn't work.

my di.xml file

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
<preference for="Reg\Checkout\Api\Data\CustomFieldsInterface" type="Reg\Checkout\Model\Data\CustomFields" />
<preference for="Reg\Checkout\Api\CustomFieldsRepositoryInterface" type="Reg\Checkout\Model\CustomFieldsRepository" />
<preference for="Reg\Checkout\Api\CustomFieldsGuestRepositoryInterface" type="Reg\Checkout\Model\CustomFieldsGuestRepository" />
<virtualType name="Magento\Sales\Model\ResourceModel\Order\Grid" type="Magento\Sales\Model\ResourceModel\Grid">
    <arguments>
        <argument name="columns">
            <item name="custom_value" xsi:type="string">sales_order.custom_value</item>
        </argument>
    </arguments>
</virtualType>
</config>

Still it doesn't work can anyone point out what's the issue.

Any help is appreciated.

Error

Column not found: 1054 Unknown column 'custom_value' in 'where clause', query was: SELECT COUNT(*) FROM `sales_order_grid` AS `main_table` WHERE (`custom_value` = 'facebook') AND (`custom_value` LIKE '%facebook%') {"exception":"[object] (Zend_Db_Statement_Exception(code: 42): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'custom_value' in 'where clause', query was: SELECT COUNT(*) FROM `sales_order_grid` AS `main_table` WHERE (`custom_value` = 'facebook') AND (`custom_value` LIKE '%facebook%') at /var/www/html/mag/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:110, PDOException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'custom_value' in 'where clause' at /var/www/html/mag/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:91)"} []

Answer 1

You can also add column in sales_order_grid.xml

app/code/Vender/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">
    <listingToolbar name="listing_top">
        <filters name="listing_filters">
                <filterSelect name="custom_value" provider="${ $.parentName }" component="Magento_Ui/js/form/element/ui-select" template="ui/grid/filters/elements/ui-select">
                    <settings>
                        <options class="Vender\Module\Model\Config\Source\CustomValueOptions"/>
                        <caption translate="true">...Select...</caption>
                        <label translate="true">Sales Custom Value</label>
                        <dataScope>cuustom_value</dataScope>
                        <imports>
                            <link name="visible">componentType = column, index = ${ $.index }:visible</link>
                        </imports>
                    </settings>
                </filterSelect>
        </filters>
    </listingToolbar>
    <columns name="sales_order_columns">
        <column name="custom_value">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">true</item>
                    <item name="sortOrder" xsi:type="number">25</item>
                    <item name="label" xsi:type="string" translate="true">Custom Value</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

Afetr this in this file :- Vender\Module\Model\Config\Source\CustomValueOptions

<?php

namespace Vendor\Module\Model\Config\Source;

use Magento\Framework\Data\OptionSourceInterface;

class CustomValueOptions implements OptionSourceInterface
{
    protected $userFactory;
    public $_options;

    public function __construct(\Magento\User\Model\UserFactory $userFactory)
    {
        $this->userFactory = $userFactory;
    }

    public function toOptionArray()
    {
        $this->_options = [];
        $this->_options[] = ['label' => 'label1','value' => 'value1'];
        $this->_options[] = ['label' => 'label2','value' => 'value2'];
        $this->_options[] = ['label' => 'label3','value' => 'value3'];
        return $this->_options;
    }

}

    

Answer 2

I have successfully applied filter by using following URL's

https://amasty.com/knowledge-base/how-to-add-a-custom-column-to-the-order-grid-in-magento-2.html and

https://meetanshi.com/blog/add-custom-column-in-order-grid-in-magento-2/?unapproved=7760&moderation-hash=847fcee67b0f5fd29b14c840cfabe2d9#comment-7760

Thanks,