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.

Lars Strojny · Accepted Answer · 2014-08-24T10:27:45.980

3

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

edited Aug 24 '14 at 10:27

answered Jan 10 '12 at 22:59

Lars Strojny

667
4
11

Bad link on the blogpost, is it no longer up-to-date (doctrine1)? – Petter Soderlund Aug 20 '14 at 12:19
One thing to note: you have to add a comparison operator to the end of the REGEXP or else Doctrine will kick it back. Doctrine requires all where clauses to have a comparison operator even though REGEXP doesn't require it – Patrick Jun 25 '15 at 18:02

score 1 · Answer 2 · answered Jan 11 '12 at 08:36

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

score -1 · Answer 3 · answered Jan 30 '14 at 22:34

-1

It is possible to do the following, though not as pretty:

$builder->add('select', 'a.fileName')
        ->add('from',    '\Company\Bundle\Entity\Asset a')
        ->add('where',   'a.someField REGEXP '.$someRegex);

answered Jan 30 '14 at 22:34

Nathaniel Ford

20,545
20
91
102

Using REGEXP in Doctrine 2.x ORM

3 Answers3

Linked