How to filter admin grid column using a different table colum from database?

Question

in my form_list.xml i have something like this

    <column name="testcolumn" class="Vendor\Module\Model\Block\Source\Testcolumn">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="label" xsi:type="string" translate="true">Testcolumn</item>
            </item>
        </argument>
        <settings>
            <filter>text</filter>
        </settings>
    </column>

and in Vendor\Module\Model\Block\Source\Testcolumn i have something like

public function prepareDataSource(array $dataSource)
{
    if (isset($dataSource['data']['items'])) {
        foreach ($dataSource['data']['items'] as & $item) {
            try {
                $email = $item['customer'];
                $testcolumn_name = $this->customerRepository->get($email)
                    ->getCustomAttribute('$testcolumn_name')->getValue();
            }catch (\Exception $e){
                $testcolumn_name = '';
            }

            $item[$this->getData('name')] = $testcolumn_name;
        }
    }
    return $dataSource;
}

is it possible to make filter work for this field ?

Thank you

Answer 1

If you want to use the same logic in your renderer to filter the column, removing items from the collection based on your custom renderer logic can be an option:

https://pastebin.com/t8EcpQX3

Answer 2

i resolved this issue with this

in di.xml

<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
    <arguments>
        <argument name="collections" xsi:type="array">
            <item name="test_listing_data_source" xsi:type="string">Vendor\Module\Model\ResourceModel\TestClass</item>
        </argument>
    </arguments>
</type>

in TestClass.php

<?php
    namespace Vendor\Module\Model\ResourceModel;
    
    use Magento\Framework\Data\Collection\Db\FetchStrategyInterface as FetchStrategy;
    use Magento\Framework\Data\Collection\EntityFactoryInterface as EntityFactory;
    use Magento\Framework\Event\ManagerInterface as EventManager;
    use Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult;
    use Psr\Log\LoggerInterface as Logger;
    
    class TestClass extends SearchResult
    {
        
        /**
         * var \Magento\Eav\Model\ResourceModel\Entity\Attribute
         */
        protected $_eavAttribute;
    
        /**
         * Collection constructor.
         * @param EntityFactory $entityFactory
         * @param Logger $logger
         * @param FetchStrategy $fetchStrategy
         * @param EventManager $eventManager
         * @param string $mainTable
         * @param string $resourceModel
         * @throws \Magento\Framework\Exception\LocalizedException
         */
        public function __construct(
            EntityFactory $entityFactory,
            Logger $logger,
            FetchStrategy $fetchStrategy,
            EventManager $eventManager,
            $mainTable = 'main_table',
            $resourceModel = ModuleName::class,
            \Magento\Eav\Model\ResourceModel\Entity\Attribute $eavAttribute
        ) {
            
            $this->_eavAttribute = $eavAttribute;
    
            parent::__construct(
                $entityFactory,
                $logger,
                $fetchStrategy,
                $eventManager,
                $mainTable,
                $resourceModel
            );
            
        }
    
        
        protected function _initSelect()
        {
            parent::_initSelect();
            
            if($attributeId = $this->_eavAttribute->getIdByCode('customer', 'test_attribute')) {
                
                $this->getSelect()
                  ->joinInner(
                      ['buyer' => $this->getTable('customer_entity_varchar')], 'main_table.customer_id = anothertable.entity_id', []
                  )->where('anothertable.attribute_id = (?)', $attributeId);
                
                $this->addFilterToMap('test_attribute','anothertable.value');
$this->addFilterToMap('entity_id', 'main_table.entity_id');
            }
    
        }
    }