Magento 2 Add Billing and Shipping Company Column on Sales Order Grid

Question

I have added two new column ('Billing Company', 'Shipping Company') in sales order gird using below code.

di.xml

<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
        <plugin name="sales_order_additional_columns" type="Company\Module\Plugin\AddColumnsSalesOrderGridCollection" sortOrder="100" disabled="false" />
    </type>

Company\Module\Plugin\AddColumnsSalesOrderGridCollection.php

<?php

namespace Company\Module\Plugin;

use Magento\Framework\Message\ManagerInterface as MessageManager;
use Magento\Sales\Model\ResourceModel\Order\Grid\Collection as SalesOrderGridCollection;

class AddColumnsSalesOrderGridCollection {

    public function aroundGetReport($subject, \Closure $proceed, $requestName) {
        $result = $proceed($requestName);

        if ($requestName === "sales_order_grid_data_source") {
            $select = $result->getSelect();

            $select->join(
                            ["soa" => "sales_order_address"], 'main_table.entity_id = soa.parent_id AND soa.address_type = "billing"', array('billing_company' => 'company')
                    )
                    ->distinct();

            $select->join(
                            ["soa2" => "sales_order_address"], 'main_table.entity_id = soa2.parent_id AND soa2.address_type = "shipping"', array('shipping_company' => 'company')
                    )
                    ->distinct();
        }

        return $result;
    }

}

view\adminhtml\ui_component\sales_order_grid.xml

<?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="billing_company">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="label" xsi:type="string" translate="true">Billing Company</item>
                    <item name="sortOrder" xsi:type="number">4</item>
                    <item name="align" xsi:type="string">left</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">true</item>
                </item>
            </argument>
        </column>

        <column name="shipping_company">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="label" xsi:type="string" translate="true">Shipping Company</item>
                    <item name="align" xsi:type="string">left</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">true</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

Using this code i am able to show new fields in order grid. But when i am filtering with either billing or shipping there is error.

[2019-02-21 06:16:35] main.CRITICAL: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'billing_company' in 'where clause', query was: SELECT DISTINCT COUNT() FROM sales_order_grid AS main_table INNER JOIN sales_order_address AS soa ON main_table.entity_id = soa.parent_id AND soa.address_type = "billing" INNER JOIN sales_order_address AS soa2 ON main_table.entity_id = soa2.parent_id AND soa2.address_type = "shipping" WHERE (billing_company LIKE '%xyz%') {"exception":"[object] (Zend_Db_Statement_Exception(code: 42): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'billing_company' in 'where clause', query was: SELECT DISTINCT COUNT() FROM sales_order_grid AS main_table INNER JOIN sales_order_address AS soa ON main_table.entity_id = soa.parent_id AND soa.address_type = \"billing\" INNER JOIN sales_order_address AS soa2 ON main_table.entity_id = soa2.parent_id AND soa2.address_type = \"shipping\" WHERE (billing_company LIKE '%xyz%') at \vendor\magento\zendframework1\library\Zend\Db\Statement\Pdo.php:235, PDOException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'billing_company' in 'where clause' at \vendor\magento\zendframework1\library\Zend\Db\Statement\Pdo.php:228)"} []

Answer 1

Try this:

class AddColumnsSalesOrderGridCollection
{

    public function aroundGetReport(
        \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject,
        \Closure $proceed,
        $requestName
    ) {
        $collection = $proceed($requestName);

        if ($requestName === "sales_order_grid_data_source") {
            if ($collection instanceof \Magento\Sales\Model\ResourceModel\Order\Grid\Collection) {
                $collection->getSelect()->join(
                    ["soa" => "sales_order_address"], 'main_table.entity_id = soa.parent_id AND soa.address_type = "billing"', array('billing_company' => 'company')
                )
                    ->distinct();

                $collection->getSelect()->join(
                    ["soa2" => "sales_order_address"], 'main_table.entity_id = soa2.parent_id AND soa2.address_type = "shipping"', array('shipping_company' => 'company')
                )
                    ->distinct();
            }
        }

        return $collection;
    }
}

[Update]

Add preference:

app/code/SR/MagentoCommunity/etc/adminhtml/di.xml

<preference for="Magento\Sales\Model\ResourceModel\Order\Grid\Collection" type="SR\MagentoCommunity\Model\Sales\ResourceModel\Order\Grid\Collection"/>

app/code/SR/MagentoCommunity/Model/Sales/ResourceModel/Order/Grid/Collection.php

<?php
namespace SR\MagentoCommunity\Model\Sales\ResourceModel\Order\Grid;

class Collection extends \Magento\Sales\Model\ResourceModel\Order\Grid\Collection
{
    protected function _construct()
    {
        parent::_construct();
        $this->_map['fields']['shipping_company'] = 'soa2.company';
        $this->_map['fields']['billing_company'] = 'soa.company';
    }
}