Case insensitive filtering with sonata admin in postgres

CaseInsensitiveStringFilter.php

<?php

namespace Liip\AcmeBundle\Filter;

use Sonata\AdminBundle\Form\Type\Filter\ChoiceType;
use Sonata\AdminBundle\Datagrid\ProxyQueryInterface;
use Sonata\DoctrineORMAdminBundle\Filter\StringFilter;

class CaseInsensitiveStringFilter extends StringFilter
{
    /**
     * {@inheritdoc}
     */
    public function filter(ProxyQueryInterface $queryBuilder, $alias, $field, $data)
    {
        if (!$data || !is_array($data) || !array_key_exists('value', $data)) {
            return;
        }

        $data['value'] = trim($data['value']);

        if (strlen($data['value']) == 0) {
            return;
        }

        $data['type'] = !isset($data['type']) ?  ChoiceType::TYPE_CONTAINS : $data['type'];

        $operator = $this->getOperator((int) $data['type']);

        if (!$operator) {
            $operator = 'LIKE';
        }

        // c.name > '1' => c.name OPERATOR :FIELDNAME
        $parameterName = $this->getNewParameterName($queryBuilder);

                                          // This is the first difference
                                          //        |
                                          //        V
        $this->applyWhere($queryBuilder, sprintf('lower(%s.%s) %s :%s', $alias, $field, $operator, $parameterName));

        if ($data['type'] == ChoiceType::TYPE_EQUAL) {

                                          // This is the second difference
                                          //                |
                                          //                V
            $queryBuilder->setParameter($parameterName, strtolower($data['value']));
        } else {
            $queryBuilder->setParameter($parameterName, sprintf($this->getOption('format'), strtolower($data['value'])));
        }
    }

    /**
     * exact copy-paste because private method
     */
    private function getOperator($type)
    {
        $choices = array(
            ChoiceType::TYPE_CONTAINS         => 'ILIKE',
            ChoiceType::TYPE_NOT_CONTAINS     => 'NOT LIKE',
            ChoiceType::TYPE_EQUAL            => '=',
        );

        return isset($choices[$type]) ? $choices[$type] : false;
    }
}

@nicovak, 👍 thx

Note that if you want this code to handle special characters (like Ü => ü), you need to replace calls to strtolower($data['value']) with mb_strtolower($data['value'], 'UTF-8').

Here is updated version for sonata admin v3

public function filter(ProxyQueryInterface $query, string $alias, string $field, FilterData $data): void
    {
        $value = $data->getValue();
        $type = $data->getType();
        $value = trim($value);

        if (strlen($value) == 0) {
            return;
        }

        $type = !isset($type) ?  ContainsOperatorType::TYPE_CONTAINS : $type;

        $operator = $this->getOperator((int) $type);

        if (!$operator) {
            $operator = 'LIKE';
        }

        // c.name > '1' => c.name OPERATOR :FIELDNAME
        $parameterName = $this->getNewParameterName($query);

        // This is the first difference
        //        |
        //        V
        $this->applyWhere($query, sprintf('lower(%s.%s) %s :%s', $alias, $field, $operator, $parameterName));

        if ($type == ContainsOperatorType::TYPE_EQUAL) {

            // This is the second difference
            //                |
            //                V
            $query->setParameter($parameterName, strtolower($value));
        } else {
            $query->setParameter($parameterName, sprintf('%%%s%%', strtolower($value)));
        }
    }

    private function getOperator($type)
    {
        $choices = array(
            ContainsOperatorType::TYPE_CONTAINS         => 'LIKE',
            ContainsOperatorType::TYPE_NOT_CONTAINS     => 'NOT LIKE',
            ContainsOperatorType::TYPE_EQUAL            => '=',
        );

        return isset($choices[$type]) ? $choices[$type] : false;
    }

replace this two methods with above code