Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Case insensitive filtering with sonata admin in postgres
<?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;
}
}
@Bomere

This comment has been minimized.

Copy link

@Bomere Bomere commented Aug 11, 2014

Hello,

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

I appreciate that :-)

Thank you

@Bomere

This comment has been minimized.

Copy link

@Bomere Bomere commented Aug 11, 2014

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'

@greg0ire

This comment has been minimized.

Copy link

@greg0ire greg0ire commented Nov 20, 2014

@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

@nicovak

This comment has been minimized.

Copy link

@nicovak nicovak commented May 29, 2015

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

@ruscon

This comment has been minimized.

Copy link

@ruscon ruscon commented Nov 26, 2015

@nicovak, 👍 thx

@gnutix

This comment has been minimized.

Copy link

@gnutix gnutix commented Apr 12, 2016

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').

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment