Custom module column in sales order grid with select filter in magento 2

Question

I have a custom module with the columns id, order_id & reason. Now I want to add the reason value in the order grid with the select filter.

Reasons are listed in the other table of the custom module.

If I am using the text field filter then it is working but in the case of the dropdown filter, it is not working properly.

I have used below code:

vendor/module/view/adminhtml/ui_component/sales_order_grid.xml

<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="cancel_reason">
            <argument name="data" xsi:type="array">
                <item name="options" xsi:type="object">Vendor\Moduel\Model\Config\Source\Reason</item>
                <item name="config" xsi:type="array">
                    <item name="visible" xsi:type="boolean">true</item>
                    <item name="filter" xsi:type="string">select</item>
                    <item name="component" xsi:type="string">Magento_Ui/js/grid/columns/select</item>
                    <item name="dataType" xsi:type="string">select</item>
                    <item name="label" xsi:type="string" translate="true">Cancellation Reason</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

Vendor\Moduel\Model\Config\Source\Reason

<?php
namespace Vendor\Module\Model\Config\Source;

use Magento\Framework\Option\ArrayInterface;

class Reason implements ArrayInterface
{

    public function toOptionArray()
    {
        $result = [];
        foreach ($this->getOptions() as $value => $label) {
            $result[] = [
                 'value' => $value,
                 'label' => $label,
             ];
        }
        return $result;
    }

    public function getOptions() {
        $objectManager = \Magento\Framework\App\ObjectManager::getInstance();
        $resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
        $connection = $resource->getConnection();
        $tableName = $resource->getTableName('cancelreason_item');            
        $sql = "Select `reason` FROM " . $tableName;
        $rows = $connection->fetchAll($sql); 

        $result = [];
        foreach($rows AS $row){
            $result[$row['reason']] = $row['reason'];
        }
        return $result;
    }
}

Answer 1

It can be helpful for you for adding custom column with select filter.

I have used below code:

vendor/module/view/adminhtml/ui_component/sales_order_grid.xml

<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="newcolumn" class="Vendor\Module\Ui\Component\Listing\Column\OrderNewColumn">
            <settings>
                <filter>select</filter>
                <sortable>false</sortable>
                <options class="Vendor\Module\Model\Source\OptionTypeStatus"/>
                <dataType>select</dataType>
                <label translate="true">New Column</label>
            </settings>
        </column>
    </columns>
</listing>

Vendor\Moduel\Ui\Component\Listing\Column

<?php
namespace Vendor\Module\Ui\Component\Listing\Column;

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

class OrderNewColumn extends Column {

    protected $_orderRepository;
    protected $_searchCriteria;
    protected $_customfactory;

    public function __construct(
        ContextInterface $context,
        UiComponentFactory $uiComponentFactory,
        OrderRepositoryInterface $orderRepository,
        SearchCriteriaBuilder $criteria,
        array $components = [], array $data = []
    ){
        $this->_orderRepository = $orderRepository;
        $this->_searchCriteria = $criteria;
        parent::__construct($context, $uiComponentFactory, $components, $data);
    }

    public function prepareDataSource(array $dataSource) {
        if (isset($dataSource['data']['items'])) {
            foreach ($dataSource['data']['items'] as & $item) {
                $order = $this->_orderRepository->get($item["entity_id"]);
                if ($order->getNewcolumn() > 0) {
                    $item['newcolumn'] = 'Yes';
                } else {
                    $item['newcolumn'] = 'No';
                }
            }
        }
        return $dataSource;
    }
}

Vendor\Moduel\Model\Source

<?php
namespace Vendor\Module\Model\Source;

use Magento\Framework\Data\OptionSourceInterface;

/**
  * Class OptionTypeStatus
  */
class OptionTypeStatus implements OptionSourceInterface
{
    /**
     * Array
     *
     * @var array
     */
    protected $options;
    /**
     * Array
     *
     * @return array
     */
    public function toOptionArray()
    {
        $typesOfStatus = [
            0 => 'No',
            1 => 'Yes'
        ];
        $options = [];
        foreach ($typesOfStatus as $key => $typeOfStatus) {
            $options[] = [
                'label' => $typeOfStatus,
                'value' => $key
            ];
        }
    
        return $options;
    }
}