带Doctrine 2查询生成器的Regex ?

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.

如标题所示，如何将正则表达式与Doctrine 2查询生成器匹配?基本上我是在尝试生成唯一的蛞蝓。

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.

这是我当前的实现。我生成蛞蝓。然后我检查是否有像这种鼻涕虫一样的鼻涕虫在使用。如果有，我将在slug的末尾添加一个-{number}，其中{number}是尚未使用的最低号。

$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.

这里的问题是，slug%可以匹配foo-bar-1、foo-bar-2和foo-bar-not-同-slug。更干净的是一个regex寻找regex slug-(\d+)或类似的东西。

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

对Doctrine 2查询构建器有什么方法来实现这一点吗?

4 个解决方案

#1

add the DoctrineExtensionsBundle - update your composer.json:

添加doctrine ineextensionsbundle——更新组合。

"beberlei/DoctrineExtensions": "0.3.x-dev",

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

添加REGEXP配置——更新应用程序/config.yml

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

where ever your QueryBuilder is do this:

在哪里你的QueryBuilder是这样做的:

$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

不要忘记使用SQL兼容的regexes

#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.

REGEXP是特定于供应商的函数，所以Doctrine本身不支持它。另外，与其说它是一个函数，不如说它是一个比较运算符(参见这个答案)。但是您可以使用字段上的函数与另一个值进行比较。doctrine ineextensions(由doctrine的贡献者编写)具有在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.

如果您不想使用Doctrine ineextensions，您可以按照本文编写自己的扩展，或者查看这个Doctrine扩展的代码，并编写自己的自定义DQL函数。

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

我已经确认REGEXP使用理论扩展非常适合我的需要!

#3

I did like this

我做了这样的

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

#4

-2

Not tested (for MySQL):

不是测试(MySQL):

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

#1