CODEWITHSUNDEEP
phpsymfonydoctrine-orm
Clément Andraud
Clément Andraud

Reputation: 9269

Symfony2, doctrine and pgsql, search with accent insensitive

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)

Upvotes: 0

Views: 2072

Answers (2)

user3577953
user3577953

Reputation:

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.

Upvotes: 1

silkfire
silkfire

Reputation: 25945

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?

Upvotes: 0

Related Questions