README

This is a library with additional classes for usage in combination with the Doctrine ORM.

Enable Postgres specific DQL functions

CAST implements the corresponding function from Postgres to convert types:

$queryBuilder->expr()->like('CAST(u.varchar, \'text\')', ':parameterName')

CONTAINS allows to use the postgres-only "@>" operator to search for values within jsonb fields or arrays.

$qb->andWhere("CONTAINS(u.numbers, :number) = true")
   ->setParameter('number', 3);

JSON_CONTAINS_TEXT allows to use the postgres-only "?" operator to search for strings within jsonb fields. This for example allows to filter Symfony users correctly by role, e.g. if you use role names that are part of others (ROLE_SUPER & ROLE_SUPER_ADMIN) where using LIKE would fail.

$qb->andWhere("JSON_CONTAINS_TEXT(u.roles, :searchRole) = true")
   ->setParameter('searchRole', 'ROLE_ADMIN');

JSON_CONTAINS_ANY_TEXT and JSON_CONTAINS_ALL_TEXT allow to use the postgres-only "?|" and "?&" operators to search for any or all of multiple strings within jsonb fields, e.g. to filter users that have at least one of the given roles. The parameter is an array, a single string is treated as an array with one element. Beware: For an empty array, JSON_CONTAINS_ANY_TEXT matches no record, JSON_CONTAINS_ALL_TEXT matches every record.

$qb->andWhere("JSON_CONTAINS_ANY_TEXT(u.roles, :searchRoles) = true")
   ->setParameter('searchRoles', ['ROLE_ADMIN', 'ROLE_EDITOR']);

JSON_FIELD_AS_TEXT allows to use the postgres-only "->>" operator to get JSON data within jsonb fields as string. This for example allows to search for records that embed JSON, e.g. if you store metadata or addresses:

$qb->andWhere("JSON_FIELD_AS_TEXT(u.address, :addrField) = :addrValue")
    ->setParameter('addrField', 'city')
    ->setParameter('addrValue', 'Dresden');

Add to config/packages/doctrine.yaml:

doctrine:
    orm:
        dql:
            string_functions:
                CAST: Vrok\DoctrineAddons\ORM\Query\AST\CastFunction
                CONTAINS: Vrok\DoctrineAddons\ORM\Query\AST\ContainsFunction
                JSON_CONTAINS_ALL_TEXT: Vrok\DoctrineAddons\ORM\Query\AST\JsonContainsAllTextFunction
                JSON_CONTAINS_ANY_TEXT: Vrok\DoctrineAddons\ORM\Query\AST\JsonContainsAnyTextFunction
                JSON_CONTAINS_TEXT: Vrok\DoctrineAddons\ORM\Query\AST\JsonContainsTextFunction
                JSON_FIELD_AS_TEXT: Vrok\DoctrineAddons\ORM\Query\AST\JsonFieldAsTextFunction

Enable types in Symfony

config/packages/doctrine.yaml

doctrine:
    dbal:
        types:
            # MySQL/MariaDB: store all date-times in UTC, the DATETIME column
            # has no timezone. On Postgres use the built-in datetimetz_immutable
            # (timestamptz) instead, it stores the instant in UTC natively.
            utcdatetime:
                name: datetime_immutable
                class: Vrok\DoctrineAddons\DBAL\Types\UTCDateTimeType

UTCDateTimeType replaces the datetime_immutable type, so it applies to every column of that type. Columns with the mutable datetime type are not converted.

Lockable entities

Implementing a simple LockableInterface allows unified handling of different entities that can be locked/unlocked by admins etc. The provided interface makes very little assumptions about your code, except that each entity can be locked/unlocked at any time (regardless of other states the entity might have).

The LockableTrait provides an opinionated implementation of that interface, by specifying a boolean property locked that defaults to false and that has a Symfony group attribute of default:read.

Slugs with correct umlauts in Symfony

Add this to your services.yaml to have ae, ue, oe in your slugs instead of a, u, o for ä, ü, ö.
This also handles some other chars, e.g. accents.
Requires the intl extension and symfony/translation-contracts.

    gedmo.listener.sluggable:
      class: Gedmo\Sluggable\SluggableListener
      tags:
        - { name: doctrine.event_listener, event: 'prePersist' }
        - { name: doctrine.event_listener, event: 'onFlush' }
        - { name: doctrine.event_listener, event: 'loadClassMetadata' }
      calls:
        - [ setTransliterator, [ [ 'Vrok\DoctrineAddons\Util\UmlautTransliterator', 'transliterate' ] ] ]

vrok / doctrine-addons

Package info

Security

Statistics

README

Enable Postgres specific DQL functions

Enable types in Symfony

Lockable entities

Slugs with correct umlauts in Symfony