0

I was trying to add company column from sales_order_address into Sales/Order grid when it comes to joining tables. But it doesn't work well. Could you review my code? And i referred to this post : How to join order grid collection to custom table in Magento2?

app\code\Kbethos\CompanyColumn\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">
    <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
    <plugin name="sales_order_additional_columns" type="Kbethos\CompanyColumn\Plugins\AddColumnsSalesOrderGridCollection" sortOrder="100" disabled="false" />
</type>
</config>

app\code\Kbethos\CompanyColumn\Plugins\AddColumnsSalesOrderGridCollection.php

<?php namespace Kbethos\CompanyColumn\Plugins;

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

class AddColumnsSalesOrderGridCollection
{
    private $messageManager;
    private $collection;

    public function __construct(MessageManager $messageManager,
        SalesOrderGridCollection $collection
    ) {

        $this->messageManager = $messageManager;
        $this->collection = $collection;
    }

    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 $this->collection
            ) {
                $select = $this->collection->getSelect();
                $select->join(
                    ["soi" => "sales_order_item"],
                    'main_table.entity_id = soi.order_id AND soi.product_type="simple"',
                    array('weight', 'product_type')
                )
                    ->distinct();

                $select->join(
                    ["soa" => "sales_order_address"],
                    'main_table.entity_id = soa.parent_id AND soa.address_type="shipping"',
                    array('email','company' ,'country_id', 'postcode', 'city', 'telephone')
                )
                    ->distinct();
            }

        }
        return $this->collection;
    }
}

app\code\Kbethos\CompanyColumn\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 company -->
        <column name="company">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="label" xsi:type="string" translate="true">Company</item>
                    <item name="sortOrder" xsi:type="number">222</item>
                    <item name="align" xsi:type="string">right</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>

But error message came like this :

enter image description here

How can i handle this issue? Thank you!

Mary

0

I got the code to work by replacing the plugin class with this:

class AddColumnsSalesOrderGridCollection
{

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

        if ($requestName === "sales_order_grid_data_source") {
            $select = $result->getSelect();

            $select->join(
                ["soi" => "sales_order_item"],
                'main_table.entity_id = soi.order_id AND soi.product_type="simple"',
                array('weight', 'product_type')
            )
                ->distinct();

            $select->join(
                ["soa" => "sales_order_address"],
                'main_table.entity_id = soa.parent_id AND soa.address_type="shipping"',
                array('email','company' ,'country_id', 'postcode', 'city', 'telephone')
            )
                ->distinct();
        }

        return $result;
    }
}
  • I got same error message tho – 3CE Oct 11 '17 at 13:15
  • After delete SOI and SOA array except company, it works! Btw, do you know how to move new column to the position where i want to put it in! I used to adjust sort order with 0 but it doesn't work! – 3CE Oct 11 '17 at 15:09
  • You should be able to drag and drop the column header to the position you want. – Aaron Allen Oct 11 '17 at 20:10
  • Aww i didn't know that. By the way, the loading time is way too long. Do you know more effective way to implement this issue? – 3CE Oct 11 '17 at 21:41
  • please refer my another post : magento.stackexchange.com/questions/196846/… – 3CE Oct 11 '17 at 21:42

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.