Billing and Shipping Company Column on Sales Order Grid

Question

I am able to show either the billing or shipping company for an order but I can't seem to grasp how to show both at the same time. Here are my files to show billing company name.

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">
    <preference for="Magento\Sales\Model\ResourceModel\Order\Customer" type="Trucatch\Sales\Model\ResourceModel\Order\Customer" />
    <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
        <plugin name="sales_order_additional_columns" type="Trucatch\Sales\Plugin\AddColumnsSalesOrderGridCollection" sortOrder="100" disabled="false" />
    </type>
</config>

Plugin/AddColumnsSalesOrderGridCollection.php

<?php namespace Trucatch\Sales\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('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">

        <!-- sales_order_address billing_company -->
        <column name="company">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="label" xsi:type="string" translate="true">Bill-to 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>

    </columns>

</listing>

To show the shipping company name I simply change

soa.address_type = "billing"

to

soa.address_type = "shipping"

But I would like to show both at the same time. I was trying something like this and then rename the information to billing_company and shipping_company instead of company. This was I could call them on the sales_order_grid.xml individually.

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

But I am not sure how to proceed in renaming the results for each.

Thanks!

EDIT

Figured out how to rename by using:

array('billing_company' => 'company')

But now I am still trying to add another join instance for the "shipping" row and not having any luck.

Answer 1

Well I finally figured it out! My initial thought of including both in the same join did not work.

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

So I then tried to include 2 separate joins like this:

        $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(
            'main_table.entity_id = soa.parent_id AND soa.address_type = "shipping"',
            array('shipping_company' => 'company')
        )
            ->distinct();

Removed the ["soa" => "sales_order_address"] because it was not allowing me to rename it twice. No big deal, I just removed it. However this was still throwing some syntax errors.

Then I finally came up with this code and now it works perfectly!

        $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();