Is there a way to use the new PostgreSQL 9.3 JSON operators in Phalcon?

Question

I'm looking for a way to take advantage of the new PostgreSQL JSON operators in Phalcon Framework.

A simple table to demonstrate:

CREATE TABLE "user"
(
  id_user serial NOT NULL,
  data json,
  CONSTRAINT pk_user PRIMARY KEY (id_user)
)

and a query which runs fine in psql:

select * from "user" where data->>'email' = '[email protected]';

However when used against the Phalcon model:

$users = UserModel::query()
            ->where("data->>'email' = :email:")
            ->bind(['email' => '[email protected]'])
            ->execute();

it produces syntax error:

ERROR: Syntax error, unexpected token >, near to '>'email' = :email:', when parsing: SELECT [Pht\Cli\Model\UserModel].* FROM [Pht\Cli\Model\UserModel] WHERE data->>'email' = :email:

I'm guessing that it has something to do with the PHQL parser not being able to process the syntax. But the question remains: how to use JSON query with Phalcon?

I've tried to wrap the JSON syntax into a Db\RawValue:

$users = UserModel::query()
            ->where(new RawValue("data->>'email' = '[email protected]'"))
            ->execute();

but it seems like it was made only for updating/inserting:

ERROR: Conditions must be string

I can always write a raw query, but that's obviously not the way to go. Also it would be nice to have the syntax available in Model::find and Model::findAll.

Answer 1

After a bit of digging into the source code and reading the forums I found a quite good workaround based on this forum post. The basic idea is to use custom database function syntax, catch and parse a special virtual function before executing the query:

UserModel::query()->where("PG_JSON_PATH(\"data->>'email'\") = :email:");

For this to work we need a specialized Postgres adapter (the one below also uses this hack to sneak in sub queries as in the original post):

class Postgresql93 extends \Phalcon\Db\Adapter\Pdo\Postgresql
{
    public function query($sqlStatement, $bindParams = null, $bindTypes = null)
    {
        $sqlStatement = $this->handle93syntax($sqlStatement);
        return parent::query($sqlStatement, $bindParams, $bindTypes);
    }

    private function handle93syntax($sqlStatement)
    {
        $specials = join('|', ['SUB_QUERY', 'PG_JSON_PATH']);
        $pattern = "/($specials)[\\s]*\\([\\s]*\\'(.*)\\'[\\s]*\\)/";

        $sqlStatement = preg_replace_callback(
            $pattern,
            function(array $matches){
                $content = str_replace("''", "'", $matches[2]);
                return $content;
            },
            $sqlStatement
        );

        return $sqlStatement;
    }
}