0

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)"} []
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;
    }

}

    
6
  • Thanks for the response i have added the column in the same file and it shows the only issue is filters are not working @RkRathod – Prits Sep 30 '20 at 9:35
  • check updated answer – Rk Rathod Sep 30 '20 at 9:47
  • Thanks for your response but the filter is not working yet i have checked log it shows me 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%') – Prits Sep 30 '20 at 10:16
  • which error ??? – Rk Rathod Sep 30 '20 at 10:17
  • Added my full error in question please check @RkRathod – Prits Sep 30 '20 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.