Orsdxy

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.

Override "MagentoSalesModelResourceModelOrderGridCollection.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

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.