在 Doctrine 2.x ORM 中使用 REGEXP

2022-01-03 00:00:00 orm php doctrine doctrine-orm

我对此进行了大量研究，我确定答案是否定的，但我很想证明自己是错的.

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

我想执行一个用 DQL 编写的包含 REGEXP 操作的查询.例如:

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

又名

$builder->add('select', 'a.fileName')
        ->add('from',    'CompanyBundleEntityAsset a')
        ->add('where',   'a.campaign=1')
        ->...REGEXP MAGIC...
        ->add('orderBy', 'a.fileName desc');

(这是一个简单的正则表达式，我意识到可以用 LIKE 来完成，但这只是一个例子 - 我真正的正则表达式更复杂)

(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)

我研究了 DoctrineORMQueryExpr 类，以及 QueryBuilder 类.我看不到对 REGEXP 的支持.SO 上有人发帖说他们使用了 Expr 类，但这实际上不起作用(他们说它未经测试).

I've looked into the DoctrineORMQueryExpr 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).

知道如何在不直接编写 SQL 的情况下在 DQL 中执行 REGEXP 吗?TIA.

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

推荐答案

问题不在于查询生成器无法为 MySQL 中的(非标准)REGEXP 功能创建查询，而在于即使您可以生成查询，DQL 解析器不可能不做任何处理就理解它.

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.

那个东西"正在扩展 Doctrine 的 DQL 以理解正则表达式语法.这可以通过扩展 DQL 来实现，如在博文.

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.

有关更多信息，请研究 DoctrineExtensions 的 MySQL 部分的代码

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

相关文章