1

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,

4
+50

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!

5
  • Thank you for your input on this. Almost there. If I print the array $item in step 5 it doesn't seem to have any information from the field additional_information in the table sales_order_payment. I must be missing something in step 5, but what? Thanks,
    – Alldo
    Sep 7 at 7:56
  • I tried to change from main_table.entity_id to sales_order.entity_id in step 2 but with no luck.
    – Alldo
    Sep 7 at 8:15
  • 1
    Are you able to see the raw JSON data in the grid without Step 5? If yes, please update your "Vendor\Module\Ui\Component\Listing\Column\AdditionalInfo" code. I will try to fix it. Sep 7 at 8:35
  • 1
    @Alldo regarding your second command, you should not change the main_table.entity_id into sales_order.entity_id. Because here main_table referring to the sales_order_grid table. We are joing the sales_order_grid table only not sales_order table. Sep 7 at 8:42
  • 1
    Its working right now, thank you! All information in the table field is visible in the grid now. Just need to figure out how to unserialize it and show the correct information. If: {"svea_order_id":0000000,"country_id":"SE","method_title":"Svea Checkout","svea_payment_method":"INVOICE","svea_queue_id":"0000000"} Then just show the text Invoice in the grid.
    – Alldo
    Sep 7 at 8:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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