0

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)"} []

2

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';
    }
}
  • Thanks for that answer. But Still same error. – Hardik Feb 21 at 7:32
  • Check updated answer. – Sohel Rana Feb 21 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.