Filtering not working in UI component

Question

I have created a UI listing in Magento 2. The first column was getting the id of the product. Since I don't want to display the id, I wanted to display its SKU. So I applied a class for the column.

     <column name="product_id" class="Avesh\Rule\Ui\Component\Listing\Column\Rules\Sku">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="filter" xsi:type="string">text</item>
                <item name="sorting" xsi:type="string">asc</item>
                <item name="label" xsi:type="string" translate="true">SKU</item>
            </item>
        </argument>
    </column>

Now I added a filter to filter the column by Sku. It doesn't work. But if I put the id of the product it works fine. What I am missing?

Searching with SKU

Searching with Id

Undefined index: sku in /var/www/vhosts/website/app/code/Avesh\Rule/Ui/Component/Listing/Column/Rules/Sku.php on line 37 I don't have a colum name in my table as sku. just have a product Id when i change it to

    <column name="sku" class="Avesh\Rule\Ui\Component\Listing\Column\Rules\Sku">

Class code

namespace Avesh\Rule\Ui\Component\Listing\Column\Rules;

use Magento\Ui\Component\Listing\Columns\Column;

use Magento\Framework\View\Element\UiComponent\ContextInterface;
use Magento\Framework\View\Element\UiComponentFactory;
use Magento\Catalog\Model\ProductFactory;

class Sku  extends Column
{
protected $productFactory;

public function __construct(
    ContextInterface $context,
    UiComponentFactory $uiComponentFactory,
    array $components = [],
    array $data = [],
    ProductFactory $productFactory
) {
    parent::__construct($context, $uiComponentFactory, $components, $data);
    $this->productFactory = $productFactory;
}

public function prepareDataSource(array $dataSource)
{
    if (isset($dataSource['data']['items'])) {
        $fieldName = $this->getData('name');
        foreach ($dataSource['data']['items'] as & $item) {
            if ($item[$fieldName] != 0) {
                $adminName = $this->getProductSku($item[$fieldName]);
                $item[$fieldName] = $adminName;
            }
        }
    }
    return $dataSource;
}

private function getProductSku($productId) {
    $product = $this->productFactory->create()->load($productId);
    return $product->getSku();
}
}

Answer 1

Add this in your collection file.

protected function _renderFiltersBefore() {

    $objectManager = \Magento\Framework\App\ObjectManager::getInstance();
    $request = $objectManager->get('Magento\Framework\App\Request\Http');
    $filters = $request->getParam("filters");
    if (isset($filters['product_id'])) {
        $objectManager = \Magento\Framework\App\ObjectManager::getInstance();
        $product = $objectManager->get('Magento\Catalog\Model\ProductFactory')->create()
            ->getCollection()
            ->addAttributeToFilter('sku', ['eq' => $filters['product_id']])->getFirstItem();
        $this->getSelect()->orWhere("product_id = '" . $product->getId() . "'");
    }

    parent::_renderFiltersBefore();
}