2

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

7
  • Your column is added when after loading the collection, you have to check the order grid data source rewrite the collection query instead.
    – HoangHieu
    Sep 25, 2020 at 6:30
  • @HoangHieu where should I check it?
    – Cebook.Rha
    Sep 25, 2020 at 6:32
  • vendor/magento/module-sales/view/adminhtml/ui_component/sales_order_grid.xml:28
    – HoangHieu
    Sep 25, 2020 at 6:34
  • vendor/magento/module-sales/etc/di.xml:833
    – HoangHieu
    Sep 25, 2020 at 6:34
  • 1
    Hi @HoangHieu I already add my custom column in _initSelect function. The grid show properly, but I still can't filter my custom column. The error still same "Column not found: 1054 Unknown column 'wof_status' in 'where clause'".
    – Cebook.Rha
    Sep 25, 2020 at 7:04

1 Answer 1

0

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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