M2.1.9 - How to add company column from `sales_order_address` into Sales/Order grid when it comes to joining tables

Question

I was trying to add company column from sales_order_address into Sales/Order grid when it comes to joining tables. But it doesn't work well. Could you review my code? And i referred to this post : How to join order grid collection to custom table in Magento2?

app\code\Kbethos\CompanyColumn\etc\di.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
    <plugin name="sales_order_additional_columns" type="Kbethos\CompanyColumn\Plugins\AddColumnsSalesOrderGridCollection" sortOrder="100" disabled="false" />
</type>
</config>

app\code\Kbethos\CompanyColumn\Plugins\AddColumnsSalesOrderGridCollection.php

<?php namespace Kbethos\CompanyColumn\Plugins;

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

class AddColumnsSalesOrderGridCollection
{
    private $messageManager;
    private $collection;

    public function __construct(MessageManager $messageManager,
        SalesOrderGridCollection $collection
    ) {

        $this->messageManager = $messageManager;
        $this->collection = $collection;
    }

    public function aroundGetReport(
        \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject,
        \Closure $proceed,
        $requestName
    ) {
        $result = $proceed($requestName);
        if ($requestName == 'sales_order_grid_data_source') {
            if ($result instanceof $this->collection
            ) {
                $select = $this->collection->getSelect();
                $select->join(
                    ["soi" => "sales_order_item"],
                    'main_table.entity_id = soi.order_id AND soi.product_type="simple"',
                    array('weight', 'product_type')
                )
                    ->distinct();

                $select->join(
                    ["soa" => "sales_order_address"],
                    'main_table.entity_id = soa.parent_id AND soa.address_type="shipping"',
                    array('email','company' ,'country_id', 'postcode', 'city', 'telephone')
                )
                    ->distinct();
            }

        }
        return $this->collection;
    }
}

app\code\Kbethos\CompanyColumn\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">

        <!-- sales_order_address company -->
        <column name="company">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="label" xsi:type="string" translate="true">Company</item>
                    <item name="sortOrder" xsi:type="number">222</item>
                    <item name="align" xsi:type="string">right</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="visible" xsi:type="boolean">true</item>
                    <!--<item name="bodyTmpl" xsi:type="string">ui/grid/cells/html</item>-->
                </item>
            </argument>
        </column>
    </columns>
</listing>

But error message came like this :

How can i handle this issue? Thank you!

Mary

Answer 1

I got the code to work by replacing the plugin class with this:

class AddColumnsSalesOrderGridCollection
{

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

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

            $select->join(
                ["soi" => "sales_order_item"],
                'main_table.entity_id = soi.order_id AND soi.product_type="simple"',
                array('weight', 'product_type')
            )
                ->distinct();

            $select->join(
                ["soa" => "sales_order_address"],
                'main_table.entity_id = soa.parent_id AND soa.address_type="shipping"',
                array('email','company' ,'country_id', 'postcode', 'city', 'telephone')
            )
                ->distinct();
        }

        return $result;
    }
}