Functions-and-Operators.md

Available functions

PostgreSQL function	DQL function	Implementation
ARRAY_AGG()	ARRAY_AGG	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ArrayAgg
ARRAY_TO_JSON()	ARRAY_TO_JSON	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ArrayToJson
CAST()	CAST	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\Cast
CEIL()	CEIL	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\Ceil
COUNT()	COUNT	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\Count
EXTRACT()	EXTRACT	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\Extract
FLOOR()	FLOOR	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\Floor
ILIKE()	ILIKE	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ILike
JSON_AGG()	JSON_AGG	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonAgg
JSON_ARRAY_LENGTH()	JSON_ARRAY_LENGTH	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonArrayLength
JSONB_AGG()	JSONB_AGG	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonbAgg
JSONB_ARRAY_LENGTH()	JSONB_ARRAY_LENGTH	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonbArrayLength
JSONB_BUILD_ARRAY()	JSONB_BUILD_ARRAY	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonbBuildArray
JSONB_BUILD_OBJECT()	JSONB_BUILD_OBJECT	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonbBuildObject
JSONB_EACH()	JSONB_EACH	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonbEach
JSONB_EACH_TEXT()	JSONB_EACH_TEXT	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonbEachText
JSONB_OBJECT_KEYS()	JSONB_OBJECT_KEYS	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonbObjectKeys
JSON_BUILD_ARRAY()	JSON_BUILD_ARRAY	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonBuildArray
JSON_BUILD_OBJECT()	JSON_BUILD_OBJECT	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonBuildObject
JSON_EACH()	JSON_EACH	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonEach
JSON_EACH_TEXT()	JSON_EACH_TEXT	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonEachText
JSON_OBJECT_KEYS()	JSON_OBJECT_KEYS	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonObjectKeys
PHRASETO_TSQUERY()	PHRASETO_TSQUERY	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\PhraseToTsQuery
PLAINTO_TSQUERY()	PLAINTO_TSQUERY	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\PlainToTsQuery
RANDOM()	RANDOM	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\Random
ROUND()	ROUND	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\Round
STRING_AGG()	STRING_AGG	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\StringAgg
ARRAY[]	ARRAY	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ToArray
BIGINT[]	BIGINT_ARRAY	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ToBigIntArray
BOOLEAN[]	BOOLEAN_ARRAY	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ToBooleanArray
INT[]	INT_ARRAY	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ToIntArray
TO_JSON()	TO_JSON	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ToJson
TO_JSONB()	TO_JSONB	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ToJsonb
SMALLINT[]	SMALLINT_ARRAY	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ToSmallIntArray
TEXT[]	TEXT_ARRAY	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ToTextArray
TO_TSQUERY()	TO_TSQUERY	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ToTsQuery
TO_TSVECTOR()	TO_TSVECTOR	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ToTsVector
TS_HEADLINE()	TS_HEADLINE	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\TsHeadline
TS_RANK()	TS_RANK	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\TsRank
TS_RANK_CD()	TS_RANK_CD	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\TsRankCd
WEBSEARCH_TO_TS_QUERY()	WEBSEARCH_TO_TS_QUERY	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\WebsearchToTsQuery

Available operators

PostgreSQL operator	DQL function	Implementation
@>	CONTAINS	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\Contains
||	JSONB_CONCAT	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonbConcat
?	JSONB_EXISTS	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonbExists
-	JSONB_REMOVE	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonbRemove
->	JSON_GET_FIELD	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonGetField
->>	JSON_GET_FIELD_AS_TEXT	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonGetFieldAsText
#>	JSON_GET_OBJECT	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonGetObject
#>>	JSON_GET_OBJECT_AS_TEXT	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonGetObjectAsText
&&	OVERLAPS	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\Overlaps
@@	TS_MATCH	Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\TsMatch

Integration with Doctrine

<?php

use Doctrine\ORM\Configuration;
use Doctrine\ORM\EntityManager;

$configuration = new Configuration();

$configuration->addCustomStringFunction('ARRAY_AGG', Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ArrayAgg::class);
$configuration->addCustomStringFunction('JSONB_CONCAT', Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonbConcat::class);
$configuration->addCustomStringFunction('JSON_GET_FIELD', Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonGetField::class);
$configuration->addCustomStringFunction('TO_TSVECTOR', Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ToTsVector::class);
$configuration->addCustomNumericFunction('TS_RANK', Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\TsRank::class);
$configuration->addCustomNumericFunction('TS_MATCH', Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\TsMatch::class);

// ...

$em = EntityManager::create($connection, $configuration);

Integration with Symfony

# config/packages/doctrine.yaml
doctrine:
    orm:
        dql:
            string_functions:
                array_agg: Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ArrayAgg
                jsonb_concat: Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonbConcat
                json_get_field: Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonGetField
                to_tsvector: Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ToTsVector
                ts_match: Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\TsMatch
            numeric_functions:
                ts_rank: Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\TsRank
        # or only for specific em
        entity_managers:
            em_name:
                string_functions:
                    array_agg: Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ArrayAgg
                    jsonb_concat: Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonbConcat
                    json_get_field: Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\JsonGetField
                    to_tsvector: Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\ToTsVector
                    ts_match: Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\TsMatch
                numeric_functions:
                    ts_rank: Pfilsx\PostgreSQLDoctrine\ORM\Query\AST\Functions\TsRank