/DoctrineJsonFunctions

Doctrine DQL functions for SQL JSON data type

Primary LanguagePHPMIT LicenseMIT

Latest Stable Version Total Downloads License Issue Stats Issue Stats

DoctrineJsonFunctions

A set of extensions to Doctrine 2 that add support for json query functions.

Table of Contents

DQL Functions

This library provide set of DQL functions.

Mysql 5.7+ JSON operators

PostgreSQL 9.3+ JSON operators

Basic support for JSON operators is implemented. This works even with Doctrine\DBAL v2.5. Official documentation of JSON operators.

  • GT(jsondoc, path)

    • expands to jsondoc->path in case of numeric path (use with JSON arrays)
    • expands to jsondoc->'path' in case of non-numeric path (use with JSON objects)
  • GT_GT(jsondoc, path)

    • expands to jsondoc->>path in case of numeric path (use with JSON arrays)
    • expands to jsondoc->>'path' in case of non-numeric path (use with JSON objects)
  • SHARP_GT(jsondoc, path)

    • expands to jsondoc#>'path'
  • SHARP_GT_GT(jsondoc, path)

    • expands to jsondoc#>>'path'

Please note that chaining of JSON operators in not supported (PR is welcomed)!

Installation

The recommended way to install DoctrineJsonFunctions is through Composer. Add the following dependency to your composer.json

{
	"require": {
		"syslogic/doctrine-json-functions": "~1.0"
	}
}

Alternatively, you can download the source code as a file and extract it.

Functions Registration

Doctrine2

Doctrine2 Documentation: "DQL User Defined Functions"

<?php

use Syslogic\DoctrineJsonFunctions\Query\AST\Functions\Mysql as DqlFunctions;

$config = new \Doctrine\ORM\Configuration();
$config->addCustomStringFunction(DqlFunctions\JsonExtract::FUNCTION_NAME, DqlFunctions\JsonExtract::class);
$config->addCustomStringFunction(DqlFunctions\JsonSearch::FUNCTION_NAME, DqlFunctions\JsonSearch::class);

$em = EntityManager::create($dbParams, $config);
$queryBuilder = $em->createQueryBuilder();

Usage

Mind the comparison when creating the expression and escape the parameters to be valid JSON.

Using Mysql 5.7+ JSON operators

$queryBuilder
  ->select('c')
  ->from('Customer', 'c')
  ->where("JSON_CONTAINS(c.attributes, :certificates, '$.certificates') = 1");
 
$result = $q->execute(array(
  'certificates' => '"BIO"',
));

Using PostgreSQL 9.3+ JSON operators

$queryBuilder
  ->select('c')
  ->from('Customer', 'c')
  ->where("GT_GT(c.attributes, 'gender') = :gender");
 
 $result = $q->execute(array(
    'gender' => 'male',
 ));

Extendability and Database Support

Architecture

Platform function classes naming rule is:

Syslogic\DoctrineJsonFunctions\Query\AST\Functions\$platformName\$functionName

Adding a new platform

To add support of new platform you just need to create new folder Syslogic\DoctrineJsonFunctions\Query\AST\Functions\$platformName and implement required function there according to naming rules

Adding a new function

If you want to add new function to this library feel free to fork it and create pull request with your implementation. Please, remember to update documentation with your new functions.