What's this?

This package provides Doctrine2 support for some specific PostgreSql 9.4+ features:

• Support of JSONB and array datatypes for integers, TEXT and JSONB
• Implementation of most used and useful functions related to the array and JSON datatypes
• Functions for basic support of text search

It can be integrated in a simple manner with Symfony, Laravel or any other framework that benefits from Doctrine2 usage.

You can easily extend package's behavior with your own array-like datatypes or other desired functions. Just follow the few steps in section Extend It! below.

---

How to Install?

Easiest possible way is through Composer

composer require "martin-georgiev/postgresql-for-doctrine=~0.8"

---

Integration with Symfony2

Register the new DBAL types

# Usually part of config.yml
doctrine:
    dbal:
        types: # register the new types
            jsonb: MartinGeorgiev\Doctrine\DBAL\Types\Jsonb
            jsonb[]: MartinGeorgiev\Doctrine\DBAL\Types\JsonbArray
            smallint[]: MartinGeorgiev\Doctrine\DBAL\Types\SmallIntArray
            integer[]: MartinGeorgiev\Doctrine\DBAL\Types\IntegerArray
            bigint[]: MartinGeorgiev\Doctrine\DBAL\Types\BigIntArray
            text[]: MartinGeorgiev\Doctrine\DBAL\Types\TextArray

Add mapping between DBAL and PostgreSql datatypes

# Usually part of config.yml
doctrine:
    dbal:
        connections:
            your_conenction:
                mapping_types:
                    jsonb: jsonb
                    jsonb[]: jsonb[]
                    _jsonb: jsonb[]
                    smallint[]: smallint[]
                    _int2: smallint[]
                    integer[]: integer[]
                    _int4: integer[]
                    bigint[]: bigint[]
                    _int8: bigint[]
                    text[]: text[]
                    _text: text[]

Integration with Laravel 5

Unfortunetly Laravel still doesn't have native integration with Doctrine. The steps below are based on FoxxMD's fork of mitchellvanw/laravel-doctrine integration. The package also works smoothly with Laravel Doctrine.

1. Register the functions and datatype mappings:

   [ 'name_of_your_entity_manager' => [ 'dql' => [ 'string_functions' => [ // Array data types related functions 'ALL_OF' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\All', // Avoid conflict with Doctrine's ALL implementation 'ANY_OF' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Any', // Avoid conflict with Doctrine's ANY implementation 'ARRAY' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Arr', 'ARRAY_APPEND' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayAppend', 'ARE_OVERLAPING_EACH_OTHER' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayAreOverlapingEachOther', 'ARRAY_CARDINALITY' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayCardinality', 'ARRAY_CAT' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayCat', 'ARRAY_PREPEND' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayPrepend', 'ARRAY_REMOVE' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayRemove', 'ARRAY_REPLACE' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayReplace', 'ARRAY_TO_STRING' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ArrayToString', 'STRING_TO_ARRAY' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\StringToArray', 'LEAST' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Least', 'GREATEST' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Greatest', 'IN_ARRAY' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\InArray', // Functions and operators used by both array and json(-b) data types 'CONTAINS' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Contains', 'IS_CONTAINED_BY' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\IsContainedBy', // Json(-b) data type related functions and operators 'JSON_GET_FIELD' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonGetField', 'JSON_GET_FIELD_AS_INTEGER' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonGetFieldAsInteger', 'JSON_GET_FIELD_AS_TEXT' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonGetFieldAsText', 'JSON_GET_OBJECT' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonGetObject', 'JSON_GET_OBJECT_AS_TEXT' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonGetObjectAsText', 'JSONB_ARRAY_ELEMENTS' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbArrayElements', 'JSONB_ARRAY_ELEMENTS_TEXT' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbArrayElementsText', 'JSONB_ARRAY_LENGTH' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbArrayLength', 'JSONB_EACH' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbEach', 'JSONB_EACH_TEXT' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbEachText', 'JSONB_EXISTS' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbExists', 'JSONB_OBJECT_KEYS' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbObjectKeys', // Basic text search related functions and operators 'TO_TSQUERY' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ToTsquery', 'TO_TSVECTOR' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\ToTsvector', 'TSMATCH' => 'MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Tsmatch', ], ], 'mapping_types' => [ 'jsonb' => 'jsonb', '_jsonb' => 'jsonb[]', 'jsonb[]' => 'jsonb[]', '_int2' => 'smallint[]', 'smallint[]' => 'smallint[]', '_int4' => 'integer[]', 'integer[]' => 'integer[]', '_int8' => 'bigint', 'bigint[]' => 'bigint[]', ],

2. Add EventSubscriber for Doctrine

3. Add the EventSubscriber for Doctrine to a ServiceProvider

   registerDoctrineEvents(); $this->registerDoctrineTypeMapping(); } /** * Register any other events for your application. * @param DispatcherContract $events * @return void */ public function boot(DispatcherContract $events) { parent::boot($events); } /** * Register Doctrine events. */ private function registerDoctrineEvents() { $eventManager = $this->registry()->getConnection()->getEventManager(); $eventManager->addEventSubscriber(new DoctrineEventSubscriber); } /** * Register any custom Doctrine type mappings */ private function registerDoctrineTypeMapping() { $databasePlatform = $this->registry()->getConnection()->getDatabasePlatform(); $entityManagers = Config::get('doctrine.entity_managers'); foreach ($entityManagers as $entityManager) { if (array_key_exists('mapping_types', $entityManager)) { foreach ($entityManager['mapping_types'] as $dbType => $doctrineName) { $databasePlatform->registerDoctrineTypeMapping($dbType, $doctrineName); } } } } /** * Get the entity manager registry * @return DoctrineManagerRegistry */ function registry() { return app(DoctrineManagerRegistry::class); } }

Integration with Doctrine2

<?php

use Doctrine\DBAL\Types\Type;

Type::addType('jsonb', "MartinGeorgiev\\Doctrine\\DBAL\\Types\\Jsonb");
Type::addType('jsonb[]', "MartinGeorgiev\\Doctrine\\DBAL\\Types\\JsonbArray");
Type::addType('smallint[]', "MartinGeorgiev\\Doctrine\\DBAL\\Types\\SmallIntArray");
Type::addType('integer[]', "MartinGeorgiev\\Doctrine\\DBAL\\Types\\IntegerArray");
Type::addType('bigint[]', "MartinGeorgiev\\Doctrine\\DBAL\\Types\\BigIntArray");
Type::addType('text[]', "MartinGeorgiev\\Doctrine\\DBAL\\Types\\TextArray");

---

Extend It!

How to add more array-like datatypes?

1. Extend MartinGeorgiev\Doctrine\DBAL\Types\AbstractTypeArray

2. You must give the new datatype a unique within your application name. For this purpose you can use the TYPE_NAME constant.

3. Depending on your new datatype's nature you may also need to overwrite some of the following methods:

   transformPostgresArrayToPHPArray() # E.g. this will be valid for postgresql's JSON datatype transformArrayItemForPHP() # In almost every case you will need to adjust the returned method to your specifc needs isValidArrayItemForDatabase() # It is encoraged to check that every element part of your php array is actually compatibale with your database datatype

How to add more functions?

1. Extend MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\AbstractFunction

2. Add behavior to your new function with overwriting customiseFunction() method. Use setFunctionPrototype() and addLiteralMapping() as in this example:

   setFunctionPrototype('array_append(%s, %s)'); $this->addLiteralMapping('StringPrimary'); # this will correspond to param №1 in the prototype set in setFunctionPrototype $this->addLiteralMapping('InputParameter'); # this will correspond to param №2 in the prototype set in setFunctionPrototype # Add as much literal mappings as you need. } }

Have in mind that you cannot use ? as part of any function protoype with Doctrine as this will result in faulty query parsing.

---

License

This package is licensed under the MIT License. Please, respect that!