Last active
July 18, 2023 18:04
-
-
Save dbu/9524776 to your computer and use it in GitHub Desktop.
Case insensitive filtering with sonata admin in postgres
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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; | |
} | |
} |
@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
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Ok, I don't need anymore an example ...
You just need to add an entry in services like this:
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'