Custom Column Sorting in Admin Grid - Magento 2

Question

I have a custom grid with a column definition as below:

 <column name="practitioner_id" class="Amrita\Practitioner\Ui\Component\Listing\Column\Practitioner">
            <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">Practitioner</item>
                    <item name="sortable" xsi:type="string">true</item>
                </item>
            </argument>
        </column>

and from Amrita\Practitioner\Ui\Component\Listing\Column\Practitioner I'm preparing the data source as follows:

 /**
 * @param array $dataSource
 * @return array
 */
public function prepareDataSource(array $dataSource)
{
    if (isset($dataSource['data']['items'])) {
        foreach ($dataSource['data']['items'] as & $item) {
            $name = $this->getData('name');
            try {
                $practitioner = $this->practitionerRepository->getById($item[$name]);
                $item[$name] = $practitioner->getName();
            } catch (NoSuchEntityException $e) {
                $item[$name] = __('-');
            }
        }
    }
    return $dataSource;
}

So I'm using an external model to retrieve the practitioner name and display it in a grid (based on another model and associated practitioner_id).

The issue is that when I sort the column by clicking the header, it orders the column by the id rather than by the name, it needs to be sorted alphabetically.

How do apply this custom sorting to this column given i'm using another model to populate the data.

I can see there's the method \Magento\Ui\Component\Listing\Columns\Column::applySorting()

But I'm not sure how to manipulate this, can anyone help?

Answer 1

For this you have to make custom data provider. You can make custom data provider in following steps :

1. For the corresponding grid go to Vendor\Module\etc\di.xml and find below lines :<virtualType name="Vendor\Module\Model\ResourceModel\SomeModel\Collection" type="Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult">

2. Edit the line and call your custom data provider, some thing like this:

   <virtualType name="Vendor\Module\Model\ResourceModel\SomeModel\Collection" type="Vendor\Module\Ui\Component\Listing\CustomDataProvider">

3. Now create file CustomDataProvider.php in directory Vendor\Module\Ui\Component\Listing with the following content:

   class CustomDataProvider extends \Magento\Framework\View\Element
   
   \UiComponent\DataProvider\SearchResult
   {
       protected function _initSelect()
       {
           parent::_initSelect();
           $this->getSelect()->joinLeft(
               [
                   'secondTable' => $this->getTable('second_table_name_from_where_data_is_coming')
               ], 
               'main_table.practitioner_id = secondTable.practitioner_id', 
               [
                   'practitioner_id'
               ]
           );
   
           return $this;
       }
   }
   

4. Flush cache and test again.