Symfony2 Doctrine - ILIKE clause for PostgreSQL?

Question

I'm currently using symfony2, doctrine 2.3 and PostgreSQL 9. I've been searching for a couple of hours now to see HOW on earth do I do a ILIKE select with QueryBuilder.

It seems they only have LIKE. In my situation, though, I'm searching case-insensitive. How on earth is it 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);

score 11 · Accepted Answer · edited Jul 22 '21 at 13:50

11

I don't know about Symfony, but you can substitute

a ILIKE b

with

lower(a) LIKE lower(b)

You could also try the operator ~~*, which is a synonym for ILIKE It has slightly lower operator precedence, so you might need parenthesis for concatenated strings where you wouldn't with ILIKE

a ILIKE b || c

becomes

a ~~* (b || c)

The manual about pattern matching, starting with LIKE / ILIKE.

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

Obviously, you can extend Symfony2 with SQL vendor specific functions:
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 butchering the rich functionality of Postgres just to stay "portable" (which hardly ever works).

edited Jul 22 '21 at 13:50

Stephan Vierkant

9,674
8
61
97

answered Dec 09 '12 at 03:20

Erwin Brandstetter

605,456
145
1,078
1,228

The `lower(a)/lower(b)` is working in the query itself but it's not in doctrine for some reason. Do you have an example for that? – tftd Dec 09 '12 at 03:55
@tftd: My expertise is with PostgreSQL. But I think I found something for you. – Erwin Brandstetter Dec 09 '12 at 04:11
Yeah.. I also don't like the ORMs that are meant to be "portable". That never works as planned! Thanks for the help, the solution from symfony-project works with a bit of tuning. – tftd Dec 09 '12 at 04:27

score 8 · Answer 2 · answered Feb 03 '13 at 21:13

8

This works for me (Symfony2 + Doctrine 2)

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

answered Feb 03 '13 at 21:13

Andrei Tchijov

559
5
11

It works but it is slow when querying big amount of records – Hokusai Jul 04 '17 at 14:42
It does not hit index when using LOWER – Ernestas Stankevičius Sep 12 '19 at 06:24

score 4 · Answer 3 · edited Feb 03 '21 at 21:27

Unfortunately, we still do not have the ability to create custom comparison operators...

But we can create a custom function in which we implement the comparison.

Our DQL query well look like this:

SELECT q FROM App\Entity\Customer q WHERE ILIKE(q.name, :name) = true

The class describing the ILIKE function will look like this:

class ILike extends FunctionNode
{
    /** @var Node */
    protected $field;
    /** @var Node */
    protected $query;

    /**
     * @param Parser $parser
     *
     * @throws \Doctrine\ORM\Query\QueryException
     */
    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->field = $parser->StringExpression();
        $parser->match(Lexer::T_COMMA);
        $this->query = $parser->StringExpression();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    /**
     * @param SqlWalker $sqlWalker
     *
     * @return string
     * @throws \Doctrine\ORM\Query\AST\ASTException
     */
    public function getSql(SqlWalker $sqlWalker)
    {
        return '(' . $this->field->dispatch($sqlWalker) . ' ILIKE ' . $this->query->dispatch($sqlWalker) . ')';
    }
}

The resulting SQL will look like this:

SELECT c0_.id AS id_0,
       c0_.name AS name_1,
FROM customer c0_
WHERE (c0_.name ILIKE 'paramvalue') = true

score 1 · Answer 4 · edited Aug 24 '15 at 21:10

1

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

DQL is case in-sensitive, except for namespace, class and field names, which are case sensitive.

edited Aug 24 '15 at 21:10

Barett

5,826
6
51
55

answered Apr 30 '14 at 15:10

maniakan

11
3

3

I'm pretty sure they're referring to the column names used in the query rather than the values you're searching i.e. username=MyUser will not return the same result as username=myusername. Anyway, I overcome this problem by either using the selected answer. – tftd May 01 '14 at 03:07

Symfony2 Doctrine - ILIKE clause for PostgreSQL?

4 Answers4

Linked