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. Database

Building extension of PostgreSQL dialect for JSON support - few questions

Purpose

  • Support for json and jsonb in PHQL for PostgreSQL dialect, I've explained it here:

https://github.com/phalcon/cphalcon/issues/13115

I want to add support only for WHERE conditions, I do not need it for SELECT ... FROM section.

If I succeed, I will pull request in Phalcon incubator.

Current plan / my thought process:

  • Extend current PostgreSQL dialect, like:

  <?php

  namespace inopx\db;

  class InopxPgDialect extends \Phalcon\Db\Dialect\Postgresql {

  ....

inopx is my private namespace of choice.

  • Introduce new expression type json, and detect it, for example:

    \k3\product\K3ProductEntity.title::jsonb <@ '{"name":"Thor"}'

    Where do I start? I mean is there some common funcion where all SQL statements go regardless of using find(), findFirst(), exectute() on Query or Connection Object?

  • Extend getSqlExpression method, and support for new json expression type

    Pass oryginal json expression without modyfying or scanning/validating it to the final SQL Expression.

    Except for modifications of Class properties into valid SQL column name, for example \k3\product\K3ProductEntity.title -> "product"."title"

    But im not sure right now if it will be needed.

  • Is it proper way to add json support for PostgreSQL?

What's more need to / should be done regarding new expression type?



10.4k

Update: I just got info, that adding new expression type would require update of PHQL parser written in Lemon:

https://github.com/phalcon/cphalcon/tree/master/ext/phalcon/mvc/model/query

as im not familiar with Lemon at all, I will probably give up and create own simple SQL Query Builder or use doctrine + jsonb patch for that matter.



145.0k

You can always just add it as a function, then just adding dialect extension or extend getSqlExpression will work.



10.4k
edited Nov '17

Sorry but I doesn't understand

1) add what as a function, and where

2) getSqlExpression as argument is taking preprocessed expressions in form of array containing expression type in expression["type"]

https://github.com/phalcon/cphalcon/blob/master/phalcon/db/dialect.zep - line 256

so i need a way to detect which condition is a JSON/JSONB condition and then pass it to getSqlExpression method in my PostgreSQL dialect class



10.4k
Accepted
answer
edited Nov '17

Ok, I've found few older posts regarding dialext extensions and registering functions.

Fortunately Postgresql jsonb operators like <@, or ? can be represented as functions for example jsonb_contains or jsonb_exists.

Now:

$dialect = new \Phalcon\Db\Dialect\Postgresql();
$dialect->registerCustomFunction(
    'jsonb_exists',
    function($dialect, $expression) {
        $arguments = $expression['arguments'];
        return sprintf(
            "jsonb_exists(%s, %s)",
            $dialect->getSqlExpression($arguments[0]),
            $dialect->getSqlExpression($arguments[1])
        );
    }
);
$dialect->registerCustomFunction(
    'jsonb_contains',
    function($dialect, $expression) {
        $arguments = $expression['arguments'];
        return sprintf(
            "jsonb_exists(%s, %s)",
            $dialect->getSqlExpression($arguments[0]),
            $dialect->getSqlExpression($arguments[1])
        );
    }
);

$connection =  new \Phalcon\Db\Adapter\Pdo\Postgresql(array(
  'host' => 'localhost',
  'port' => '5432',
  'dbname' => 'db_name',
  'username' => 'postgres',
  'password' => 'secret',
  'dialectClass' => $dialect
));

// This is working...
$sql2 = 'SELECT * FROM product WHERE jsonb_exists(product."picSmall", \'dataBase64\') LIMIT 1 OFFSET 0;';
$sql2 = 'SELECT * FROM product WHERE jsonb_contains(product."picSmall", \'{"dataBase64":23}\') LIMIT 1 OFFSET 0;';

...and it works like a charm.

But honestly... things like above should be explained in the documentation...