DoctrineJsonFunctions DBAL simple trait

README.md

Basic DBAL for one Json operation

currently only tested with sqlite but should be ready for comments.

add the following to your repository class:

use Infrastructure\ORM\Repository\Traits;

class ExampleSubjectRepository extends ServiceEntityRepository
{
    use Traits\JsonPlatformDbal;

now you can use the following in your repository methods:

$segment = 'example_search';

$queryBuilder = $this->createQueryBuilder('s');
$queryBuilder
    ->where('s.sequence = :sequence')
    ->setParameter('sequence', $sequence)
;
$this->andWhereJson($queryBuilder, 'number', 'segment', $segment);
$result = $queryBuilder->getQuery()->getOneOrNullResult();

where this

$this->andWhereJson($queryBuilder, 'number', 'segment', $segment);

translates to the following on sqlite

->where("JSON_EXTRACT(s.number, '$.segment') = 'search_query'")
->setParameter(':search_query', $searchQuery) //$searchQuery contains $segment which contains `example_search`

and to the following on postgresql

->where("JSON_EXTRACT(s.number, 'segment') = 'example_search'")
->setParameter(':search_query', $searchQuery) //$searchQuery contains $segment which contains `example_search`

JsonPlatformDbal.php

<?php

namespace Infrastructure\ORM\Repository\Traits;

use Doctrine\ORM\QueryBuilder;
use Doctrine\ORM\EntityManagerInterface;
use Exception;

/**
 * Trait to be used in Doctrine repositories as DBAL (database abstraction layer) to access json fields database
 * agnostic (currently supports postgresql, sqlite and mysql
 *
 * this trait assumes that you have installed DoctrineJsonFunctions https://github.com/ScientaNL/DoctrineJsonFunctions
 *
 * see also  * https://github.com/dunglas/doctrine-json-odm (not used here)
 * 
 * - postgres https://www.postgresql.org/docs/9.3/functions-json.html
 *      ->where("JSON_GET_TEXT(c.attributes, 'gender') = :gender"); postgres
 *      (->> 	text 	Get JSON object field as text 	'{"a":1,"b":2}'::json->>'b')
 * - sqlite https://www.sqlite.org/json1.html#jex
 *      ->where("JSON_EXTRACT(c.attributes, '$.gender') = :gender"); sqlite
 * - mysql https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-extract
 *      ->where("JSON_EXTRACT(c.attributes, '$.gender') = :gender"); mysql
 */
trait JsonPlatformDbal
{
    private array $platformSqlTemplate = [
      'postgresql' => ['operator' => 'JSON_GET_TEXT', 'json_selector_prefix' => ''],
      'sqlite' => ['operator' => 'JSON_EXTRACT', 'json_selector_prefix' => '$.'],
      'mysql' => ['operator' => 'JSON_EXTRACT', 'json_selector_prefix' => '$.'],
    ];

    /**
     * Do not add return type here or it the method signature is not compatible.
     *
     * @return EntityManagerInterface
     */
    abstract public function getEntityManager(); // read docblock

    protected function andWhereJson(
        QueryBuilder $queryBuilder,
        string $fieldName,
        string $jsonSelector,
        string $searchQuery,
        ?string $alias = null
    ): void {
        if (!$alias) {
            $alias = $queryBuilder->getRootAliases()[0];
        }
        $jsonFunction = $this->getPlatformJsonFunction();
        $jsonSelectorPrefix = $this->getPlatformJsonSelectorPrefix();

        $queryBuilder
            ->andWhere("$jsonFunction({$alias}.{$fieldName}, '{$jsonSelectorPrefix}{$jsonSelector}') = :search_query")
            ->setParameter(':search_query', $searchQuery)
        ;
    }

    private function getPlatformJsonFunction()
    {
        return $this->platformSqlTemplate[$this->getPlatformName()]['operator'];
    }

    private function getPlatformJsonSelectorPrefix()
    {
        return $this->platformSqlTemplate[$this->getPlatformName()]['json_selector_prefix'];
    }

    private function getPlatformName(): string
    {
        $name = $this->getEntityManager()->getConnection()->getDatabasePlatform()->getName();
        if (array_key_exists($name, $this->platformSqlTemplate)) {
            return $name;
        }

        throw new Exception("unsupported sql platform ");
    }
}