Company Column on Sales Grids (Order, Invoice, Shipment, Credit Memo)

Question

This is a continuation from my previous question: Billing and Shipping Company Column on Sales Order Grid

I was able to place the Ship-to and Bill-to Company Columns on the 4 Sales Grids, but I am having an issue with using the Filter options for the columns. When I try to search using the filter, I receive an error (Attention Something went wrong" but I can't find any more information on this error. Nothing is showing in the logs. The search box does not appear to be searching these new columns either.

First, I have tried adding the billing_company and shipping_company columns to the sales_order_grid, but I was unsuccessful trying to populate the columns from the sales_order_address table. However with the columns in sales_order_grid, I was not receiving the error anymore.

I have also tried including the columns in the search criteria since it would appear all filtering only searches the sales_order_grid table. But I fear I was unsuccessful as well.

Also for some odd reason the company names do not line up with the order for Invoice, Shipment and Credit Memo? I don't see any reason that particular company name is being associated with the wrong Invoice, Shipment, or Credit Memo. Do I need to have a separate Plugin for each Grid?

Please have a look at my code and hopefully someone can point me in the right direction.

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>

    <!-- Sync the column "company" between the sales_order_address and sales_order_grid tables -->
    <virtualType name="Magento\Sales\Model\ResourceModel\Order\Grid" type="Magento\Sales\Model\ResourceModel\Grid">
        <arguments>
            <argument name="mainTableName" xsi:type="string">sales_order</argument>
            <argument name="gridTableName" xsi:type="string">sales_order_grid</argument>
            <argument name="orderIdField" xsi:type="string">sales_order.entity_id</argument>
            <argument name="joins" xsi:type="array">
                <item name="sales_shipping_address" xsi:type="array">
                    <item name="table" xsi:type="string">sales_order_address</item>
                    <item name="origin_column" xsi:type="string">shipping_address_id</item>
                    <item name="target_column" xsi:type="string">entity_id</item>
                </item>
                <item name="sales_billing_address" xsi:type="array">
                    <item name="table" xsi:type="string">sales_order_address</item>
                    <item name="origin_column" xsi:type="string">billing_address_id</item>
                    <item name="target_column" xsi:type="string">entity_id</item>
                </item>
            </argument>
        </arguments>
    </virtualType>

    <virtualType name="ShippingAddressAggregator" type="Magento\Framework\DB\Sql\ConcatExpression">
        <arguments>
            <argument name="columns" xsi:type="array">
                <item name="company" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_shipping_address</item>
                    <item name="columnName" xsi:type="string">company</item>
                </item>
            </argument>
        </arguments>
    </virtualType>
    <virtualType name="BillingAddressAggregator" type="Magento\Framework\DB\Sql\ConcatExpression">
        <arguments>
            <argument name="columns" xsi:type="array">
                <item name="company" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">company</item>
                </item>
            </argument>
        </arguments>
    </virtualType>

    <virtualType name="ShippingNameAggregator" type="Magento\Framework\DB\Sql\ConcatExpression">
        <arguments>
            <argument name="columns" xsi:type="array">
                <item name="company" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_shipping_address</item>
                    <item name="columnName" xsi:type="string">company</item>
                </item>
            </argument>
        </arguments>
    </virtualType>
    <virtualType name="BillingNameAggregator" type="Magento\Framework\DB\Sql\ConcatExpression">
        <arguments>
            <argument name="columns" xsi:type="array">
                <item name="company" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">company</item>
                </item>
            </argument>
        </arguments>
    </virtualType>  

</config>

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">

    <!-- "sales_order_grid_data_source" -->
    <columns name="sales_order_columns">

        <!-- sales_order_address billing_company -->
        <column name="billing_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>

        <!-- sales_order_address shipping_company -->
        <column name="shipping_company">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="label" xsi:type="string" translate="true">Ship-to Company</item>
                    <item name="sortOrder" xsi:type="number">6</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>

AddColumnsSalesOrderGridCollection.php

<?php namespace Trucatch\Sales\Plugin;

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

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

        if ($requestName = array("sales_order_grid_data_source", 
            "sales_order_creditmemo_grid_data_source",
            "sales_order_invoice_grid_data_source",
            "sales_order_shipment_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;
    }
}


// "sales_order_view_shipment_grid_data_source"
// "sales_order_view_invoice_grid_data_source"
// "sales_order_view_creditmemo_grid_data_source"

UpgradeSchema.php

<?php

namespace Trucatch\Sales\Setup;

use Magento\Framework\Setup\UpgradeSchemaInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;

class UpgradeSchema implements UpgradeSchemaInterface
{
    /**
     * Upgrades DB schema for a CompanyColumn
     *
     * @param SchemaSetupInterface $setup
     * @param ModuleContextInterface $context
     * @return void
     */
    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $setup->startSetup();
        if (version_compare($context->getVersion(), '1.0.4') < 0) {

            // Get module table
            $orderGridTable = $setup->getTable('sales_order_grid');

            // Check if the table already exists
            if ($setup->getConnection()->isTableExists($orderGridTable) == true) {
                // Declare data
                $columns = [
                    'billing_company' => [
                        'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                        'nullable' => true,
                        'length' => 255,
                        'comment' => 'Billing Company',
                    ],
                    'shipping_company' => [
                        'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                        'nullable' => true,
                        'length' => 255,
                        'comment' => 'Shipping Company',
                    ],
                ];

                $connection = $setup->getConnection();
                foreach ($columns as $name => $definition) {
                    $connection->addColumn($orderGridTable, $name, $definition);
                }

            }
        }
        $setup->endSetup();
    }
}

---

EDIT

I have since modified the code quite a bit to simplify it. Still working on getting the Company Columns to be used in the filters tab though. I have included <item name="filter" xsi:type="string">text</item> in the sales_order_grid.xml. Which shows the field but when I search the Company Column on Magento backend, I receive this error: "Something went wrong with processing current custom view and filters have been reset to its original state. Please edit filters then click apply." Which I then looked in the logs and found a full print out of the error:

[2018-08-01 16:02:57] main.CRITICAL: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'billing_company' in 'where clause', query was: SELECT COUNT(*) FROM `sales_order_grid` AS `main_table`
LEFT JOIN `sales_order_address` AS `soa1` ON main_table.entity_id = soa1.parent_id AND soa1.address_type = "billing"
LEFT JOIN `sales_order_address` AS `soa2` ON main_table.entity_id = soa2.parent_id AND soa2.address_type = "shipping"
LEFT JOIN `shipperhq_order_detail_grid` AS `shipper_order_join` ON main_table.entity_id=shipper_order_join.order_id WHERE (`billing_company` LIKE '%animal%') {"exception":"[object] (Zend_Db_Statement_Exception(code: 42): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'billing_company' in 'where clause', query was: SELECT COUNT(*) FROM `sales_order_grid` AS `main_table`
LEFT JOIN `sales_order_address` AS `soa1` ON main_table.entity_id = soa1.parent_id AND soa1.address_type = \"billing\"
LEFT JOIN `sales_order_address` AS `soa2` ON main_table.entity_id = soa2.parent_id AND soa2.address_type = \"shipping\"
LEFT JOIN `shipperhq_order_detail_grid` AS `shipper_order_join` ON main_table.entity_id=shipper_order_join.order_id WHERE (`billing_company` LIKE '%animal%') at /home/mag22tru/domains/dev/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 /home/mag22tru/domains/dev/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228)"} []

Does anyone happen to know of a work around to get these columns to be used in the filters tab? I think it is looking in the wrong database table when filtering. With this feature, the module will be complete and I can post for anyone else looking to add this functionality.