Add a custom column to the order grid’s filter option that is fulltext searchable.

In this blog, we will learn how to add a custom column to the order grid’s filter option that is fulltext searchable in Magento 2.

What is fulltext search

The ‘Fulltext’ search option in Magento 2 means that results are fetched based on the entire text of each attribute and rank each by relevancy, which is substantially more beneficial for the customers. In other words, a relevance-based search in which each search query is evaluated based on a score determined by the MATCH() AGAINST() query in MySQL.

Step 1: Create db_schema.xml in the app/code/Vendor/Module/etc/ directory. We will add custom columns to the three tables sales_order, sales_order_grid, and quote in the db_schema.xml file. We must specify indexType as fulltext for a certain column in order to enable fulltext search on that column.

Among the order grid columns that are by default fulltext searchable in Magento 2 are increment_id, billing_name, shipping_name, billing_address, shipping_address, customer_name, and customer_email.

<?xml version="1.0"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="sales_order_grid" resource="sales">
        <column xsi:type="text" name="custom_field" nullable="true" comment="Custom Attribute"/>
        <index referenceId="SALES_ORDER_GRID_CUSTOM_FIELD" indexType="fulltext">
            <column name="custom_field"/>
        </index>
    </table>
    <table name="sales_order" resource="sales">
        <column xsi:type="text" name="custom_field" nullable="true" comment="Custom Attribute"/>
    </table>
    <table name="quote" resource="default">
        <column xsi:type="text" name="custom_field" nullable="true" comment="Custom Attribute"/>
    </table>
</schema>

Step 2: We need to declare the custom column to the UI order listing grid. For that, we will create a sales_order_grid.xml file in the app/code/Vendor/Module/view/adminhtml/ui_component/ directory.

<?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">
        <column name="custom_field">
            <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">Custom Field</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

Step 3: We will construct a virtualType of the already-existing class Magento\Sales\Model\ResourceModel\Grid. After that, we’ll give that virtual type the column as an argument. This will enable the custom column to appear in the order grid filter.
To accomplish this, we will create a di.xml file in the directory app/code/Vendor/Module/etc.

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
<virtualType name="Magento\Sales\Model\ResourceModel\Order\Grid" type="Magento\Sales\Model\ResourceModel\Grid">
<arguments>
<argument name="columns" xsi:type="array">
<item name="custom_field" xsi:type="string">sales_order.custom_field</item>
</argument>
</arguments>
</virtualType>
</config>

Result:

That’s all you need to do. I hope this helps.

Reference: https://github.com/magento/magento2/blob/2.4-develop/app/code/Magento/Sales/etc/db_schema.xml

You can also check our other related blogs:

Magento 2 custom column filter in order grid collection