Use PostgreSQL NOT SIMILAR TO in Symfony Doctrine query builder

Question

I am attempting to use PostgreSQL's NOT SIMILAR TO exclude a blacklist from the results of a query,

When I run the query in my repository method below:

$qb = $this->getEntityManager()->createQueryBuilder('p');

$query = $qb
    ->select('p')
    ->from('CRMPiccoBundle:Person', 'p')
    ->where("lower(p.email) not similar to '(" . implode('|', $blacklist) . ")%'")
    ->getQuery();

return $query->getResult();

I get the following error:

[Doctrine\ORM\Query\QueryException]                                                                                                                
SELECT p FROM CRMPiccoBundle:Person p WHERE lower(p.email) not similar to '(abuse@|admin@|billing@|compliance@|devnull@)%' 

[Doctrine\ORM\Query\QueryException]                                     
[Syntax Error] line 0, col 94: Error: Expected end of string, got 'to'

However, when I run this query against my local DB with PgAdmin it works.

How can I achieve this with Doctrine using the Symfony Doctrine Query builder (or similar)? I am using PostgreSQL 9.5.5

Rendy Eko Prastiyo · Accepted Answer · 2017-02-16 06:36:50Z

1

$qb = $this->getEntityManager()->createQueryBuilder('p');

$select = $qb
    ->select('p')
    ->from('CRMPiccoBundle:Person', 'p')
;

foreach ($blacklist as $key => $item) {
    $select
        ->where('lower(p.email) NOT LIKE :key'.$key)
        ->setParameter('key'.$key, "$item%")
    ;
}

$query = $select->getQuery();

return $query->getResult();

edited Feb 16, 2017 at 6:36

answered Feb 16, 2017 at 3:58

Rendy Eko Prastiyo

1,0987 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rendy Eko Prastiyo Over a year ago

@crmpicco thanks for the edit, I don't keep an eye on that, because I only write the code without testing :D

crmpicco Over a year ago

Thanks for the answer, I didn't think about lining up the NOT LIKEs like that. It makes perfect sense when I seen your answer.

Biapy · Accepted Answer · 2024-01-03 07:50:36Z

0

martin-georgiev/postgresql-for-doctrine @ GitHub adds support for REGEXP, SIMILAR_TO, NOT_SIMILAR_TO, JSON_GET_FIELD and CONTAINS operators, among others.

answered Jan 3, 2024 at 7:50

Biapy

5904 silver badges12 bronze badges

Collectives™ on Stack Overflow

Use PostgreSQL NOT SIMILAR TO in Symfony Doctrine query builder

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related