Symfony2 Doctrine - ILIKE clause for PostgreSQL?

Question

I am currently using symfony2, doctrine 2.3 and PostgreSQL 9. I have been looking for a couple of hours to see HOW to make ILIKE select on earth using QueryBuilder .

It seems they have only LIKE . In my situation, however, I am looking for case insensitive. How it's done?

 // -- this is the "like"; $search = 'user'; $query = $this->createQueryBuilder('users'); $query->where($query->expr()->like('users.username', $query->expr()->literal('%:username%')))->setParameter(':username', $search); // -- this is where I get "[Syntax Error] line 0, col 86: Error: Expected =, <, <=, <>, >, >=, !=, got 'ILIKE' $search = 'user'; $query = $this->createQueryBuilder('users'); $query->where('users.username ILIKE :username')->setParameter(':username', $search); 

Answer 1

I do not know about Symphony, but you can replace

 a ILIKE b 

with

 lower(a) LIKE lower(b) 

You can also try the ~~* operator, which is synonymous with ILIKE It has a slightly lower operator priority, so you may need a bracket for concatenated strings, where you could not with ILIKE

 a ILIKE b || c 

becomes

 a ~~* (b || c) 

Pattern matching guide starting with LIKE / ILIKE .

I think this guy had the same problem and got the answer:
http://forum.symfony-project.org/viewtopic.php?f=23&t=40424

Obviously, you can extend Symfony2 with the special features of the SQL provider:
http://docs.doctrine-project.org/projects/doctrine-orm/en/2.1/cookbook/dql-user-defined-functions.html

I am not a fan of ORMs and frameworks that share Postgres' rich features to remain "portable" (which almost never work).

Answer 2

This works for me (Symfony2 + Doctrine 2)

 $qq = 'SELECT x FROM MyBundle:X x WHERE LOWER(xy) LIKE :y'; $q = $em->createQuery($qq)->setParameter(':y', strtolower('%' . $filter . '%')); $result = $q->getResult(); 

Answer 3

From what I know, search queries in Symfony2 (at least when using Doctrine) are case in-sensitive . As noted in the Doctrine QL docs :

DQL is case sensitive, with the exception of namespaces, classes, and fields that are case sensitive.