0

I am trying to add SKU in sales order gird and also want to make it filterable. This is the code that i am using. I am able to add SKU in order grid and it is also working with sku filter as well. But there is issue when i filter with date range.

Company\Module\Plugin\AddColumnsSalesOrderGridCollection.php

<?php

namespace Company\Module\\Plugin;

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

class AddColumnsSalesOrderGridCollection {

    public function aroundGetReport(
    \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject, \Closure $proceed, $requestName
    ) {

        $collection = $proceed($requestName);

        if ($requestName === "sales_order_grid_data_source") {
            if ($collection instanceof \Magento\Sales\Model\ResourceModel\Order\Grid\Collection) {
                $collection->getSelect()->join(
                                ["soa" => "sales_order_address"], 'main_table.entity_id = soa.parent_id AND soa.address_type = "billing"', array('billing_company' => 'company', 'billing_phone' => 'telephone')
                        )
                        ->distinct();

                $collection->getSelect()->join(
                                ["soa2" => "sales_order_address"], 'main_table.entity_id = soa2.parent_id AND soa2.address_type = "shipping"', array('shipping_company' => 'company', 'shipping_phone' => 'telephone')
                        )
                        ->distinct();

                $collection->getSelect()->join(array(
                    'soa3' => $collection->getTable('sales_order_item')), 'soa3.order_id=`main_table`.entity_id', array(
                    'skus' => new \Zend_Db_Expr('group_concat(soa3.sku SEPARATOR ", <br> ")')
                )); //->group('soa3.order_id');
                $collection->getSelect()->group('main_table.entity_id');
            }
        }

        return $collection;
    }

}

Company\Module\Model\Sales\ResourceModel\Order\Grid\Collection.php

<?php
namespace Company\Module\\Model\Sales\ResourceModel\Order\Grid;

class Collection extends \Magento\Sales\Model\ResourceModel\Order\Grid\Collection
{
    protected function _construct()
    {
        parent::_construct();
        $this->_map['fields']['shipping_company'] = 'soa2.company';
        $this->_map['fields']['shipping_phone'] = 'soa2.telephone';
        $this->_map['fields']['billing_company'] = 'soa.company';
        $this->_map['fields']['billing_phone'] = 'soa.telephone';
        $this->_map['fields']['skus'] = 'soa3.sku';
    }
}

Company\Module\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">
        <column name="billing_company">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="label" xsi:type="string" translate="true">Billing 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>

        <column name="shipping_company">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="label" xsi:type="string" translate="true">Shipping Company</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>

        <column name="billing_phone">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="label" xsi:type="string" translate="true">Billing Phone</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>

        <column name="shipping_phone">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="label" xsi:type="string" translate="true">Shipping Phone</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>

        <column name="skus">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="label" xsi:type="string" translate="true">Product ID</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 name="bodyTmpl" xsi:type="string">ui/grid/cells/html</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

Above code working fine to display SKU in sale order grid and working well for sku filter as well. But getting issue when i filter with order Purchase Date.

Here is the error in log file.

[2019-03-28 06:15:08] main.CRITICAL: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous, query was: SELECT DISTINCT COUNT(DISTINCT main_table.entity_id) FROM sales_order_grid AS main_table INNER JOIN sales_order_address AS soa ON main_table.entity_id = soa.parent_id AND soa.address_type = "billing" INNER JOIN sales_order_address AS soa2 ON main_table.entity_id = soa2.parent_id AND soa2.address_type = "shipping" INNER JOIN sales_order_item AS soa3 ON soa3.order_id=main_table.entity_id WHERE (created_at >= '2019-03-01 00:00:00') AND (created_at <= '2019-03-31 23:59:59') AND (created_at >= '2019-03-01 00:00:00') AND (created_at <= '2019-03-31 23:59:59') {"exception":"[object] (Zend_Db_Statement_Exception(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous, query was: SELECT DISTINCT COUNT(DISTINCT main_table.entity_id) FROM sales_order_grid AS main_table INNER JOIN sales_order_address AS soa ON main_table.entity_id = soa.parent_id AND soa.address_type = \"billing\" INNER JOIN sales_order_address AS soa2 ON main_table.entity_id = soa2.parent_id AND soa2.address_type = \"shipping\" INNER JOIN sales_order_item AS soa3 ON soa3.order_id=main_table.entity_id WHERE (created_at >= '2019-03-01 00:00:00') AND (created_at <= '2019-03-31 23:59:59') AND (created_at >= '2019-03-01 00:00:00') AND (created_at <= '2019-03-31 23:59:59') at vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:235, PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous at vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228)"} []

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.