Magento2 join product details in sales order grid as filterable column

Question

We have added some additional sortable / filterable columns to sales order grid using some basic joins. This would be the relevant code regarding data

app/code/Vendor/Module/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">
        <arguments>
            <argument name="collections" xsi:type="array">
                <item name="sales_order_grid_data_source" xsi:type="string">Vendor\Module\Model\ResourceModel\Order\Grid\Collection</item>
            </argument>
        </arguments>
    </type>
    <type name="Sinapsis\OrderGrid\Model\ResourceModel\Order\Grid\Collection">
        <arguments>
            <argument name="mainTable" xsi:type="string">sales_order_grid</argument>
            <argument name="resourceModel" xsi:type="string">Magento\Sales\Model\ResourceModel\Order</argument>
        </arguments>
    </type>
</config>

And then app/code/Vendor/Module/Model/ResourceModel/Order/Grid/Collection.php

<?php
namespace Vendor\Module\Model\ResourceModel\Order\Grid;

use Magento\Framework\Data\Collection\Db\FetchStrategyInterface as FetchStrategy;
use Magento\Framework\Data\Collection\EntityFactoryInterface as EntityFactory;
use Magento\Framework\Event\ManagerInterface as EventManager;
use Magento\Sales\Model\ResourceModel\Order;
use Magento\Sales\Model\ResourceModel\Order\Grid\Collection as OrderGridCollection;
use Psr\Log\LoggerInterface as Logger;

class Collection extends OrderGridCollection
{

    public function __construct(
        EntityFactory $entityFactory,
        Logger $logger,
        FetchStrategy $fetchStrategy,
        EventManager $eventManager,
        $mainTable = 'sales_order_grid',
        $resourceModel = Order::class
    ) {
        parent::__construct($entityFactory, $logger, $fetchStrategy, $eventManager, $mainTable, $resourceModel);
    }

    protected function _renderFiltersBefore()
    {
        $joinTable = $this->getTable('sales_order');
        $this->getSelect()->joinLeft(
            ['sales_order_table' => $joinTable],
            'main_table.entity_id = sales_order_table.entity_id',
            ['coupon_code']
        );

        $joinTable = $this->getTable('sales_order_address');
        $this->getSelect()->joinLeft(
            ['sales_order_address_table' => $joinTable],
            'sales_order_table.billing_address_id = sales_order_address_table.entity_id',
            ['country_id', 'region', 'city', 'postcode', 'telephone']
        );
        parent::_renderFiltersBefore();
    }
}

Now we want to add a column including some product details, such as product name, sku, etc... In this case, being complex data, it's not required to be sortable but it should be filterable

How could we do the join to sales_order_item database table, so we can concatenate all possible results in one single field per order with the products info?

Answer 1

Join sales_order_item database table to add product info in one single field per order:
app/code/Vendor/Module/Model/ResourceModel/Order/Grid/Collection.php

<?php
namespace Vendor\Module\Model\ResourceModel\Order\Grid;

use Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult;
use Magento\Sales\Model\ResourceModel\Order\Grid\Collection as OrderGridCollection;
use Zend_Db_Expr;

class Collection extends OrderGridCollection
{
    public function addFieldToFilter($field, $condition = null): Collection
    {
        if ($field === 'order_items' && !$this->getFlag('product_filter_added')) {
            // Add the sales/order_item model to this collection
            $this->getSelect()->join(
                [$this->getTable('sales_order_item')],
                "main_table.entity_id = {$this->getTable('sales_order_item')}.order_id",
                []
            );

            // Group by the order id, which is initially what this grid is id'd by
            $this->getSelect()->group('main_table.entity_id');

            // On the products field, let's add the sku and name as filterable fields
            $this->addFieldToFilter([
                "{$this->getTable('sales_order_item')}.sku",
                "{$this->getTable('sales_order_item')}.name",
            ], [
                $condition,
                $condition,
            ]);

            $this->setFlag('product_filter_added', 1);

            return $this;
        } else {
            return parent::addFieldToFilter($field, $condition);
        }
    }

    /**
     * Perform operations after collection load.
     *
     * @return SearchResult
     */
    protected function _afterLoad(): SearchResult
    {
        $items = $this->getColumnValues('entity_id');

        if (count($items)) {
            $connection = $this->getConnection();

            // Build out item sql to add products to the order data
            $select = $connection->select()
                ->from([
                    'sales_order_item' => $this->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 "|")'),
                    'product_qtys'  => new Zend_Db_Expr(
                        'GROUP_CONCAT(`sales_order_item`.qty_ordered SEPARATOR "|")'
                    ),
                ])
                ->where('order_id IN (?)', $items)
                ->where('parent_item_id IS NULL') // Eliminate configurable products, otherwise two products show
                ->group('order_id');

            $items = $connection->fetchAll($select);

            // Loop through this sql an add items to related orders
            foreach ($items as $item) {
                $row = $this->getItemById($item['order_id']);
                $productSkus = explode('|', $item['product_skus']);
                $productQtys = explode('|', $item['product_qtys']);
                $productNames = explode('|', $item['product_names']);
                $html = '';

                foreach ($productSkus as $index => $sku) {
                    $html .= sprintf(
                        '<div>%d x [%s] %s </div><br/>',
                        $productQtys[$index], $sku, $productNames[$index]
                    );
                }

                $row->setData('order_items', $html);
            }
        }

        return parent::_afterLoad();
    }

    protected function _renderFiltersBefore()
    {
        $joinTable = $this->getTable('sales_order');
        $this->getSelect()->joinLeft(
            ['sales_order_table' => $joinTable],
            'main_table.entity_id = sales_order_table.entity_id',
            ['coupon_code']
        );

        $joinTable = $this->getTable('sales_order_address');
        $this->getSelect()->joinLeft(
            ['sales_order_address_table' => $joinTable],
            'sales_order_table.billing_address_id = sales_order_address_table.entity_id',
            ['country_id', 'region', 'city', 'postcode', 'telephone']
        );
        parent::_renderFiltersBefore();
    }
}

Add the order items column to UI Components: app/code/Vendor/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">
    <!--  other columns go here -->
    <column name="order_items">
        <settings>
            <filter>text</filter>
            <bodyTmpl>ui/grid/cells/html</bodyTmpl>
            <sortable>false</sortable>
            <label translate="true">Order Items</label>
        </settings>
    </column>
</columns>
</listing>

You're done!

Reference: https://github.com/markshust/magento2-module-ordergrid