[NFR]: Registering custom expressions in PHQL Dialects like PostgreSQL @@ Expression

[tztztztz]

Is your feature request related to a problem? Please describe.

I MUST use this kind of condition in PHQL:

to_tsvector('English', cast(client_data_original as TEXT)) @@ plainto_tsquery('English', ':cdo_ftxt:')

There are two problems:

1. Phalcon doesn't recognize parameters in functions, so I can't use

plainto_tsquery('English', ':cdo_ftxt:')

I need to set explicit value without parametrizing it, like this:

plainto_tsquery('English', 'John Smith')

2. Second more serious problem is, that PHQL doesn't recognize @@ expression, instead I can use function ts_match_vq, but after that, PostgreSQL is not using Fulltext index like it is using with @@ expression, so I really need PHQL to recognize @@ expression.

How can I modify PostgreSQL dialect in Phalcon source code to recognize such expressions?

Describe the solution you'd like
It would be ideal if we have way to register custom expressions to any dialect in similar way to register functions via registerCustomFunction method.

For example in PostgreSQL there are many very useful expressions to work with jsonb data type like: "|&>", "@>", "#>" etc. Currently I can't use them and it hurts.

Fixing those two problem would level up Phalcon ORM to higher level.

[tztztztz]

Another suggestion is to allow adding conditions with "native" mark, so it is not processed by PHQL, but left as it is.

I think it would be much simpler solution to implement / code.

Something like:

$builder->andWhereNative($condition, $bindParams, $bindTypes);

In that case condition and binding are not processed by PHQL but left for final PDO execution, with binds merged with PHQL binds.

[tztztztz]

I've also created list of non-standard operators in PostgreSQL:

https://github.com/tztztztz/List-of-all-PostgreSQL-10-operators/blob/master/non-standard-operators.txt

and full list of operators:

https://github.com/tztztztz/List-of-all-PostgreSQL-10-operators/blob/master/full-list.txt

[niden]

Tracked in #14608