Magento 2 Add customer Billing Phone number column in Order and Invoice grid. I also want to filter and search with this phone number

Question

Magento 2 Add customer Billing Phone number column in Order and Invoice grid. I also want to filter and search with this phone number. Please provide code if anyone have done this.

Answer 1

First you need to add column through uicomponent

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">
    <column name="telephone">
        <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">Phone Number</item>
                <item name="sortOrder" xsi:type="number">6</item>
            </item>
        </argument>
    </column>
 </columns>
 </listing>

then Add the collection file in your module

Vendor\Module\Model\ResourceModel\Order\Grid\Collection.php

<?php

namespace Vendor\Module\Model\ResourceModel\Order\Grid;

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

/**
 * Order grid extended collection
 */
class Collection extends OriginalCollection
{

    protected function _initSelect()
    {
        $this->addFilterToMap('status', 'main_table.status');
        $this->addFilterToMap('customer_id', 'main_table.customer_id');

        parent::_initSelect();
    }

    protected function _renderFiltersBefore()
    {
    $joinTable = $this->getTable('sales_order_address');
    $this->getSelect()->joinLeft($joinTable, "main_table.entity_id = 
    {$joinTable}.parent_id AND {$joinTable}.address_type = 'billing'", 
      ['telephone']);
       parent::_renderFiltersBefore();
   }
    
}

At last Add di.xml in your module

<?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>

It will add customer billing phone number in order grid with filter. I wrote this code it is my tested code. I hope this will help

Answer 2

The above Answer is perfect, But is not work when both billing and shipping address required, Some vendors require both billing and shipping addresses details in order grid then follow this join collection code.

protected function _initSelect()
{
    $this->addFilterToMap('billing_telephone', 'soa.telephone');
    $this->addFilterToMap('shipping_telephone', 'soa1.telephone');
    parent::_initSelect();
}
protected function _renderFiltersBefore()
{
    $this->getSelect()->joinLeft(
        ['soa' => 'sales_order_address'],
        "main_table.entity_id =soa.parent_id AND soa.address_type = 'billing'",
        ['billing_telephone'=>'soa.telephone']
    );

    $this->getSelect()->joinLeft(
        ['soa1' => 'sales_order_address'],
        "main_table.entity_id =soa1.parent_id AND soa1.address_type = 'shipping'",
        ['shipping_telephone'=>'soa1.telephone']
    );

    parent::_renderFiltersBefore();
}

and In sales_order_grid.xml

<column name="billing_telephone">
        <settings>
            <filter>text</filter>
            <label translate="true">Billing Telephone</label>
        </settings>
    </column>

    <column name="shipping_telephone">
        <settings>
            <filter>text</filter>
            <label translate="true">Shipping Telephone</label>
        </settings>
    </column>

It renders correct values and is filterable, it tested on magento 2.4.2 version. Hope it usefull.