Search code examples
phpormdoctrinedoctrine-orm

Using REGEXP in Doctrine 2.x ORM

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.

Solution

  • 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