Company Column on Sales Order/Invoice/CreditMemo/Shipment Grid

Question

I have built a module to show the Billing and Shipping Company Name in the Admin Grids for Order, Invoice, CreditMemo, and Shipment. I still can't believe this is not built into Magento2.

At first I was extending Sales/Model/ResourceModel/Order/Grid/Collection using _renderFiltersBefore and joinLeft, but I decided to convert it to a Plugin instead and try to use aroundGetReport.

Which both were working just fine, until I added a third-party module to handle our shipping costs. This third-party module is also joining tables to be added to the Sales Order Grid, which now it's conflicting with my module. I was able to get the Sales/Model/ResourceModel/Order/Grid/Collection using _renderFiltersBefore version to work with the ShipperHQ module.

But, I also added a third-party Gift Registry module which has it's own Sales Order Grid within the module to show placed orders for each gift registry. This is also conflicting because it is using entity_id from sales_order_grid as well.

However, now that I have moved to a Plugin, I cannot seem to get _renderFiltersBefore to work (display data). Plus I was unable to get the data to show on the Gift Registry grid with either solution.

Error: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'entity_id' in where clause is ambiguous

Below are the files which work correctly when I have the 2 third party modules disabled.

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

<!-- Add Company Name to Customer Grid -->
<preference for="Magento\Sales\Model\ResourceModel\Order\Customer" type="Trucatch\Sales\Model\ResourceModel\Order\Customer" />

<!-- Add Company Name to Order Grids -->
<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
    <plugin name="sales_order_additional_columns" type="Trucatch\Sales\Plugin\AddColumnsSalesOrderGridCollection" sortOrder="100" disabled="false" />
    <plugin name="sales_invoice_additional_columns" type="Trucatch\Sales\Plugin\AddColumnsSalesInvoiceGridCollection" sortOrder="100" disabled="false" />
    <plugin name="sales_shipment_additional_columns" type="Trucatch\Sales\Plugin\AddColumnsSalesShipmentGridCollection" sortOrder="100" disabled="false" />
    <plugin name="sales_creditmemo_additional_columns" type="Trucatch\Sales\Plugin\AddColumnsSalesCreditMemoGridCollection" sortOrder="100" disabled="false" />
    <plugin name="sales_view_invoice_additional_columns" type="Trucatch\Sales\Plugin\AddColumnsSalesViewInvoiceGridCollection" sortOrder="100" disabled="false" />
    <plugin name="sales_view_shipment_additional_columns" type="Trucatch\Sales\Plugin\AddColumnsSalesViewShipmentGridCollection" sortOrder="100" disabled="false" />
    <plugin name="sales_view_creditmemo_additional_columns" type="Trucatch\Sales\Plugin\AddColumnsSalesViewCreditMemoGridCollection" sortOrder="100" disabled="false" />
</type>

<!-- Adjust Order # Length -->
<type name="Magento\SalesSequence\Model\Sequence">
    <arguments>
        <argument name="pattern" xsi:type="string"><![CDATA[%s%'.05d%s]]></argument>
    </arguments>
</type>

</config>

AddColumnsSalesOrderGridCollection.php

<?php namespace Trucatch\Sales\Plugin;

class AddColumnsSalesOrderGridCollection
{

private $resource;

public function __construct(
    \Magento\Framework\App\ResourceConnection $resource
) {
    $this->resource = $resource;
}

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 \Magento\Sales\Model\ResourceModel\Order\Grid\Collection) {
            $select = $result->getSelect();
            $select->joinLeft(
                ['soa1' => $this->resource->getTableName('sales_order_address')],
                'main_table.entity_id = soa1.parent_id AND soa1.address_type = "billing"',
                array('billing_company' => 'company')
            )
            ->distinct();
            $select->joinLeft(
                ['soa2' => $this->resource->getTableName('sales_order_address')],
                'main_table.entity_id = soa2.parent_id AND soa2.address_type = "shipping"',
                array('shipping_company' => 'company')
            )
            ->distinct();
        }
    }
    return $result;
}
}

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>

Now the file from ShipperHQ that is causing the conflicts

CollectionFactory.php

<?php

namespace ShipperHQ\Shipper\Plugin\Order;

class CollectionFactory
{
/**
 * @var \ShipperHQ\Shipper\Helper\LogAssist
 */
private $shipperLogger;
/** @var \Magento\Framework\App\ResourceConnection */
private $resource;

public function __construct(
    \ShipperHQ\Shipper\Helper\LogAssist $shipperLogger,
    \Magento\Framework\App\ResourceConnection $resource
) {
    $this->shipperLogger = $shipperLogger;
    $this->resource = $resource;
}

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 \Magento\Sales\Model\ResourceModel\Order\Grid\Collection) {
            $select = $result->getSelect();
            $select->joinLeft(
                ['shipper_order_join' => $this->resource->getTableName('shipperhq_order_detail_grid')],
                'entity_id' . '=shipper_order_join.' . 'order_id',
                []
            );
        }
    }
    return $result;
}
}

The other conflicting file from the Gift Registry module

Orders.php

<?php

namespace Mirasvit\Giftr\Block\Adminhtml\Registry\Edit\Tab;

use Magento\Backend\Block\Widget\Tab\TabInterface;

class Orders extends \Magento\Backend\Block\Widget\Grid\Extended
implements TabInterface
{
/**
 * @var \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $collectionFactory
 */
protected $collectionFactory;

/**
 * @var \Magento\Framework\Registry
 */
protected $registry;

/**
 * @var \Magento\Backend\Block\Widget\Context
 */
protected $context;

/**
 * @param \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $collectionFactory
 * @param \Magento\Framework\Registry $registry
 * @param \Magento\Backend\Block\Template\Context $context
 * @param array $data
 */
public function __construct(
    \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $collectionFactory,
    \Magento\Framework\Registry $registry,
    \Magento\Backend\Block\Template\Context $context,
    \Magento\Backend\Helper\Data $backendHelper,
    array $data = []
) {
    $this->collectionFactory = $collectionFactory;
    $this->registry = $registry;
    $this->context = $context;
    parent::__construct($context, $backendHelper, $data);
}

/**
 * @return void
 */
protected function _construct()
{
    parent::_construct();
    $this->setDefaultSort('created_at', 'desc');
    $this->setId('edit_tab_orders');
    $this->setSaveParametersInSession(true);
    $this->setUseAjax(true);
}

/**
 * Prepare collection for grid
 *
 * @return $this
 */
protected function _prepareCollection()
{
    $registry = $this->registry->registry('current_registry');
    $orderIds = ($registry->hasOrders()) ? $registry->getOrderIds() : [0];
    $collection = null;

    $collection = $this->collectionFactory->getReport('sales_order_grid_data_source')
        ->addFieldToSelect(
            [
                'entity_id',
                'increment_id',
                'created_at',
                'grand_total',
                'billing_name',
                'shipping_name',
                'status'
            ]
        )
        ->addFieldToFilter('entity_id', ['in', $orderIds]);


    $this->setCollection($collection);
    parent::_prepareCollection();

    $this->prepareTotals(['grand_total' => 0]);

    return $this;
}

/**
 * Prepare total row for grid.
 *
 * @param array $fields - fields to count with default values
 *
 * @return $this
 */
private function prepareTotals(array $fields = [])
{
    $totals = new \Magento\Framework\DataObject();
    foreach ($this->getCollection() as $item) {
        foreach ($fields as $field => $value) {
            $fields[$field] += $item->getData($field);
        }
    }

    $totals->setData($fields);

    $this->setCountTotals(true);
    $this->setTotals($totals);

    return $this;
}

/**
 * Prepare grid columns
 *
 * @return $this
 */
protected function _prepareColumns()
{
    $this->addColumn('increment_id', [
        'header' => __('Order #'),
        'index' => 'increment_id',
        'align' => 'center',
        'width' => '100px',
        'totals_label' => __('Total'),
    ]);

    $this->addColumn('order_created_at', [
        'header' => __('Purchased On'),
        'index' => 'created_at',
        'type' => 'datetime',
    ]);

    $this->addColumn('billing_name', [
        'header' => __('Bill to Name'),
        'index' => 'billing_name',
    ]);

    $this->addColumn('shipping_name', [
        'header' => __('Ship to Name'),
        'index' => 'shipping_name',
    ]);

    $this->addColumn('status', [
        'header' => __('Order Status'),
        'index' => 'status',
        'width' => '100px',
    ]);

    $this->addColumn('grand_total', [
        'header' => __('Order Total'),
        'index' => 'grand_total',
        'type' => 'currency',
        'currency' => 'order_currency_code',
    ]);

    $this->addColumn('action', [
        'header' => __('Action'),
        'align' => 'center',
        'filter' => false,
        'sortable' => false,
        'width' => '100px',
        'type' => 'action',
        'getter' => 'getId',
        'header_css_class' => 'a-center',
        'totals_label' => '',
        'actions' => [
            [
                'url' => ['base' => 'sales/order/view/'],
                'caption' => __('View'),
                'field' => 'order_id',
            ],
        ],
    ]);

    return parent::_prepareColumns();
}

/**
 * @param \Magento\Sales\Model\Order|\Magento\Framework\DataObject $row
 *
 * @return string
 */
public function getRowUrl($row)
{
    return $this->getUrl('sales/order/view', ['order_id' => $row->getId()]);
}

/**
 * Used for AJAX loading
 *
 * @return string
 */
public function getGridUrl()
{
    return $this->getUrl('*/*/grid', ['_current' => true, 'block_id' => $this->getId()]);
}

public function getTabLabel()
{
    return __('Orders');
}

public function getTabTitle()
{
    return $this->getTabLabel();
}

public function canShowTab()
{
    return true;
}

public function isHidden()
{
    return false;
}
}

Does anyone know a workaround so I can use my module alongside the third-party modules I have installed? In short, there are multiple modules trying to use the entity_id column from sales_order_grid table.

Please let me know if you need any additional information.

Thanks!

Answer 1

This question was resolved by both third-party module creators. Which both were simple solutions.

ShipperHQ - CollectionFactory.php

Replaced this:

$select->joinLeft(
            ['shipper_order_join' => $this->resource->getTableName('shipperhq_order_detail_grid')],
            'entity_id' . '=shipper_order_join.' . 'order_id',
            []
        );

With this:

$select->joinLeft(
            ['shipper_order_join' => $this->resource->getTableName('shipperhq_order_detail_grid')],
            'main_table.entity_id' . '=shipper_order_join.' . 'order_id',
            []
        );

Mirasvit - Gift Registry - Orders.php

Replaced this:

->addFieldToFilter('entity_id', ['in', $orderIds]);

With this:

->addFieldToFilter('main_table.entity_id', ['in' => $orderIds]);

Now all is well! No conflicts!