[NFR] MySQL SELECT query: REGEXP

[zsilbi]

Problem

Here you can find the original post: http://forum.phalconphp.com/discussion/1193/select-query-by-regexp-error

Hi,

When i try to execute a query using the mysql regexp, i get this error:

Phalcon\Mvc\Model\Exception: Syntax error, unexpected token IDENTIFIER(regexp), near to ' "^:name:"', when parsing: SELECT * FROM Model WHERE name regexp "^:name:" (79)

I can confirm this.

Possible solution

Add REGEXP function be recognized by Phalcon\Mvc\Model.

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[kbtz]

It's not a bug. The query language used for models is PHQL which is quite similar to MySQL but haven't all it's functionality implemented (just like the REGEXP function).

Meanwhile you can implement a findByRawSql in your base model as demonstrated here. In this one you can use all MySQL-specific syntax.

[dugwood]

You can also extend the Dialect, see http://forum.phalconphp.com/discussion/1748/date-sub-interval-mysql for instance.

[kbtz]

@dugwood didn't know about that... nice!

[hailie-rei]

Will it be implemented?
I have a set if queries that depends on options:
$sql = $this->getModelsManager()->createBuilder()
->from(array('wgl' => '\Model\WGloss'))
->...
->where('wgr.lang = "ru"');

        if (isset($data['f']) && $data['f'] == 'y') {
            $sql->andWhere('LOWER(wgl.gloss) REGEXP :text:', array(
                'text' => '[[:<:]]' . $data['text'] . '[[:>:]]'
            ));
        } else {
            $sql->andWhere('LOWER(wgl.gloss) LIKE :text:', array(
                'text' => '%' . $data['text'] . '%'
            ));
        }

`
and it's not useful to write all set of queries using MySQL string. Is there a possibility to avoid this and use more flexible variant?

[Green-Cat]

This is not a missing feature, although phql only has standard SQL functions, you can add any vendor specific functions yourself since 2.0.3, see https://blog.phalconphp.com/post/phalcon-2-0-3-released.

@sergeyklay

[hailie-rei]

@Green-Cat Thanks! It's useful!