Doctrine LIKE case insensitive

Question

is it possible to make a search with Doctrine case insensitive?

Answer 1

This depends mainly on your Database-Server. A LIKE with MySQL is case insensitive a like with PostgreSQL is case sensitive. But you can help yourself with something like this:

$pattern = strtolower('HEllO WorlD');
$q = Doctrine_Query::create()
    ->select('u.username')
    ->from('User u')
    ->where("LOWER(u.username) LIKE ?", $pattern);

Answer 2

Also, you can try:

$queryBuilder->where('LOWER(b.title) LIKE LOWER(:query)')
        ->setParameter('query', '%' . $query . '%');

Important: After converting a string that contains special characters to lower case with strtolower(), the special characters don’t appear correct.

Answer 3

Other thing you can do is:

$qb->andWhere($qb->expr()->like('lower(o.name)', ':name'));

$qb->setParameter('name', '%'. strtolower($search) . '%');

Regards.

Answer 4

The best way to do this is to have a canonized username column, in that column you can store the lowercase version of the username column and any other canonization process you want to do and search and index that column instead of the username column, then just do

->where("u.canonic_username) LIKE ?", $pattern);

That way you can use indexes and all of that.