Magento 2: Sorting is not working while adding custom column in Order Grid Using Ui Component

I want to add a custom column in order grid with sorting enabled in it.

For now, I am including a column which shows the color attribute of the product in that order.

This is the file sales_order_grid.xml file created in app/code/VENDOR/MODULE/view/adminhtml/ui_component/

below is the code

<?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">
<columns name="sales_order_columns">
    <column name="color" class="VENDORMODULEUiComponentListingColumnColor">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="label" xsi:type="string" translate="true">Color</item>
                <item name="sortable" xsi:type="boolean">false</item>
                <item name="filter" xsi:type="string">text</item>
            </item>
        </argument>
    </column>
</columns>
</listing>

And app/code/VENDOR/MODULE/Ui/Component/Listing/Column/Color.php

<?php
namespace VENDORMODULEUiComponentListingColumn;
use MagentoFrameworkViewElementUiComponentContextInterface;
use MagentoFrameworkViewElementUiComponentFactory;
class Color extends MagentoUiComponentListingColumnsColumn
{
/**
 * @var MagentoCatalogModelOrderFactory
 */
protected $_orderFactory;

/**
 * @var MagentoCatalogModelProductRepository
 */
protected $productFactory;

/**
 * 
 * @param ContextInterface   $context           
 * @param UiComponentFactory $uiComponentFactory   
 * @param array              $components        
 * @param array              $data              
 */
public function __construct(
    ContextInterface $context,
    UiComponentFactory $uiComponentFactory,
    MagentoSalesModelOrderFactory $orderFactory,
    MagentoCatalogModelProductFactory $productFactory,
    array $components = [],
    array $data = []
) {
    parent::__construct($context, $uiComponentFactory, $components, $data);
    $this->_orderFactory = $orderFactory;
    $this->_productFactory = $productFactory;
}

/**
 * Prepare Data Source
 *
 * @param array $dataSource
 * @return array
 */
public function prepareDataSource(array $dataSource)
{
    if (isset($dataSource['data']['items'])) {
        foreach ($dataSource['data']['items'] as & $item) {
            //Get order details by order id.
            $order = $this->_orderFactory->create()->load($item['entity_id']);

            //Get all products in this order.
            $products = $order->getAllItems();
            foreach ($products as $product) {
                $productId = $product->getProductId();
                $productData = $this->_productFactory->create()->load($productId);
                $item['color'] = $productData->getResource()->getAttribute('color')->getFrontend()->getValue($productData);
            }
        }
    }

    return $dataSource;
}
}

Now when I perform sorting over this it gives following as color column is not related to order:

2 exception(s): Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'color' in 'order clause', query was: SELECT `main_table`.* FROM `sales_order_grid` AS `main_table` ORDER BY color ASC  LIMIT 20 Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'color' in 'order clause'

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'color' in 'order clause', query was: SELECT `main_table`.* FROM `sales_order_grid` AS `main_table` ORDER BY color ASC  LIMIT 20
#0 /opt/lampp/htdocs/magento2/vendor/magento/framework/DB/Statement/Pdo/Mysql.php(95): Zend_Db_Statement_Pdo->_execute(Array)
#1 /opt/lampp/htdocs/magento2/vendor/magento/zendframework1/library/Zend/Db/Statement.php(303): MagentoFrameworkDBStatementPdoMysql->_execute(Array)
#2 /opt/lampp/htdocs/magento2/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /opt/lampp/htdocs/magento2/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `main_ta...', Array)

Same case when applying a filter, so how to perform this.


Go to Source of this post
Author Of this post: user56654
Title Of post: Magento 2: Sorting is not working while adding custom column in Order Grid Using Ui Component
Author Link: {authorlink}