We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

How can I extend the PHQL syntax? Is parser.lemon outdated?

Hello,

I'm in need for the latest PostgreSQL 9.3 JSON operators syntax (https://www.postgresql.org/docs/9.3/static/functions-json.html). Regarding this question: https://stackoverflow.com/q/21095399/1697320 on StackOverflow I am trying to get Phalcon to accept syntax like

Model::query()->where("data->>'email' = :email");

So I forked the cphalcon project and started digging into the source to find out that there is lemon behind the PHQL. But I'm assuming that the parsel.lemon is outdated in regard to parser.c? For starters the phql_ret_qualified_name function in parser.lemon is missing 3rd argument which was introduced in parser.c in rev 123065c (I'm currently on 1.2.6 branch). Am I missing something?

I've managed to recompile Phalcon using the parser.lemon from repo, but this only made things worse. It doesn't even recognize SELECT statment now.

I'v done this:

$ cd ext/mvc/model/query
$ gcc -o lemon lemon.c
$ ./lemon parser.lemon
$ git checkout -- parser.h  # since it was missing some define's after "./lemon parser.lemon" command
$ cat base.c >> parser.c
$ cd ../../../..
$ php scripts/gen-build.php
$ cd build/64bits/
$ make

The diff between my local parser.c and the GitHub version is now yielding almost every line. And even simple Model::query()->execute() throws errors:

ERROR: Syntax error, unexpected token SELECT, near...

So I would greatly appreciate if you guys could tell me what am I doing wrong, or provide some pointers on how can I make Phalcon to accept the syntax (maybe it can be done in PHP without recompiling the source?).

Also a side question: is there any chance on implementing this natively? I know that it is localized to a single RDMS, but with all that recent NoSQL boom pretty much everyone would like to use it with Postgres.



98.9k

PHQL only supports operators that can be translated into valid SQL statements on each of the supported database systems. I guess this is not supported by MySQL/SQLite, right?

We use this Makefile to generate the parser: https://github.com/phalcon/cphalcon/blob/1.3.0/ext/mvc/model/query/Makefile

You may also want to check raw queries: https://docs.phalcon.io/en/latest/reference/phql.html#using-raw-sql



2.2k

Ok, thanks for the pointer. I'll definetly dig into this as it got interesting :) Meanwhile I've found a pretty good workaround based on this solution: https://forum.phalcon.io/discussion/1082/sneaking-in-subqueries-into-phql#C4020

True, the syntax is specific to PosgreSQL. But databases these days have so many deviations from the SQL standard, that it is close to impossible to write a cross-db query. New features are provided by the database engines with the use of domain-specific language (which is very fashionable recently). In the end the choice for an application developer is: either sit quietly and be stuck with the SQL syntax from the 90's or get lost, don't use the ORM and write your queries as raw text.

My point is: there is an adapter for the MongoDB. Why not do the same with PostgreSQL? Or CouchDB? Or any other persistance system for that matter?

Sorry about the grumble, just had to get it out of my system ;) Thanks again for your answer :)



98.9k

Just maintaining the MongoDB adapter represents a lot of work for us, I'm afraid that we don't have enough resources to create/maintain more specialized adapters.



2.2k

Ok, you got me there. Lack of time is really an issue. I'll try to contribute after I get familiar with the code :)



66

Hello Guys,

It seems very old thread, we just migrated to posgtgres db as no sql db alternative. And now facing the same problem, as mentioned here. JSON operators are not supported by phalcon 3.2.2 version.

Can you guys please let me know how did you tackle and fixed mentioned issue?