[BUG] phalcon Does not support REGEXP and RLIKE in query model

[amin-ajami]

AWESOME !!!!!!!

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

Syntax error, unexpected token IDENTIFIER(REGEXP), near to ' ",(4)," ', when parsing: SELECT [Multiple\Frontend\Models\Test].* FROM [Multiple\Frontend\Models\Test] WHERE kind REGEXP ",(4),"

like this error as open question :

http://forum.phalconphp.com/discussion/1193/select-query-by-regexp-error

#2532

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

[akaNightmare]

http://joxi.ru/j1A5Z7jSKMRnrE

[phalcon]

Why is this a bug?

[calinrada]

It is not a bug. It is something that would be nice to have. I will close the question from the forum with the same explanation. @akaNightmare use raw sql.

[amin-ajami]

hi , why phalcon ORM dos not support REGEXP and RLIKE ?
maybe this problem dos not bug but can change performance and create problem in unique program structure !!
why we should change some part of the big project for phalcon ORM dos not support for example REGEXP or RLIKE!

[amin-ajami]

akaNightmare solution is correct but we cant change model structure in some part .

[dugwood]

Implement your own dialect, as REGEXP and RLIKE are MySQL specific functions:
http://forum.phalconphp.com/discussion/1748/date-sub-interval-mysql

[brilliapps]

I think REGEX is important. It certainly takes much time to implement REGEX for sqlite, postrgres and mysql. By the way if have PDO object for sqlite you have to FIRST define your own function handling REGEX param and expression which uses preg_replace (PCRE) or ereg (POSIX as in the case of mysql but ereg -like functions are deprecated in PHP). In phalcon in onConstruct method it could look like this (not tested and taken from stack overflow).

$this->db->getInternalHandler()->sqliteCreateFunction('regexp', function() {
if(preg_match('/^'.$pattern.'$/i', $string)) {return true;} return false;
}, 2);
But then is the dialect.

Unfortunately REGEXP operator doesn't work whatever function i add using sqliteCreateFunction ("Syntax error, unexpected token IDENTIFIER(REGEXP)") but a function alone regexp(aaa, 'bbb') works - i mean it at least calls getSqlExpression method, but the REGEX operator doesn't, so it is not possible to process anything. I even don't say about postgress and mysql and something cross-db-engine.

[andresgutierrez]

You can use custom functions to address this: https://blog.phalconphp.com/post/phalcon-2-0-3-released

[brilliapps]

Thank you for your reply. By the way I've been able to resolve my problem using sql Like so far to compare numbers somewhere in the middle of text. You just can imagine the code for it was terrible so i need to replace it (it was in text f.e. ::tag_a::number::/tag_a::).

[toramanlis]

Implement your own dialect, as REGEXP and RLIKE are MySQL specific functions: http://forum.phalconphp.com/discussion/1748/date-sub-interval-mysql

This means not only this is not a bug, but also it shouldn't be implemented directly int raw sql. However, there could be a method like regexWhere which has to be implemented separately in the adapters. The issue is that SQL, as a language, doesn't have a standard syntax for this functionality making it inconvenient for ORMs.