在Doctrine 2.x ORM中使用REGEXP [英] Using REGEXP in Doctrine 2.x ORM

问题描述

我已经研究了很多，我确定答案是否定的，但我很乐意证明是错误的。

我想执行使用DQL编写的包含REGEXP操作的查询。例如：

  select * from assets 
其中，campaign_id = 1 
和fileName REGEXP'godzilla *' 
 order by fileName desc 
  

aka

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $'$'$' \\ Entity\Asset a'）
- > add（'where'，'a.campaign = 1'）
- > ... REGEXP MAGIC ...
- > ; add（'orderBy'，'a.fileName desc'）;

（这是一个简单的正则表达式，我意识到可以像LIKE一样完成，但这只是一个例子 - 我的真正的正则表达式更复杂）

我研究了Doctrine\ORM\Query\Expr类，再加上QueryBuilder类。我看不到REGEXP的支持。有人在SO上有一个帖子说他们使用Expr类，但这并不实际工作（他们表示未经测试）。

任何想法如何执行REGEXP DQL写/直写SQL？
TIA。

解决方案

问题不在于Query Builder无法为（非标准）在MySQL中，REGEXP功能更多，即使您可以生成查询，DQL解析器也无法理解它，而不做任何事情。

正在扩展Doctrine的DQL以了解正则表达式语法。这可以通过按照在博客。

有关详细信息，请参阅 DoctrineExtensions的MySQL部分

I've research this a great deal and I'm sure the answer is no, but I'd love to proven wrong.

I'd like to execute a query written in DQL that contains the REGEXP operation. For example:

select * from assets 
where campaign_id = 1
and fileName REGEXP 'godzilla*'
order by fileName desc

aka

$builder->add('select', 'a.fileName')
        ->add('from',    '\Company\Bundle\Entity\Asset a')
        ->add('where',   'a.campaign=1')
        ->...REGEXP MAGIC...
        ->add('orderBy', 'a.fileName desc');

(This is a simple regex and I realize could be done as a LIKE, but it's just an example - my real regex expression is more complicated)

I've looked into the Doctrine\ORM\Query\Expr class, plus the QueryBuilder class. I see no support for REGEXP. Someone on SO has a post saying they used the Expr class, but this doesn't actually work (they stated it was untested).

Any idea how to execute REGEXP in DQL w/out writing straight SQL? TIA.

解决方案

The issue is not so much that Query Builder cannot create queries for the (non-standard) REGEXP functionality in MySQL but more that even if you can generate your query, there is no way the DQL parser will understand it without doing something about it.

That "something" is extending Doctrine’s DQL to understand the regular expression syntax. This is doable by extending the DQL as described in a blog post.

For more information study the code of the MySQL part of DoctrineExtensions

这篇关于在Doctrine 2.x ORM中使用REGEXP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！