Add custom product attribute to Order Grid

Question

I have an custom product attribute shipper imported with all the products, which show that the product shipper. I want to show this attribute on the Order Grid with values to know that which order belong to which shipper.

How could I do this?

Answer 1

A way to do this override sales_order_grid.xml to your own module:-

<columns name="sales_order_columns" class="Magento\Catalog\Ui\Component\Listing\Columns">
        <column name="full_name" class="companyname\Custom\Ui\Component\Listing\Column\Fullname">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Full Name</item>
                </item>
            </argument>
        </column>
    </columns>

Now create the file at companyname\Custom\Ui\Component\Listing\Column\Fullname.php. Your filename will be different this as was my requirement

<?php 
namespace companyname\Custom\Ui\Component\Listing\Column;

use \Magento\Sales\Api\OrderRepositoryInterface;
use \Magento\Framework\View\Element\UiComponent\ContextInterface;
use \Magento\Framework\View\Element\UiComponentFactory;
use \Magento\Ui\Component\Listing\Columns\Column;
use \Magento\Framework\Api\SearchCriteriaBuilder;

class Fullname extends Column
{


    public function prepareDataSource(array $dataSource)
    {
        if (isset($dataSource['data']['items'])) {
            foreach ($dataSource['data']['items'] as & $item) {

                $order  = $item["entity_id"];
                //Apply your algorithm here to fetch product attr from order id
            }
        }

        return $dataSource;
    }
}

Answer 2

I got another solution to do this.

In my di.xml

<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
    <plugin name="sales_order_additional_columns" type="Vendor\Module\Plugin\AddColumnsSalesOrderGridCollection" />
</type>

Override the file CollectionFactory.php

public function aroundGetReport(
    \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject,
    \Closure $proceed,
    $requestName
) {
    $objectManager = \Magento\Framework\App\ObjectManager::getInstance();
    $collection = $objectManager->create('Magento\Sales\Model\ResourceModel\Order\Grid\Collection');
    $result = $proceed($requestName);
    if ($requestName == 'sales_order_grid_data_source') {
        if ($result instanceof $collection) {
            $collection->getSelect()->join(
                 ["soi" => "sales_order_item"],
                 'main_table.entity_id = soi.order_id',
                 ['sku' => 'GROUP_CONCAT(DISTINCT soi.sku)',    
                  'name' => 'GROUP_CONCAT(DISTINCT soi.name)',
                  'tax_percent' => 'soi.tax_percent',
                  'discount_percent' => 'soi.discount_percent'
                 ]
             )->join(
                    ['cpev' => 'catalog_product_entity_varchar'],
                    'soi.product_id = cpev.entity_id',
                    ['value' => 'GROUP_CONCAT(cpev.value)']
                )->where('cpev.attribute_id = shipper_id attribute id')
                    ->group("soi.order_id");
        }

        return $collection;
    }
    else{
        return $result;
    }

}

File

sales_order_grid.xml

<column name="sku">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="label" xsi:type="string" translate="true">Sku</item>
                <item name="filter" xsi:type="string">text</item>
                <item name="visible" xsi:type="boolean">true</item>
            </item>
        </argument>
    </column>
    <column name="name">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="label" xsi:type="string" translate="true">Product Name</item>
                <item name="filter" xsi:type="string">text</item>
                <item name="visible" xsi:type="boolean">true</item>
            </item>
        </argument>
    </column>
    <column name="tax_percent">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="label" xsi:type="string" translate="true">Tax Percent</item>
                <item name="filter" xsi:type="string">text</item>
                <item name="visible" xsi:type="boolean">true</item>
            </item>
        </argument>
    </column>
    <column name="discount_percent">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="label" xsi:type="string" translate="true">Discount Percent</item>
                <item name="filter" xsi:type="string">text</item>
                <item name="visible" xsi:type="boolean">true</item>
            </item>
        </argument>
    </column>
    <column name="value">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="filter" xsi:type="string">text</item>
                <item name="label" xsi:type="string" translate="true">Shipper ID</item>
            </item>
        </argument>
    </column>

This works fine and give me all the values on export, but I got an error when I trying to export the order by selecting an Order with check-box and export

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'entity_id' in where clause is ambiguous, query was: SELECT COUNT(DISTINCT soi.order_id) FROM sales_order_grid AS main_table INNER JOIN sales_order_item AS soi ON main_table.entity_id = soi.order_id INNER JOIN catalog_product_entity_varchar AS cpev ON soi.product_id = cpev.entity_id WHERE (cpev.attribute_id = 178) AND (entity_id IN('98'))