Magento 2: Filter Custom Column in Admin Grid

Question

I have a custom column in an admin grid, this column is populated with a custom renderer and is not actually saved to the database. Is it possible to still filter by this column?

in my Grid.php file:

$this->addColumn(
  'test_column', [
    'header' => __('Test Column'), 
    'index' => 'test_column', 
    'renderer' => 'Company\Module\Block\StockMovement\Renderer\Test',
    'filter_condition_callback' => array($this, '_myTestFilter')
  ]
);

...

protected function _myTestFilter($collection, $column)
  {
    if (!$value = $column->getFilter()->getValue()) {
      return $this;
    }
    $collection->getSelect()->where("test_column like ?", "%$value%");
    return $this;
  }

And then here is my "Test.php" Renderer file:

<?php

namespace Company\Module\Block\StockMovement\Renderer;

use Magento\Framework\DataObject;

class Test extends \Magento\Backend\Block\Widget\Grid\Column\Renderer\AbstractRenderer
{   
  public function render(DataObject $row)
  {
    $comments = $row->getsm_comments();
    if ($comments) {
      return 'abc';
    } else {
      return 'xyz';
    }
  }
}

So now in my custom column on my grid with either abc or xyz text values. These are not actually saved in the DB but just rendered text. How can I filter using this column's text?

Answer 1

UPDATED (2) ANSWER: 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 a option:

EDIT: Moved filtering before collection set to widget to have pager working.

    protected function _prepareCollection()
    {
        ...
        //add following before setCollection called
        if ($this->getTestColumnFilter() !== null) {
            $collection = $this->_addCustomFilterToCollection($collection);
        }
        ...
        $this->setCollection($collection);
    }

    protected function _prepareColumns()
    {
        ...
        //add a filter_condition_callback pointing to an empty function to prevent default filtering that will cause an error like *'test_column not found'*
        $this->addColumn(
            'test_column', [
                'header' => __('Test Column'),
                'index' => 'test_column',
                'renderer' => 'Company\Module\Block\StockMovement\Renderer\Test',
                'filter_condition_callback' => array($this, '_preventDefaultFilter')
            ]
        );
        ...
    }

    /**
     * @param $collection
     * @param $column
     * @return $this
     */
    public function _preventDefaultFilter($collection, $column)
    {
        return $this;
    }

    /**
     * Get test_column filter from request using the same method in grid widget
     *
     * @return mixed|null
     */
    private function getTestColumnFilter()
    {
        $filter = $this->getParam($this->getVarNameFilter(), null);

        if ($filter === null) {
            $filter = $this->_defaultFilter;
        }

        if (is_string($filter)) {
            $data = $this->_backendHelper->prepareFilterString($filter);
            $data = array_merge($data, (array)$this->getRequest()->getPost($this->getVarNameFilter()));
        } elseif ($filter && is_array($filter)) {
            $data = $filter;
        } elseif (0 !== sizeof($this->_defaultFilter)) {
            $data = $this->_defaultFilter;
        }

        if (array_key_exists('test_column', $data)) {
            return $data['test_column'];
        }
        return null;
    }

    /**
     * Add our custom filter based on renderer logic
     *
     * @param $collection
     * @return \Magento\Framework\Data\CollectionDataSourceInterface
     */
    private function _addCustomFilterToCollection($collection)
    {
        foreach ($collection->getItems() as $key => $row) {
            //renderer logic here
            $comments = $row->getId() % 2 == 1;
            if ($comments) {
                $renderedValue = 'abc';
            } else {
                $renderedValue = 'xyz';
            }

            $filterValue = $this->getTestColumnFilter();
            //remove item from collection if filter value is not equal to rendered value
            if ($filterValue !== $renderedValue){
                $collection->addFieldToFilter(
                    "{$this->getMainTableAlias($collection)}.{$row->getIdFieldName()}",
                    ['neq' => $row->getId()]
                );
            }
        }

        return $collection;
    }


    /**
     * Identify main table alias or its name if alias is not defined.
     *
     * @param $collection
     * @return string
     */
    private function getMainTableAlias($collection)
    {
        foreach ($collection->getSelect()->getPart(\Magento\Framework\DB\Select::FROM) as $tableAlias => $tableMetadata) {
            if ($tableMetadata['joinType'] == 'from') {
                return $tableAlias;
            }
        }
        return 'main_table';
    }

Answer 2

//Add Your column In protected function _prepareColumns()

$this->addColumn(
                'Col_Name',
                [
                    'header' => __('col_name'),
                    'index' => 'Col_Name',
                    'type'      => 'options',
                    'options'   => array('Option1 Value' => 'Option1 Label','Option2 value' => 'Option2 Label',
                        'Option3 Value' => 'Option3 Label',
                        'Option4 value' => 'Option4 Label'),
                    'renderer'  => 'Vendor\ModuleName\Block\Adminhtml\Grid\Renderer\Renderer_FileName',
                    'filter_condition_callback' => [$this, '_customFilter'],
                    'sortable'  => false
                ]
            );

//Outside _prepareColumns Function Add below Filter Method

protected function _customFilter($collection, $column)
{
    $value = $column->getFilter()->getValue();
    if (!$value) {
        return $this;
    }
    $collection->addFieldToFilter('Col_Name',['like' => '%' . $value . '%']);
    return $this;
}

Answer 3

To filter a custom column in the admin grid of Magento 2, you need to follow these steps:

1. Create a custom column in the grid by extending the grid class and adding the column to the _prepareColumns method.

protected function _prepareColumns()
{
    // ... other columns
    
    $this->addColumn(
        'custom_column',
        [
            'header' => __('Custom Column'),
            'index' => 'custom_column',
        ]
    );

    // ... other columns
}

2. Add the filter_condition_callback attribute to the column definition, which will define the custom filter condition for the column.

protected function _prepareColumns()
{
    // ... other columns
    
    $this->addColumn(
        'custom_column',
        [
            'header' => __('Custom Column'),
            'index' => 'custom_column',
            'filter_condition_callback' => [$this, '_customFilter'],
        ]
    );

    // ... other columns
}

3. Implement the _customFilter method in the grid class. This method will receive the filter value entered by the user and should return a Zend_Db_Select object that filters the collection based on the custom condition.

protected function _customFilter($collection, $column)
{
    if (!$value = $column->getFilter()->getValue()) {
        return $this;
    }

    $this->getCollection()->getSelect()->where(
        "custom_column = ?",
        $value
    );

    return $this;
}

4. Clear the Magento cache to make the changes take effect.