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');
 }
 
 }
 }