0

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?

  • what does getsm_comments do? – Marius Aug 23 at 5:51
  • That's just another column in the grid, I just had that while testing, but it could be any condition there. Really just trying to figure out how to filter the grid based on the rendered text yes or no which aren't actually saved to the table. – AJ47 Aug 23 at 12:40
  • sorting and filtering has to be done via the sql query attached to the collection you are displaying. I asked what that method does, maybe there is an easy way to join some other table and get the value for the column directly in the query results. – Marius Aug 23 at 13:06
  • Ah okay, maybe this isn't possible then since my Test column values are just displayed / echoed text essentially and not tied to an actual table in the DB. getsm_comments is getting comment value from sm "stock movements" table in db (created via a 3rd party module). – AJ47 Aug 23 at 13:29
  • maybe you can join your collection with the movements table and obtain the value 0/1 via sql. – Marius Aug 23 at 13:58
2
+50

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

  • Thanks, it's actually a text field, I'll update my question to clarify that. – AJ47 Aug 23 at 12:42
  • 1
    Just ignore the converting the type to options suggestion, the above code tested and it works. I'll update the answer for you. – muhammedv Aug 23 at 18:05
  • thanks! this seems to work for the most part, but it doesn't seem to update the $collection count, for example, after filtering by this column, it will have a bunch of blank pages and the rows I'm looking for may be on page 2 when page 1 is completely blank. Any ideas? – AJ47 Aug 23 at 19:02
  • just to clarify, here is an example of what I mean: screencast.com/t/im38KMEn7a – AJ47 Aug 23 at 19:22
  • The reason causing that is the way getSize() function implemented in Magento\Framework\Data\Collection. Once $_totalRecords value set to collection, it doesn't get updated after filtering the collection. Grid widget gets the last page value from collection itself in Magento_Backend ::adminhtml/templates/widget/grid.phtml and getLastPageNumber() function uses getSize() function. I will update my answer with a different approach to the problem. – muhammedv Aug 23 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.