Using REGEXP in Doctrine 2.x ORM

Question

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.

Answer 1

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

Answer 2

You can not do this with Doctrine2 for the moment. You can add custom function, but REGEXP is not a function, but a comparison operator. There is no support for customs comparison operators in Doctrine2 yet.

Look at this forum thread: https://groups.google.com/group/doctrine-user/browse_thread/thread/b98e37fc296c8183/06782192719156c6?lnk=gst&q=regexp#06782192719156c6

You should use Native SQL: http://www.doctrine-project.org/docs/orm/2.1/en/reference/native-sql.html