Get additional_information from sales_order_payment to show in backend sales order grid

Question

I have a custom sales order grid module I want to extend, to show the information from the field additional_information, in the table sales_order_payment.

I have this right now, but it ain't showing the payment method like Invoice or Credit Card, only the payment provider.

Path:

\app\code\Vendor\SalesOrderGrid\view\adminhtml\ui_component\sales_order_grid.xml

Code:

    <column name="payment_method" component="Magento_Ui/js/grid/columns/select">
        <settings>
            <filter>select</filter>
            <options class="Magento\Payment\Ui\Component\Listing\Column\Method\Options"/>
            <dataType>select</dataType>
            <label translate="true">Payment Method</label>
            <visible>false</visible>
        </settings>
    </column>

The data in additional_information is stored like this:

{"svea_order_id":0000000,"country_id":"SE","method_title":"Svea 
Checkout","svea_payment_method":"INVOICE","svea_queue_id":"0000000"}

or

{"svea_order_id":0000000,"country_id":"SE","method_title":"Svea 
Checkout","svea_payment_method":"SVEACARDPAY_PF","svea_delivery_id":0000000}

And I want to fetch the data INVOICE and SVEACARDPAY_PF and others to show in the sales order grid.

Thank you,

Answer 1

You can display the additional_information by using the joinLeft concept.

The actual "additional_information" data resides in sales_order_payment table. We can join the sales_order_grid table and sales_order_payment table using the order's entity_id.

Step 1:

Declare our sales_order_grid datasource in di.xml file

Vendor\Module\etc\adminhtml\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>
</config>

Step 2:

Join the tables using the sales_order_grid table's entity_id and sales_order_payment table's parent_id column.

Vendor\Module\Model\ResourceModel\Order\Grid\Collection:

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

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

class Collection extends OriginalCollection
{
    protected function _renderFiltersBefore()
    {
        $joinTable = $this->getTable('sales_order_payment');
        $this->getSelect()->joinLeft($joinTable, 'main_table.entity_id = sales_order_payment.parent_id', ['additional_information']);
        parent::_renderFiltersBefore();
    }
}

Step 3:

Define additional_information column in sales_order_grid.xml Uicomponent

Vendor\Module\view\adminhtml\ui_component\sales_order_grid.xml

<?xml version="1.0"?>
<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="additional_information">
            <settings>
                <filter>text</filter>
                <label translate="true">Additional Information</label>
            </settings>
        </column>
    </columns>
</listing>

With this step, your grid will display additional_information in JSON format.

Step 4:

Now you can customise your JSON how will display in grid using column's class="Vendor\Module\Ui\Component\Listing\Column\AdditionalInfo" attribute.

Like below code:

<?xml version="1.0"?>
    <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="additional_information" class="Vendor\Module\Ui\Component\Listing\Column\AdditionalInfo">
                <settings>
                    <filter>text</filter>
                    <label translate="true">Additional Information</label>
                </settings>
            </column>
        </columns>
    </listing>

Step 5:

Inside your listing column class, you can customize a display like which data you want to display in sales_order_grid.

For example:

Vendor\Module\Ui\Component\Listing\Column\AdditionalInfo

<?php

namespace Vendor\Module\Ui\Component\Listing\Column;

use Magento\Ui\Component\Listing\Columns\Column;

/**
 * Class AdditionalInfo
 */
class AdditionalInfo extends Column
{

    /**
     * Prepare Data Source
     *
     * @param array $dataSource
     * @return array
     */
    public function prepareDataSource(array $dataSource)
    {
        if (isset($dataSource['data']['items'])) {
            foreach ($dataSource['data']['items'] as & $item) {
               //Here each $item is a row of sales_order_grid. Grid all data can available here. You can customize your additional_information JSON here like which data you want to display in grid. 
            }
        }
        return $dataSource;
    }
}

For more information refer to this link: https://magenable.com.au/magento-ecommerce/modifying-default-magento-2-sales-order-grid-adding-coupon-code-column/

Hope it will help you!