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

Hello,

Can you make an example for how to use this class ?

I appreciate that :-)

Thank you

Ok, I don't need anymore an example ...

You just need to add an entry in services like this:

services: 
    sonata.admin.orm.filter.type.insensitive_string:
        class: Acme\YourBundle\Filter\CaseInsensitiveStringFilter
        tags:
            - { name: sonata.admin.filter.type, alias: doctrine_orm_istring }

After add doctrine_orm_istring in second parameter of your filters and thats all ...

However, the operator ILIKE doesnt work, I had to replace with LIKE in private function getOperator because I got an error: QueryException: [Syntax Error] line 0, col 154: Error: Expected =, <, <=, <>, >, >=, !=, got 'ILIKE'

@dbu : line 54, you state you're copy / pasting exactly, and line 59, you change LIKE to ILIKE… I don't think ILIKE needs to be used at all here

I tested it, the "ILIKE" throws an exception, you have to replace it by :

ChoiceType::TYPE_CONTAINS         => 'LIKE',

And the service is : (we just redeclare the service to use the new class)

sonata.admin.orm.filter.type.string:
    class: Acme\MyBundle\Admin\Filter\CaseInsensitiveStringFilter
    tags:
        - { name: sonata.admin.filter.type, alias: doctrine_orm_string }

You can find a complete example here: https://github.com/nicovak/doctrine-unaccent-extension-postgres

@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