Symfony2, doctrine and pgsql, search with accent insensitive

Question

I'm using Symfony2 and i have a simple query with createQueryBuilder like :

            $qb = $this->_em->createQueryBuilder();
            $qb->select(array('a'))
                    ->from('MyProjectBundle:Account', 'a')
                    ->where('LOWER(a.firstname) LIKE LOWER(?1)')
                    ->setParameters(array(1 => '%'.$search.'%'));
            return $qb->getQuery()->getResult();

I'm using pdo_pgsql for my database, how can i add an accent insensitive for this query ?

For now, i just have the lower, but i need to retrieve the account "Clément" when $search is "cle". This is for an autocomplete form (like facebook when you search someone)

No hehe, `->setParameters(array(1 => '%'.iconv($search).'%'));` — Sal00m, Apr 29 '14 at 13:11
Oh sorry, i didn't read.... check this http://stackoverflow.com/questions/13596638/function-to-remove-accents-in-postgresql — Sal00m, Apr 29 '14 at 13:18

score 1 · Accepted Answer · answered Apr 29 '14 at 15:20

You can create your own DQL function and then register it in your config.yml file. Registering in configuration can be done this way:

# app/config/config.yml
doctrine:
    orm:
        # ...
        dql:
            string_functions:
                test_string: Acme\HelloBundle\DQL\StringFunction
                second_string: Acme\HelloBundle\DQL\SecondStringFunction
            numeric_functions:
                test_numeric: Acme\HelloBundle\DQL\NumericFunction
            datetime_functions:
                test_datetime: Acme\HelloBundle\DQL\DatetimeFunction

Creating your own DQL function is a bit wider problematics, but it is well explained in DQL User Defined Functions.

score 0 · Answer 2 · edited May 23 '17 at 11:53

0

In MySQL, to retrieve values from a database in a diacritic-insensitive way, you should make sure that the collation of your fields are set to something neutral, like for example utf8_general_ci.

But for PostgreSQL, collations work in a different way. Perhaps this answer may help you:
Does PostgreSQL support "accent insensitive" collations?

edited May 23 '17 at 11:53

Community

1
1

answered Apr 29 '14 at 13:14

silkfire

24,585
15
82
105

I know this topic, but unaccent doesn't work with Symfony2 and Doctrine – Clément Andraud Apr 29 '14 at 13:15

Symfony2, doctrine and pgsql, search with accent insensitive

2 Answers2