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

Question

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 app\code\Game\WoF\view\adminhtml\ui_component\sales_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>

app\code\Game\WoF\etc\adminhtml\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="sales_grid_collection" type="\Game\WoF\Model\Plugin\Sales\Order\Grid"/> -->
        <plugin name="add_data_to_orders_grid"
                type="\Game\WoF\Plugin\AddDataToOrdersGrid"
                sortOrder="10"
                disabled="false"/>
    </type>
</config>

app\code\Game\WoF\Plugin\AddDataToOrdersGrid.php

<?php
namespace Game\WoF\Plugin;
 
/**
 * Class AddDataToOrdersGrid
 */
class AddDataToOrdersGrid
{
    /**
     * @var \Psr\Log\LoggerInterface
     */
    private $logger;

    public static $table = 'sales_order_grid';
    public static $lefJoinWofCustomer = 'spintowin_customer';
    public static $leftJoinWofInfo = 'spintowin_info';
 
    /**
     * AddDataToOrdersGrid constructor.
     *
     * @param \Psr\Log\LoggerInterface $customLogger
     * @param array $data
     */
    public function __construct(
        \Psr\Log\LoggerInterface $customLogger,
        array $data = []
    ) {
        $this->logger   = $customLogger;
    }
 
    /**
     * @param \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject
     * @param \Magento\Sales\Model\ResourceModel\Order\Grid\Collection $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(\Magento\Framework\DB\Select::WHERE);
    
                $collection->getSelect()->setPart(\Magento\Framework\DB\Select::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

Answer 1

This error occurs when the database cannot find a column on any of the joined tables. In this case the neither the sales_order_grid table nor the spintowin_customer table contain a column called wof_status.

The sales_order_grid table is a Magento table and does not have such a column.

If you expect that the wof_status column would be found on either spintowin_customer or sales_order_grid, then check your Game\WoF\Setup classes, or run the migrations with the following command:

$ bin/magento setup:upgrade

If you expect that the wof_status column would be found on a different table, then check your SQL statements.

For other people encountering this error, some other common causes of the Unknown column error:

1. A known Magento 2 pitfall where defining nonexisting columns in UI Components will search for them in the table defined in the previously-parsed UI Component, often in another module. In this case, you'll see a table mentioned that has absolutely nothing to do with the components you're working on (such as braintree_transaction_details which popped up for me today). Check that your UI Components' column names are correct!

2. An incorrect or missing main table declared for a type or virtualType. Ensure that di.xml is properly defining tables, for instance:

    <type name="Foo\Bar\Model\ResourceModel\Baz\Grid\Collection">
        <arguments>
            <argument name="mainTable" xsi:type="string">foo_bar_baz</argument>
        </arguments>
    </type>