0

I have the following from another post, and now I'm looking for a way to make these 4 extra columns filterable.

Thank you,

\app\code\Vendor\SalesOrderGrid\Ui\Component\Listing\Column\Address.php

    <?php

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

    use Magento\Sales\Api\OrderRepositoryInterface;
    use Magento\Ui\Component\Listing\Columns\Column;
    use Magento\Framework\View\Element\UiComponent\ContextInterface;
    use Magento\Framework\View\Element\UiComponentFactory;

    /**
     * Class Address
     */
    class Address extends Column
    {
    /**
    * @var OrderRepositoryInterface
    */
private $orderRepository;


/**
 * Address constructor.
 * @param ContextInterface $context
 * @param UiComponentFactory $uiComponentFactory
 * @param OrderRepositoryInterface $orderRepository
 * @param array $components
 * @param array $data
 */
public function __construct(
    ContextInterface $context,
    UiComponentFactory $uiComponentFactory,
    OrderRepositoryInterface $orderRepository,
    array $components = [],
    array $data = []
) {
    $this->orderRepository = $orderRepository;
    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) {
            $column = $this->getData('name');
            $orderId = $item['entity_id'];
            $order = $this->orderRepository->get($orderId);

            if ($column == 'street') {
                $streetArr = $order->getShippingAddress()->getStreet();
                $data = implode(',', $streetArr);
            } elseif ($column == 'zip') {
                $data = $order->getShippingAddress()->getPostcode();
            } elseif ($column == 'city') {
                $data = $order->getShippingAddress()->getCity();
            } elseif ($column == 'phone') {
                $data = $order->getShippingAddress()->getTelephone();
            }

            $item[$this->getData('name')] = $data;
        }
    }

    return $dataSource;
    }
    }

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

    <column name="street" class="Vendor\SalesOrderGrid\Ui\Component\Listing\Column\Address">
        <settings>
            <filter>text</filter>
            <label translate="true">Street</label>
            <bodyTmpl>ui/grid/cells/html</bodyTmpl>
            <visible>true</visible>
        </settings>
    </column>

    <column name="zip" class="Vendor\SalesOrderGrid\Ui\Component\Listing\Column\Address">
        <settings>
            <filter>text</filter>
            <label translate="true">Zip</label>
            <bodyTmpl>ui/grid/cells/html</bodyTmpl>
            <visible>true</visible>
        </settings>
    </column>

    <column name="city" class="Vendor\SalesOrderGrid\Ui\Component\Listing\Column\Address">
        <settings>
            <filter>text</filter>
            <label translate="true">City</label>
            <bodyTmpl>ui/grid/cells/html</bodyTmpl>
            <visible>true</visible>
        </settings>
    </column>

    <column name="phone" class="Vendor\SalesOrderGrid\Ui\Component\Listing\Column\Address">
        <settings>
            <filter>text</filter>
            <label translate="true">Phone</label>
            <bodyTmpl>ui/grid/cells/html</bodyTmpl>
            <visible>true</visible>
        </settings>
    </column>
1

The filter will not be working with the data source class. For filter, we need to join sales_order_address table to sales_order table.

Follow these steps to make it work with the filter

1. Remove datasource class Vendor\SalesOrderGrid\Ui\Component\Listing\Column\Address from columns

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">
        <settings>
            <childDefaults>
                <param name="fieldAction" xsi:type="array">
                    <item name="provider" xsi:type="string">false</item>
                </param>
            </childDefaults>
        </settings>
        <column name="street">
            <settings>
                <filter>text</filter>
                <label translate="true">Street</label>
                <bodyTmpl>ui/grid/cells/html</bodyTmpl>
                <visible>true</visible>
            </settings>
        </column>
        <column name="postcode">
            <settings>
                <filter>text</filter>
                <label translate="true">Zip</label>
                <bodyTmpl>ui/grid/cells/html</bodyTmpl>
                <visible>true</visible>
            </settings>
        </column>
        <column name="city">
            <settings>
                <filter>text</filter>
                <label translate="true">City</label>
                <bodyTmpl>ui/grid/cells/html</bodyTmpl>
                <visible>true</visible>
            </settings>
        </column>
        <column name="telephone">
            <settings>
                <filter>text</filter>
                <label translate="true">Phone</label>
                <bodyTmpl>ui/grid/cells/html</bodyTmpl>
                <visible>true</visible>
            </settings>
        </column>
    </columns>
</listing>

2. Create plugin for order collection data provider

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">
        <plugin name="mageprince_custom_orders_grid" type="Vendor\Module\Plugin\OrdersGrid" sortOrder="10"/>
    </type>
</config>

app/code/Vendor/Module/Plugin/OrdersGrid.php

<?php

namespace Vendor\Module\Plugin;

class OrdersGrid
{
    public function afterGetReport($subject, $collection, $requestName)
    {
        if ($requestName !== 'sales_order_grid_data_source') {
            return $collection;
        }

        if ($collection->getMainTable() === $collection->getResource()->getTable('sales_order_grid')) {
            $orderAddressTable  = $collection->getResource()->getTable('sales_order_address');

            $collection->getSelect()->joinLeft(
                ['oat' => $orderAddressTable],
                'oat.parent_id = main_table.entity_id AND oat.address_type = \'shipping\'',
                ['telephone', 'city', 'postcode', 'street']
            );
        }

        return $collection;
    }
}

That's it you can now filter the new columns City, Street, Zip, Phone in order grid

1
  • 1
    Working perfect, thank you very much! – Alldo May 2 at 12:07
-1

Try below code :-

XML file code change below code your column name

<columns name="sales_order_columns">
        <column name="short_name" class="Vendor\ModuleName\Ui\Component\Listing\Column\Mycolumn">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">My Column</item>
                </item>
            </argument>
        </column>
    </columns>

Filter the text in your custom column below codes :-

/etc/adminhtml/di.xml

<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\Reporting">
        <plugin name="sales_grid_collection" type="\Vendor\ModuleName\Model\Plugin\Sales\Order\Grid"/>
    </type>
</config>

And then last create plugin model file Grid.php under

/Model/Plugin/Sales/Order/Grid.php

<?php
namespace Vendor\ModuleName\Model\Plugin\Sales\Order;
 
 
class Grid
{
 
    public static $table = 'sales_order_grid';
    public static $leftJoinTable = 'custom_order';
 
    public function afterSearch($intercepter, $collection)
    {
        if ($collection->getMainTable() === $collection->getConnection()->getTableName(self::$table)) {
 
            $leftJoinTableName = $collection->getConnection()->getTableName(self::$leftJoinTable);
 
            $collection
                ->getSelect()
                ->joinLeft(
                    ['co'=>$leftJoinTableName],
                    "co.order_id = main_table.entity_id",
                    [
                        'short_name' => 'co.short_name'
                    ]
                );
 
            $where = $collection->getSelect()->getPart(\Magento\Framework\DB\Select::WHERE);
 
            $collection->getSelect()->setPart(\Magento\Framework\DB\Select::WHERE, $where);
 
            //echo $collection->getSelect()->__toString();die;
 
 
        }
        return $collection;
 
 
    }
 
 
}

And above code you can add multiple filter columns like :-

$collection
 ->getSelect()
 ->joinLeft(
     ['co'=>$leftJoinTableName],
       "co.order_id = main_table.entity_id",
     [
       'short_name1' => 'co.short_name1',
       'short_name2' => 'co.short_name2'
     ]
  );

THANKS.

3
  • What does short_name means? Is it just an alias? – Alldo May 2 at 4:47
  • It is column name. – Mohit Patel May 2 at 6:25
  • I don't understand exactly, I don't think I have a table named custom_order for instance - should I create it manually? – Alldo May 2 at 6:32

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.