Doctrine Extensions REGEXP not working in Symfony2

Question

I am using Doctrine 2.1 and need to use a REGEXP in MySQL.

REGEXP is not currently supported in the default installation of Doctrine so I am using beberlei/DoctrineExtensions

I cannot get Doctrine to recognize my REGEXP, I have followed the example on SO.

I'm using Doctrine 2.1 and Symfony 2.7.1

Here's the code, any ideas?

Config

# config.yml
# Doctrine Configuration
doctrine:
    dbal:
        driver:   "%database_driver%"
        host:     "%database_host%"
        port:     "%database_port%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"
        charset:  UTF8
        keep_slave: true
        slaves:   %database_slaves%

    orm:
        auto_generate_proxy_classes: "%kernel.debug%"
        auto_mapping: true
        metadata_cache_driver: apc
        query_cache_driver: apc
        result_cache_driver:
            type: service
            id: cache
        dql:
            string_functions:
                regexp: DoctrineExtensions\Query\Mysql\Regexp

Repository

// GroupRepository
            $dql = "SELECT g FROM {$this->_entityName} g WHERE g.name REGEXP '^[:alpha:]'";
            return $this->getEntityManager()->createQuery($dql)->getResult();

Error on page load

// Error
CRITICAL - Uncaught PHP Exception Doctrine\ORM\Query\QueryException: "[Syntax Error] line 0, col 64: Error: Expected =, <, <=, <>, >, >=, !=, got 'REGEXP'" at /symfony/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php line 52

UPDATE:

I've updated the query to match the expected format

    $dql = "SELECT g FROM {$this->_entityName} g WHERE REGEXP(g.name, '^[:alpha:]')";

Now I have started getting a new error:

[Syntax Error] line 0, col -1: Error: Expected =, <, <=, <>, >, >=, !=, got end of string.

I think you need to do it like `WHERE REGEXP(g.name, '^[:alpha:]')`. This will then get translated by the extension to `(g.name REGEXP '^[:alpha:]')`. — qooplmao, Jun 25 '15 at 15:54
@Qoop I changed my query to match your suggestion but now I'm getting `[Syntax Error] line 0, col -1: Error: Expected =, <, <=, <>, >, >=, !=, got end of string.` — Patrick, Jun 25 '15 at 17:00
@Qoop If I add an `= 1` at the end of the query, it runs but now the SQL isn't what I need :-( `SELECT g FROM {$this->_entityName} g WHERE REGEXP(g.name, '^[:alpha:]') = 0 ` — Patrick, Jun 25 '15 at 17:15
I found that my character class was incorrect it is supposed to be `'^[^[:alpha:]]'`, which complicated finding the solution — Patrick, Jun 25 '15 at 17:56
@Qoop the issue was that I had to add an `= 1` to the end of the REGEXP expression so that Doctrine would recognize it. I posted my answer below, but I can't accept my own answer until tomorrow — Patrick, Jun 26 '15 at 13:57

score 4 · Accepted Answer · answered Jun 25 '15 at 17:59

4

I found the solution through the github page for the project

Doctrine requires that all where clauses require a comparison operator even though a clause like REGEXP doesn't require it.

$dql = "SELECT g FROM {$this->_entityName} g WHERE g.status = 1 AND REGEXP(g.name, '^[^[:alpha:]]') = 1";

Also my regex character class was incorrect in my original question.

answered Jun 25 '15 at 17:59

Patrick

3,142
4
31
46

1

the quotes are important! ->andWhere("REGEXP(t.something, '^[0-9]{12}$') = 1") - works ->andWhere('REGEXP(t.something, "^[0-9]{12}$") = 1') - doesn't work – Serge Velikan Aug 12 '15 at 10:55

Doctrine Extensions REGEXP not working in Symfony2

1 Answers1