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
ASmain_table
LEFT JOINspintowin_customer
ASsc
ON sc.order_id = main_table.entity_id
LEFT JOINspintowin_info
ASsi
ON sc.spin_id = si.entity_id
LEFT JOINsales_order
ASso
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