Regex with Doctrine 2 query builder?

Question

As per the title, how would one match on a regular expression with the Doctrine 2 query builder? Basically I'm trying to generate unique slugs.

Here is my current implementation. I generate the slug. I then check to see if there are any slugs in use like this slug. If there are, I will append a -{number} to the end of the slug where {number} is the lowest number not already in use.

$qb->select(array('partial o.{id, slug}'))
   ->from('Foo\Bar\Entity\Object', 'o')
   ->where($qb->expr()->like('o.slug', ':slug'));

$slug = new SlugNormalizer($text);
$qb->setParameter('slug', $slug->__toString().'-%');

The problem here is LIKE slug% could match foo-bar-1, foo-bar-2, AND foo-bar-not-the-same-slug. What would be cleaner is a regex looking for REGEX slug-(\d+) or something similar.

Any way to do this with the Doctrine 2 query builder?

Audrieaudris

Answer 1

install the DoctrineExtensionsBundle:

composer require beberlei/doctrineextensions

add the REGEXP configuration - update your app/config.yml

doctrine:
    orm:
        dql:
            string_functions:
                regexp: DoctrineExtensions\Query\Mysql\Regexp

where ever your QueryBuilder is do this:

$qb = $this->createQueryBuilder('x');

return $qb->andWhere('REGEXP(x.your_property, :regexp) = true')
          ->setParameter('regexp', '[[:digit:]]{3}') // insert your own regex here
          ->getQuery()->getResult();

and don't forget to use SQL compatible regexes

Sup

It would be good to point a reference. github.com/beberlei/DoctrineExtensions – Floater

Answer 2

REGEXP is a vendor specific function so Doctrine itself doesn't support it. Plus it's not a function so much as a comparison operator (see this answer). But you can use the function on the field to compare with another value. DoctrineExtensions (written by a contributor to doctrine) has code to enable regular expression in MySQL.

Example from the File:

$query = $this->getEntityManager()->createQuery('SELECT A FROM Entity A WHERE REGEXP(A.stringField, :regexp) = 1');
$query->setParameter('regexp', '^[ABC]');
$results = $query->getArrayResult();

If you don't want to use DoctrineExtensions, you can write your own by following this blog post, or you can look at the code for this Doctrine extension and write your own custom DQL function.

I have confirmed that REGEXP using DoctrineExtensions works great for my needs!

Polecat

Answer 3

Not tested (for MySQL):

$qb->where(new Doctrine\ORM\Query\Expr\Comparison(
    'o.slug', 'REGEXP', ':slug')
);
$qb->setParameter('slug', '^'.$slug->__toString().'-[[:digit:]]+$');

Amadeus

This does not work. The Doctrine\ORM\Query\Expr\Comparison class only has the following operators: const EQ = '='; const NEQ = '<>'; const LT = '<'; const LTE = '<='; const GT = '>'; const GTE = '>='; There is NO REGEXP option! Plus it's not a comparison operation ... why would it live there in the 1st place? – Schreiber

You're right, it does not work. But it's a comparison operation. Two operand, one operator between them, for me it is a comparison. – Amadeus

Ah, when you state it like that, maybe you're right. Could be a comparison op .. regardless, heh, it doesn't work. =[ – Schreiber

Answer 4

I did like this

 $query->andWhere('REGEXP(r.status, :text) = 1')
       ->orWhere('REGEXP(r.comment, :text) = 1')
       ->setParameter('text',MY REGULAR EXP);

Disentangle

What do you mean with the second parameter in setParameter (MY REGULAR EXP)? – Geulincx

setParameter is a method of doctrine. – Disentangle