1

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.

  • I have simplified the code (actually changed it all quite a bit) and I'm in the process of finalizing the module that I have created to show the Company Columns but I am still in need of filtering/searching hte columns on the grids. Can anyone assist? I have tried a few different approaches. Latest was in sales_order_grid.xml, I have <item name="filter" xsi:type="string">text</item>. But that is giving me errors when searching. – RLTcode Apr 18 '18 at 21:53
0

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();
  • Been swamped getting this new site up and running but when I get a chance I will update with the complete working version. Had to make some changes to the above code as it was conflicting with certain modules. May actually just move it to github for others to download. – RLTcode Jan 26 '18 at 0:44
  • Were you added this module on github? Cause getting error in filter for billing and shipping company. – Hardik Feb 21 at 6:17
  • RLTcode? your filter also work? when filter your column text field is there and also filter working? – HaFiz Umer Oct 3 at 6:08
  • not working for me. – HaFiz Umer Oct 3 at 6:51

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.