0

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?

1 Answer 1

1
+100

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

6
  • Nice! I will check next week. I am out of town now... One question, if it's filterable, shouldn't we add some new logic in _renderFiltersBefore method? Apr 3, 2023 at 8:39
  • @RaulSanchez since the filter for product information (order items) requires product name, sku, we need to use addFieldToFilter method because addFieldToFilter method does not provide that information.
    – Tu Van
    Apr 3, 2023 at 10:28
  • 1
    Nice. Thank you very much Apr 3, 2023 at 13:32
  • 1
    It worked like a charm! Thanks again Apr 12, 2023 at 9:57
  • 1
    @RaulSanchez cheers!
    – Tu Van
    Apr 12, 2023 at 11:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.