I can see attribute in Catalog -> Products grid but how to Shown Product attribute in Sales -> Orders grid in magento 2

Question

I have created a custom attribute "item location" for every product. It was created in Stores -> Attributes -> Product, then assigned to the default attribute set.

So every item has its own location in the warehouse which is shown in this attribute. I can see this attribute in Catalog -> Products grid, but I would like these data in Sales Orders grid as well. Any idea?

Answer 1

Try following way:

app/code/SR/MagentoCommunity/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="join_sales_order_grid_with_product"
                type="SR\MagentoCommunity\Plugin\UiComponent\DataProvider\CollectionFactory" sortOrder="100" />
    </type>
</config>

app/code/SR/MagentoCommunity/Plugin/UiComponent/DataProvider/CollectionFactory.php

<?php
namespace SR\MagentoCommunity\Plugin\UiComponent\DataProvider;

use Magento\Sales\Model\ResourceModel\Order\Grid\Collection as OrderGridCollection;

class CollectionFactory
{
    public function afterGetReport(
        \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject,
        $collection,
        $requestName
    ) {
        if ($requestName == 'sales_order_grid_data_source') {
            if ($collection instanceof OrderGridCollection) {
                $collection->getSelect()
                    ->join(
                        $collection->getTable('sales_order_item'),
                        'main_table.entity_id = '.$collection->getTable('sales_order_item').'.order_id',
                        [
                            'product_skus' => new \Zend_Db_Expr('GROUP_CONCAT(`sales_order_item`.sku SEPARATOR "|")'),
                            'product_names' => new \Zend_Db_Expr('GROUP_CONCAT(`sales_order_item`.name SEPARATOR "|")')
                        ]
                    )
                    ->where('parent_item_id IS NULL')
                    ->group('main_table.entity_id');
            }
        }

        return $collection;
    }
}

If you manage product sku in grid, then you can avoid above two steps.

Now how to display in grid:

app/code/SR/MagentoCommunity/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="item_location" class="SR\MagentoCommunity\Ui\Component\Listing\Column\ItemLocation">
            <settings>
                <filter>textRange</filter>
                <label translate="true">Item Location</label>
                <visible>true</visible>
                <filter>false</filter>
                <sortable>false</sortable>
            </settings>
        </column>
        <column name="product_names">
            <settings>
                <filter>text</filter>
                <label translate="true">Product Name</label>
                <visible>true</visible>
                <filter>false</filter>
                <sortable>false</sortable>
            </settings>
        </column>
    </columns>
</listing>

app/code/SR/MagentoCommunity/Ui/Component/Listing/Column/ItemLocation.php

<?php
namespace SR\MagentoCommunity\Ui\Component\Listing\Column;

use Magento\Framework\View\Element\UiComponent\ContextInterface;
use Magento\Framework\View\Element\UiComponentFactory;
use Magento\Ui\Component\Listing\Columns\Column;
use Magento\Catalog\Model\ProductRepository;

class ItemLocation extends Column
{
    /**
     * @var ProductRepository
     */
    private $productRepository;

    /**
     * ItemLocation constructor.
     *
     * @param ContextInterface $context
     * @param UiComponentFactory $uiComponentFactory
     * @param ProductRepository $productRepository
     * @param array $components
     * @param array $data
     */
    public function __construct(
        ContextInterface $context,
        UiComponentFactory $uiComponentFactory,
        ProductRepository $productRepository,
        array $components = [],
        array $data = []
    ) {
        $this->productRepository = $productRepository;
        parent::__construct($context, $uiComponentFactory, $components, $data);
    }

    /**
     * Prepare Data Source
     *
     * @param array $dataSource
     * @return array
     */
    public function prepareDataSource(array $dataSource)
    {
        if (isset($dataSource['data']['items'])) {
            foreach ($dataSource['data']['items'] as & $item) {
                $productSkus = isset($item['product_skus']) ? $item['product_skus'] : null;
                $itemLocation = [];
                if ($productSkus) {
                    $productSkus = explode('|', $productSkus);
                    foreach ($productSkus as $sku) {
                        try {
                            $product = $this->productRepository->get($sku);
                            if (trim($product->getAttributeText("country_of_manufacture"))) {
                                $itemLocation[] = $product->getAttributeText("country_of_manufacture");
                            }
                        } catch (\Exception $e) {}
                    }
                }

                if (count($itemLocation) <= 0) {
                    $itemLocation[] = 'N/A';
                }
                $item[$this->getData('name')] = implode('|', $itemLocation);
            }
        }

        return $dataSource;
    }
}

Here I use country_of_manufacture attribute as an example. This is dropdown. If your custom attribute is not dropdown then you can replace by following code:

if ($customAttribute = $product->getData('custom_attribute')) {
    $itemLocation[] = $customAttribute;
}

instead of

if (trim($product->getAttributeText("country_of_manufacture"))) {
    $itemLocation[] = $product->getAttributeText("country_of_manufacture");
}