Skip to content

FILTER CUSTOM COLUMN SALES ORDER GRID, error SQLSTATE[42S22]: Column not found: 1054 Unknown column’ in ‘where clause’

I am trying to add custom column in sales order grid the column name is WoF Status. its work and show at sales order grid. but my problem is, when I filter this column. I got error like this :

main.CRITICAL: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘wof_status’ in ‘where clause’, query was: SELECT COUNT(*) FROM sales_order_grid AS main_table
LEFT JOIN spintowin_customer AS sc ON sc.order_id = main_table.entity_id
LEFT JOIN spintowin_info AS si ON sc.spin_id = si.entity_id
LEFT JOIN sales_order AS so ON so.entity_id = main_table.entity_id WHERE (wof_status LIKE ‘%Expired%’)

Here is my appcodeGameWoFviewadminhtmlui_componentsales_order_grid.xml code :

<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="wof_status">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="component" xsi:type="string">Magento_Ui/js/grid/columns/column</item>
                <item name="label" xsi:type="string" translate="true">WoF Status</item>
                <!-- <item name="sortOrder" xsi:type="number">60</item> -->
                <item name="align" xsi:type="string">left</item>
                <item name="dataType" xsi:type="string">text</item>
                <item name="visible" xsi:type="boolean">true</item>
                <item name="filter" xsi:type="string">text</item>
            </item>
        </argument>
    </column>
</columns>

appcodeGameWoFetcadminhtmldi.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="MagentoFrameworkViewElementUiComponentDataProviderCollectionFactory">
        <!-- <plugin name="sales_grid_collection" type="GameWoFModelPluginSalesOrderGrid"/> -->
        <plugin name="add_data_to_orders_grid"
                type="GameWoFPluginAddDataToOrdersGrid"
                sortOrder="10"
                disabled="false"/>
    </type>
</config>

appcodeGameWoFPluginAddDataToOrdersGrid.php

<?php
namespace GameWoFPlugin;
 
/**
 * Class AddDataToOrdersGrid
 */
class AddDataToOrdersGrid
{
    /**
     * @var PsrLogLoggerInterface
     */
    private $logger;

    public static $table = 'sales_order_grid';
    public static $lefJoinWofCustomer = 'spintowin_customer';
    public static $leftJoinWofInfo = 'spintowin_info';
 
    /**
     * AddDataToOrdersGrid constructor.
     *
     * @param PsrLogLoggerInterface $customLogger
     * @param array $data
     */
    public function __construct(
        PsrLogLoggerInterface $customLogger,
        array $data = []
    ) {
        $this->logger   = $customLogger;
    }
 
    /**
     * @param MagentoFrameworkViewElementUiComponentDataProviderCollectionFactory $subject
     * @param MagentoSalesModelResourceModelOrderGridCollection $collection
     * @param $requestName
     * @return mixed
     */
    public function afterGetReport($subject, $collection, $requestName)
    {
        if ($requestName !== 'sales_order_grid_data_source') {
            return $collection;
        }
 
        if ($collection->getMainTable() === $collection->getConnection()->getTableName(self::$table)) {
            try {
                $lefJoinTableCustomer = $collection->getConnection()->getTableName(self::$lefJoinWofCustomer);
                $leftJoinTableInfo = $collection->getConnection()->getTableName(self::$leftJoinWofInfo);

                $collection->getSelect()->joinLeft(
                    ['sc' => $lefJoinTableCustomer],
                    'sc.order_id = main_table.entity_id',
                    [
                        'sc.timestamp' => 'sc.timestamp',
                        'sc.play_status' => 'sc.play_status'
                    ]
                );

                $collection->getSelect()->joinLeft(
                    ['si' => $leftJoinTableInfo],
                    'sc.spin_id = si.entity_id',
                    [
                        'si.start_date' => 'si.start_date',
                        'si.end_date' => 'si.end_date',
                        'si.expired_duration' => 'si.expired_duration'
                    ]
                );

                $collection->getSelect()->columns("
                    IF(
                    sc.customer_id IS NULL,
                    '', 
                    IF (
                        TIME_TO_SEC( TIMEDIFF( NOW(), si.start_date ) > TIMEDIFF( si.end_date , si.start_date ) ),
                        'Expired',
                        IF (
                        sc.play_status = '1',
                        'Already Played',
                        IF (
                            NOW() > DATE_ADD( timestamp, INTERVAL si.expired_duration SECOND ),
                            'Expired',
                            'Not Play'
                        )
                        )
                    )
                    ) AS wof_status
                ");
    
                $where = $collection->getSelect()->getPart(MagentoFrameworkDBSelect::WHERE);
    
                $collection->getSelect()->setPart(MagentoFrameworkDBSelect::WHERE, $where);

                //echo $collection->getSelect()->__toString();die;
            } catch (Zend_Db_Select_Exception $selectException) {
                // Do nothing in that case
                $this->logger->log(100, $selectException);
            }
        }
        
        return $collection;
    }
}

Can anyone help me please? Thanks