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

Question

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="VENDOR\MODULE\Ui\Component\Listing\Column\Color">
 <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 VENDOR\MODULE\Ui\Component\Listing\Column;
use Magento\Framework\View\Element\UiComponent\ContextInterface;
use Magento\Framework\View\Element\UiComponentFactory;
class Color extends \Magento\Ui\Component\Listing\Columns\Column
{
/**
 * @var \Magento\Catalog\Model\OrderFactory
 */
protected $_orderFactory;
/**
 * @var \Magento\Catalog\Model\ProductRepository
 */
protected $productFactory;
/**
 * 
 * @param ContextInterface $context 
 * @param UiComponentFactory $uiComponentFactory 
 * @param array $components 
 * @param array $data 
 */
public function __construct(
 ContextInterface $context,
 UiComponentFactory $uiComponentFactory,
 \Magento\Sales\Model\OrderFactory $orderFactory,
 \Magento\Catalog\Model\ProductFactory $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): Magento\Framework\DB\Statement\Pdo\Mysql->_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.

• Pramod: ipfs-sec.stackexchange.cloudflare-ipfs.com/magento/A/question/…
• Pramod: look at it may be this could be great help for you

Answer 1

Override "Magento\Sales\Model\ResourceModel\Order\Grid\Collection.php" and then need to create below code in that file.

protected function _initSelect()
 {
 parent::_initSelect();
 //do your sql join like below code
 /*$this->getSelect()->joinLeft(
 ['secondTable' => $this->getTable('secondTable')],
 'main_table.entity_id = secondTable.entity_id',
 array('*')
 );*/
 }

Hope this helps

Answer 2

You are getting this error because the magento is looking for 'color' column in sales_order table which it cannot find.

You will have to either provide custom dataprovider for the column or append these values in the respective rows of the table after adding a new 'color' column.

Answer 3

protected function _initSelect() { parent::_initSelect();

 //do your sql join like below code
 /*$this->getSelect()->joinLeft(
 ['secondTable' => $this->getTable('secondTable')],
 'main_table.entity_id = secondTable.entity_id',
 array('*')
 );*/
}
0

Override "Magento\Sales\Model\ResourceModel\Order\Grid\Collection.php" and then need to create below code in that file.