martin-georgiev / postgresql-for-doctrine Goto Github PK

PostgreSQL enhancements for Doctrine. Provides support for advanced data types (json, jssnb, arrays), text search, array operators and jsonb specific functions.

Home Page: https://packagist.org/packages/martin-georgiev/postgresql-for-doctrine

License: MIT License

PHP 100.00%

doctrine jsonb postgresql arrays doctrine-orm doctrine-dbal json-data

postgresql-for-doctrine's People

Contributors

Stargazers

Watchers

Forkers

hirewheel ppamment dionx magriv gemorroj arturslogins llaakkkk ossinkine bednic rdey rich-id rudireg gwendolenlynch odolbeau hason-contributions ergineker pierreboissinot massimilianobraglia tkuska nyholm ajgarlag devnix vysokeskoly bigwhoop gallyamow jenkoian keithbrink dmitryuk mpiot faniahy tacman shigekun bilalmallick priyadi micperr adrienbrault tomberriot alexander-schranz fluffydiscord captain-igloo

postgresql-for-doctrine's Issues

Add CAST function

probable realization same as mysql https://github.com/beberlei/DoctrineExtensions/blob/master/src/Query/Mysql/Cast.php

Adding ILIKE support

Hello Martin,

I use your package in my project, and I want to say BIG thanks for your work. Your package is really helpful.

And I want to suggest to you to add ILIKE function support.
I can do it by myself, but it would be great if you will help me with understanding what is inside your package.

Error with symfony and dql ilke function

I'm not sure it's an issue. But when I try to use your bundle with symfony 4.4. and the ILIKE function like this :

entity_managers:
            client:
                class_metadata_factory_name: Doctrine\ORM\Mapping\ClassMetadataFactory
                default_repository_class: Doctrine\ORM\EntityRepository
                quote_strategy: doctrine.orm.quote_strategy.default
                entity_listener_resolver: null
                repository_factory: doctrine.orm.container_repository_factory
                hydrators: {  }
                filters: {  }
                naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
                auto_mapping: true
                connection: dbalclient
                mappings:
                    Client:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity/Client'
                        prefix: 'App\Entity\Client'
                        alias: Client
                dql:
                    string_functions:
                        ILIKE: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Ilike

In my controller :

$clientRepository = $this->emc->getRepository(Client::class);
            /** @var ORMQueryBuilder */
            $searchQuery = $clientRepository->createQueryBuilder('c');
            $searchQuery->select('partial c.{id}');
                if (isset($name)) {
		    $searchQuery->andWhere('ILIKE(c.name, :name) = TRUE');
                    $searchQuery->setParameter('name', $nom);
		}

I got this error :

<!-- It's a requirement to specify a Metadata Driver and pass it to Doctrine\ORM\Configuration::setMetadataDriverImpl(). (500 Internal Server Error) -->

Error with UNACCENT

HI,

I'm getting an error using the unaccent function :

An exception has been thrown during the rendering of a template ("An exception occurred while executing a query: SQLSTATE[42883]: Undefined function: 7 ERROR: function unaccent(character varying) does not exist
LINE 1: ... status s3_ ON s2_.status_id = s3_.id WHERE LOWER(unaccent(c...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.").

My Config :

doctrine:

    orm:
        dql:
            datetime_functions:
                DATE: Oro\ORM\Query\AST\Functions\SimpleFunction
            numeric_functions:
                TIMESTAMPDIFF: Oro\ORM\Query\AST\Functions\Numeric\TimestampDiff
            string_functions:
                UNACCENT: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Unaccent
                JSONB_EXISTS: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbExists
                ANY_OF: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Any
                LEAST: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Least
                GREATEST: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Greatest
                STRING_TO_ARRAY: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\StringToArray
                JSON_GET_FIELD: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonGetField
                JSON_GET_FIELD_AS_TEXT: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonGetFieldAsText

And my code :

$qb = $this->em->createQueryBuilder()
                                ->select('entity')
                                ->from($entityClass, 'entity')
                                ->leftJoin('entity.company_client', 'company')
                                ->leftJoin('entity.current_status', 'statusDetails')
                                ->leftJoin('statusDetails.status', 'status')
                                ->where('LOWER(UNACCENT(company.commercial_name)) LIKE :query')
                                ->orWhere("entity.number LIKE :query")
                                ->orWhere('LOWER(status.label) LIKE :query')
                                ->setParameter('query', '%'.$searchQuery.'%');

Any idea what could be wrong ? The other functions seems to be working.

Thanks in advance for your help,
Julien

How to use TO_JSON with JSON_GET_FIELD_AS_TEXT

I want to get sql like select (np.params::json)->> 'slug', np.params is a text field

DDL:

JSON_GET_FIELD_AS_TEXT(TO_JSON(np.params), 'slug')

Finally got an error:
Doctrine\ORM\Query\QueryException: [Syntax Error] line 0, col 110: Error: Expected Doctrine\ORM\Query\Lexer::T_INPUT_PARAMETER, got 'np'
What I do wrong?

Symfony5 Error: Class 'jsonb_each_text' is not defined.

I installed bundle and after try to execute simple query faced with error. What's wrong with my configuration ?

doctrine:
    dbal:
        types:
            jsonb: MartinGeorgiev\Doctrine\DBAL\Types\Jsonb
            jsonb[]: MartinGeorgiev\Doctrine\DBAL\Types\JsonbArray
        mapping_types:
            jsonb: jsonb
            jsonb[]: jsonb[]
        schema_filter: '~^(?!messenger_messages)~'
        driver: 'pdo_pgsql'
        server_version: '9.6'
        charset: utf8
        default_table_options:
            charset: utf8
            collate: utf8_unicode_ci

        url: '%env(resolve:DATABASE_URL)%'
    orm:
        dql:
            string_functions:
                JSONB_EACH_TEXT: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\JsonbEachText

        auto_generate_proxy_classes: true
        naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
        auto_mapping: true
        mappings:
            App:
                is_bundle: false
                type: annotation
                dir: '%kernel.project_dir%/src/Entity'
                prefix: 'App\Entity'
                alias: App

query

    $query = $entityManager
        ->createQuery("
    SELECT p
    FROM App:Product p
    JOIN jsonb_each_text(p.extras) e on true
    WHERE e.value = 'world'
    ");

[Semantical Error] line 0, col 58 near 'jsonb_each_text(p.extras)': Error: Class 'jsonb_each_text' is not defined.

Example for JsonGetFieldAsText

Hi,

Could you please give me an example in Doctrine for this SQL query :

SELECT id, title
FROM my_table
where title->>'fr' like '%AAA%';

I've tried some syntaxe in DQL, but i don't find a way to use it.

First off I want to say this package looks really awesome. I am trying out postgres for the first time because I want to be able to store and query json for a particular use case I have... However I'm having some issues figuring out how to actually use the tools provided in this package. A few examples would be really great, though I am grateful for your time putting the package together either way.

Thanks again!

Feature: add SIMILAR TO functions

Suggesting to add support for SIMILAR TO and NOT SIMILAR TO functions

See doc https://www.postgresql.org/docs/9.6/functions-matching.html
Pull request #56

Example for ILIKE, ANY_OF, IS_CONTAINED_BY, to pretend problem as in #33

#33 - There was a problem with using ILIKE function, I also had the same problem with ANY_OF, IS_CONTAINED_BY, so it will great to add a page like code_to_inspire.md and add there live examples.
I have some example there https://speakerdeck.com/llaakkkk/what-ive-learnt-after-starting-to-work-with-symfony-and-postgresql?slide=13

? and ?| operators

if attributes is a jsonb column containing attributes {"author": ["mark_twain"]}, how to I write this query in doctrine?

select code, attributes from instance where (attributes->'author')::jsonb ? 'mark_twain';

CONTAINS maps to @>, but I think I need the ? operator (actually, I think I need the ?| operator, since I'll have multiple elements, e.g. mark_twain or walt_whitman)

Thanks

`ANY` should accept `text[]` field, currently throw a `Expected Doctrine\ORM\Query\Lexer::T_SELECT`

i.e if I have a field featureFlags which is declared as text[]

and I try to do

       $this->em->createQuery("

            SELECT u FROM App\Entity\EndUser u
            WHERE 'something' = ANY(u.featureFlags)
        ");

I got Expected Doctrine\ORM\Query\Lexer::T_SELECT, got 'u'

whereas if i type directly in postgresql

SELECT * FROM end_users WHERE 'something' = ANY (feature_flags)

it works

symfony / doctrine configuration help

None of the functions are registered. I'm stuck on how to configure this correctly.

        $queryBuilder = $instanceRepository->createQueryBuilder('i')
            ->andWhere('CONTAINS(i.attributes, :attr)')
            ->setParameter('attr', 'year');

throw


assert($token !== null)

Range types (mainly tstzrange, tsrange and daterange)

Awesome project!

When you have time, could you add support for the built-in range types in Postgres?
https://www.postgresql.org/docs/current/rangetypes.html

For the date based ranges, it would be really cool if it already maps the values to a DatePeriod class:
https://www.php.net/manual/en/class.dateperiod.php

jsonb type is interpreted as json in doctrine migrations

When creating an entity with a jsonb field and generating a migration with doctrine migrations, the migration defines the field as JSON, which forces me to fix that mannually each time I create a migration.

Do you have a way to fix this ?

Add requiresSQLCommentHint to custom doctrine types

Hi! Can you add overrided requiresSQLCommentHint() into all types?

User Deprecated: The type "text[]" was implicitly marked as commented due to the configuration. This is deprecated and will be removed in DoctrineBundle 2.0. Either set the "commented" attribute in the configuration to "false" or mark the type as commented in "MartinGeorgiev\Doctrine\DBAL\Types\TextArray::requiresSQLCommentHint().

arrays in Symfony Forms

I'm trying to implement a data transformer to save integer[] values in a postgres database using the bundle.

// some entity with tags
/**
 * @ORM\Column(type="integer[]", nullable=true)
 */
    protected $tagIds;

    public function getTagIds(): array
    {
        return $this->tagIds ?: [];
    }

    public function setTagIds($tagIds): self
    {
        $this->tagIds = $tagIds;
        return $this;
    }

I'd like to add these tags Id via a custom form type (for now, just a string of comma-delimited integers, for this issue report)

        $builder
            ->add('tagIds', TagIdsType::class, [
                'required' => false,
                'help' => 'comma-delimited tag ids',
            ])
        ;

The TagIdsType, following the instructions from https://symfony.com/doc/current/form/create_custom_field_type.html

class TagIdsType extends AbstractType
{
    public function buildForm(FormBuilderInterface $builder, array $options)
    {
        $builder
            ->add('tag_string', TextareaType::class, [
                'help' => 'comma-delimited tag ids',
            ])
        ;
        $builder->get('tag_string')->addModelTransformer(new TagIdsTransformer());

Finally, the TagIdsTransformer:

// TagIdsTransformer.php
namespace App\Form\DataTransformer;

use App\Entity\TaggableInterface;
use Symfony\Component\Form\DataTransformerInterface;

class TagIdsTransformer implements DataTransformerInterface

{
    public function transform($taggableEntity)
    {
        /** @var TaggableInterface $taggableEntity */
        return $taggableEntity ? implode(',', $taggableEntity->getTagIds()): '';
    }

    public function reverseTransform($tagsAsString)
    {
        dump($tagsAsString);
        $tagsAsArray =  array_map(fn ($idAsString) => (int)$idAsString, explode(',', $tagsAsString));
        dump($tagsAsArray);
        return $tagsAsArray;
    }
}

When I fill out the form with a string of comma-delimited integers, it fails with the message

One or more of items given doesn't look like valid. {"tag_string":[3]} array

The transform and reverseTransform appear to be returning the right values, so I'm not sure where the error is. Obviously, I don't want tag_string, it's not part of the entity.

I'm posting here because if someone provides a solution, I can submit a PR to the documentation, as I imagine this can help others as well. Thanks.

Cannot use object of type Doctrine\Common\Lexer\Token as array

Hi,

Juste upgraded to 8.3 and the UNACCENT function throws an error :

Error:
Cannot use object of type Doctrine\Common\Lexer\Token as array

  at vendor/martin-georgiev/postgresql-for-doctrine/src/MartinGeorgiev/Doctrine/ORM/Query/AST/Functions/BaseVariadicFunction.php:24
  at MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\BaseVariadicFunction->feedParserWithNodes(object(Parser))
     (vendor/martin-georgiev/postgresql-for-doctrine/src/MartinGeorgiev/Doctrine/ORM/Query/AST/Functions/BaseFunction.php:50)
  at MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\BaseFunction->parse(object(Parser))
     (vendor/doctrine/orm/src/Query/Parser.php:3662)
  at Doctrine\ORM\Query\Parser->CustomFunctionsReturningStrings()
     (vendor/doctrine/orm/src/Query/Parser.php:3526)
  at Doctrine\ORM\Query\Parser->CustomFunctionDeclaration()
     (vendor/doctrine/orm/src/Query/Parser.php:3491)
  at Doctrine\ORM\Query\Parser->FunctionDeclaration()
     (vendor/doctrine/orm/src/Query/Parser.php:3017)
  at Doctrine\ORM\Query\Parser->StringPrimary()
     (vendor/doctrine/orm/src/Query/Parser.php:2993)
  at Doctrine\ORM\Query\Parser->StringExpression()
     (vendor/doctrine/orm/src/Query/Parser.php:3299)
  at Doctrine\ORM\Query\Parser->LikeExpression()
     (vendor/doctrine/orm/src/Query/Parser.php:2653)
  at Doctrine\ORM\Query\Parser->SimpleConditionalExpression()
     (vendor/doctrine/orm/src/Query/Parser.php:2535)
  at Doctrine\ORM\Query\Parser->ConditionalPrimary()
     (vendor/doctrine/orm/src/Query/Parser.php:2514)
  at Doctrine\ORM\Query\Parser->ConditionalFactor()
     (vendor/doctrine/orm/src/Query/Parser.php:2482)
  at Doctrine\ORM\Query\Parser->ConditionalTerm()
     (vendor/doctrine/orm/src/Query/Parser.php:2457)
  at Doctrine\ORM\Query\Parser->ConditionalExpression()
     (vendor/doctrine/orm/src/Query/Parser.php:2556)
  at Doctrine\ORM\Query\Parser->ConditionalPrimary()
     (vendor/doctrine/orm/src/Query/Parser.php:2514)
  at Doctrine\ORM\Query\Parser->ConditionalFactor()
     (vendor/doctrine/orm/src/Query/Parser.php:2482)
  at Doctrine\ORM\Query\Parser->ConditionalTerm()
     (vendor/doctrine/orm/src/Query/Parser.php:2457)
  at Doctrine\ORM\Query\Parser->ConditionalExpression()
     (vendor/doctrine/orm/src/Query/Parser.php:1392)
  at Doctrine\ORM\Query\Parser->WhereClause()
     (vendor/doctrine/orm/src/Query/Parser.php:883)
  at Doctrine\ORM\Query\Parser->SelectStatement()
     (vendor/doctrine/orm/src/Query/Parser.php:850)
  at Doctrine\ORM\Query\Parser->QueryLanguage()
     (vendor/doctrine/orm/src/Query/Parser.php:257)
  at Doctrine\ORM\Query\Parser->getAST()
     (vendor/doctrine/orm/src/Query/Parser.php:357)
  at Doctrine\ORM\Query\Parser->parse()
     (vendor/doctrine/orm/src/Tools/Pagination/Paginator.php:258)
  at Doctrine\ORM\Tools\Pagination\Paginator->unbindUnusedQueryParams(object(Query))
     (vendor/doctrine/orm/src/Tools/Pagination/Paginator.php:247)
  at Doctrine\ORM\Tools\Pagination\Paginator->getCountQuery()
     (vendor/doctrine/orm/src/Tools/Pagination/Paginator.php:120)
  at Doctrine\ORM\Tools\Pagination\Paginator->count()
     (vendor/pagerfanta/pagerfanta/lib/Adapter/Doctrine/ORM/QueryAdapter.php:56)
  at Pagerfanta\Doctrine\ORM\QueryAdapter->getNbResults()
     (vendor/pagerfanta/pagerfanta/lib/Core/Pagerfanta.php:371)
  at Pagerfanta\Pagerfanta->getNbResults()
     (vendor/twig/twig/src/Extension/CoreExtension.php:1570)
  at twig_get_attribute(object(Environment), object(Source), object(Pagerfanta), 'nbResults', array(), 'any', false, false, false, 35)
     (var/cache/dev/twig/36/3692ad3c26ebc916598237062d126a3e.php:182)
  at __TwigTemplate_d01b6cdd1d91d228c1eb35c184bd8f6b->block_content_title('r', 'S')
     (vendor/twig/twig/src/Template.php:182)
  at Twig\Template->displayBlock('r', 'S')
     (vendor/twig/twig/src/Template.php:254)
  at Twig\Template->renderBlock('r', 'S')
     (var/cache/dev/twig/95/957352b11b80bb752c2b6d5f52ef7771.php:111)
  at __TwigTemplate_05226f51c79b116598121e70c2b5a8a9->block_page_title('r', 'S')
     (vendor/twig/twig/src/Template.php:182)
  at Twig\Template->displayBlock('r', 'S')
     (var/cache/dev/twig/c4/c46260aeea041c0d598d5b83238480a1.php:91)
  at __TwigTemplate_59c5786fd79890b459ce46ecc14d10ac->doDisplay('r', 'S')
     (vendor/twig/twig/src/Template.php:405)
  at Twig\Template->displayWithErrorHandling('r', 'S')
     (vendor/twig/twig/src/Template.php:378)
  at Twig\Template->display('r', 'S')
     (var/cache/dev/twig/b4/b42627e01864d91bf7ee7d2710c15e7e.php:56)
  at __TwigTemplate_4f33419ce4919df4ca1b546eb002cfdc->doDisplay('r', 'S')
     (vendor/twig/twig/src/Template.php:405)
  at Twig\Template->displayWithErrorHandling('r', 'S')
     (vendor/twig/twig/src/Template.php:378)
  at Twig\Template->display('r', 'S')
     (var/cache/dev/twig/95/957352b11b80bb752c2b6d5f52ef7771.php:53)
  at __TwigTemplate_05226f51c79b116598121e70c2b5a8a9->doDisplay('r', 'S')
     (vendor/twig/twig/src/Template.php:405)
  at Twig\Template->displayWithErrorHandling('r', 'S')
     (vendor/twig/twig/src/Template.php:378)
  at Twig\Template->display('r', 'S')
     (var/cache/dev/twig/36/3692ad3c26ebc916598237062d126a3e.php:99)
  at __TwigTemplate_d01b6cdd1d91d228c1eb35c184bd8f6b->doDisplay('r', 'S')
     (vendor/twig/twig/src/Template.php:405)
  at Twig\Template->displayWithErrorHandling(array('paginator' => object(Pagerfanta), 'fields' => array('createdAt' => array('css_class' => '', 'format' => 'd/m/Y H:i', 'help' => null, 'label' => 'Date création', 'type' => 'datetime', 'fieldType' => 'datetime', 'dataType' => 'datetime', 'virtual' => false, 'sortable' => true, 'template' => '@EasyAdmin/default/field_datetime.html.twig', 'type_options' => array('required' => false), 'form_group' => null, 'permission' => null, 'prepend_html' => null, 'append_html' => null, 'fieldName' => 'createdAt', 'scale' => null, 'length' => null, 'unique' => false, 'nullable' => true, 'precision' => null, 'columnName' => 'created_at', 'property' => 'createdAt'), 'getCommercialNameAdmin' => array('css_class' => '', 'format' => null, 'help' => null, 'label' => 'Nom Commercial', 'type' => 'text', 'fieldType' => null, 'dataType' => 'text', 'virtual' => true, 'sortable' => false, 'template' => 'Admin/templates/details.html.twig', 'type_options' => array('required' => false), 'form_group' => null, 'permission' => null, 'prepend_html' => null, 'append_html' => null, 'columnName' => 'getCommercialNameAdmin', 'fieldName' => 'getCommercialNameAdmin', 'id' => false, 'length' => null, 'nullable' => false, 'precision' => 0, 'scale' => 0, 'unique' => false, 'property' => 'getCommercialNameAdmin'), 'type' => array('css_class' => '', 'format' => null, 'help' => null, 'label' => 'Domaine activité', 'type' => 'string', 'fieldType' => 'text', 'dataType' => 'string', 'virtual' => false, 'sortable' => true, 'template' => 'Admin/templates/categories.html.twig', 'type_options' => array('required' => false), 'form_group' => null, 'permission' => null, 'prepend_html' => null, 'append_html' => null, 'fieldName' => 'type', 'scale' => null, 'length' => 255, 'unique' => false, 'nullable' => true, 'precision' => null, 'columnName' => 'type', 'property' => 'type'), 'email' => array('css_class' => '', 'format' => null, 'help' => null, 'label' => 'Email', 'type' => 'string', 'fieldType' => 'text', 'dataType' => 'string', 'virtual' => false, 'sortable' => true, 'template' => '@EasyAdmin/default/field_string.html.twig', 'type_options' => array('required' => false), 'form_group' => null, 'permission' => null, 'prepend_html' => null, 'append_html' => null, 'fieldName' => 'email', 'scale' => null, 'length' => 255, 'unique' => false, 'nullable' => true, 'precision' => null, 'columnName' => 'email', 'property' => 'email'), 'phone_number' => array('css_class' => '', 'format' => null, 'help' => null, 'label' => 'Numéro téléphone', 'type' => 'string', 'fieldType' => 'text', 'dataType' => 'string', 'virtual' => false, 'sortable' => true, 'template' => '@EasyAdmin/default/field_string.html.twig', 'type_options' => array('required' => false), 'form_group' => null, 'permission' => null, 'prepend_html' => null, 'append_html' => null, 'fieldName' => 'phone_number', 'scale' => null, 'length' => 255, 'unique' => false, 'nullable' => true, 'precision' => null, 'columnName' => 'phone_number', 'property' => 'phone_number'), 'getFullAddress' => array('css_class' => '', 'format' => null, 'help' => null, 'label' => 'Adresse', 'type' => 'text', 'fieldType' => null, 'dataType' => 'text', 'virtual' => true, 'sortable' => false, 'template' => '@EasyAdmin/default/field_text.html.twig', 'type_options' => array('required' => false), 'form_group' => null, 'permission' => null, 'prepend_html' => null, 'append_html' => null, 'columnName' => 'getFullAddress', 'fieldName' => 'getFullAddress', 'id' => false, 'length' => null, 'nullable' => false, 'precision' => 0, 'scale' => 0, 'unique' => false, 'property' => 'getFullAddress'), 'getOwner.fullname' => array('css_class' => '', 'format' => null, 'help' => null, 'label' => 'Pro', 'type' => 'text', 'fieldType' => null, 'dataType' => 'text', 'virtual' => true, 'sortable' => false, 'template' => '@EasyAdmin/default/field_text.html.twig', 'type_options' => array('required' => false), 'form_group' => null, 'permission' => null, 'prepend_html' => null, 'append_html' => null, 'columnName' => 'getOwner.fullname', 'fieldName' => 'getOwner.fullname', 'id' => false, 'length' => null, 'nullable' => false, 'precision' => 0, 'scale' => 0, 'unique' => false, 'property' => 'getOwner.fullname'), 'total_open_offers' => array('css_class' => '', 'format' => null, 'help' => null, 'label' => 'Total offre ouverte', 'type' => 'integer', 'fieldType' => 'integer', 'dataType' => 'integer', 'virtual' => false, 'sortable' => true, 'template' => '@EasyAdmin/default/field_integer.html.twig', 'type_options' => array('required' => false), 'form_group' => null, 'permission' => null, 'prepend_html' => null, 'append_html' => null, 'fieldName' => 'total_open_offers', 'scale' => null, 'length' => null, 'unique' => false, 'nullable' => true, 'precision' => null, 'options' => array('default' => 0), 'columnName' => 'total_open_offers', 'property' => 'total_open_offers'), 'getAboAdmin' => array('css_class' => '', 'format' => null, 'help' => null, 'label' => 'Abo.', 'type' => 'text', 'fieldType' => null, 'dataType' => 'text', 'virtual' => true, 'sortable' => false, 'template' => '@EasyAdmin/default/field_text.html.twig', 'type_options' => array('required' => false), 'form_group' => null, 'permission' => null, 'prepend_html' => null, 'append_html' => null, 'columnName' => 'getAboAdmin', 'fieldName' => 'getAboAdmin', 'id' => false, 'length' => null, 'nullable' => false, 'precision' => 0, 'scale' => 0, 'unique' => false, 'property' => 'getAboAdmin'), 'completion_rate' => array('css_class' => '', 'format' => null, 'help' => null, 'label' => 'Completion %', 'type' => 'integer', 'fieldType' => 'integer', 'dataType' => 'integer', 'virtual' => false, 'sortable' => true, 'template' => '@EasyAdmin/default/field_integer.html.twig', 'type_options' => array('required' => false), 'form_group' => null, 'permission' => null, 'prepend_html' => null, 'append_html' => null, 'fieldName' => 'completion_rate', 'scale' => null, 'length' => 11, 'unique' => false, 'nullable' => true, 'precision' => null, 'options' => array('default' => '0'), 'columnName' => 'completion_rate', 'property' => 'completion_rate')), 'batch_form' => object(FormView), 'delete_form_template' => object(FormView), 'app' => object(AppVariable), 'pathToWeb' => '/var/www/html/../web', 'website_url' => 'https://dev.myworks.fr', 'IS_PROD' => 'false', 'requestExtFilters' => array(), '_request_parameters' => array('ext_filters' => array())), array('body_id' => array(object(__TwigTemplate_d01b6cdd1d91d228c1eb35c184bd8f6b), 'block_body_id'), 'body_class' => array(object(__TwigTemplate_d01b6cdd1d91d228c1eb35c184bd8f6b), 'block_body_class'), 'content_title' => array(object(__TwigTemplate_d01b6cdd1d91d228c1eb35c184bd8f6b), 'block_content_title'), 'global_actions' => array(object(__TwigTemplate_b823a6a38f939e4ee8bf01043e4c187e), 'block_global_actions'), 'search_action' => array(object(__TwigTemplate_a17047e7bbe470e185909a38f376a686), 'block_search_action'), 'search_form' => array(object(__TwigTemplate_a17047e7bbe470e185909a38f376a686), 'block_search_form'), 'filters_action' => array(object(__TwigTemplate_d01b6cdd1d91d228c1eb35c184bd8f6b), 'block_filters_action'), 'new_action' => array(object(__TwigTemplate_a17047e7bbe470e185909a38f376a686), 'block_new_action'), 'batch_actions' => array(object(__TwigTemplate_d01b6cdd1d91d228c1eb35c184bd8f6b), 'block_batch_actions'), 'content_header' => array(object(__TwigTemplate_d01b6cdd1d91d228c1eb35c184bd8f6b), 'block_content_header'), 'main' => array(object(__TwigTemplate_b823a6a38f939e4ee8bf01043e4c187e), 'block_main'), 'table_head' => array(object(__TwigTemplate_d01b6cdd1d91d228c1eb35c184bd8f6b), 'block_table_head'), 'table_body' => array(object(__TwigTemplate_d01b6cdd1d91d228c1eb35c184bd8f6b), 'block_table_body'), 'item_actions' => array(object(__TwigTemplate_a17047e7bbe470e185909a38f376a686), 'block_item_actions'), 'delete_form' => array(object(__TwigTemplate_d01b6cdd1d91d228c1eb35c184bd8f6b), 'block_delete_form'), 'content_footer' => array(object(__TwigTemplate_d01b6cdd1d91d228c1eb35c184bd8f6b), 'block_content_footer'), 'paginator' => array(object(__TwigTemplate_d01b6cdd1d91d228c1eb35c184bd8f6b), 'block_paginator'), 'body_javascript' => array(object(__TwigTemplate_a17047e7bbe470e185909a38f376a686), 'block_body_javascript'), 'request_parameters_as_hidden' => array(object(__TwigTemplate_a17047e7bbe470e185909a38f376a686), 'block_request_parameters_as_hidden'), 'list_form_filters' => array(object(__TwigTemplate_a17047e7bbe470e185909a38f376a686), 'block_list_form_filters'), 'list_form_filters_fields' => array(object(__TwigTemplate_a17047e7bbe470e185909a38f376a686), 'block_list_form_filters_fields')))
     (vendor/twig/twig/src/Template.php:378)
..
     (vendor/easycorp/easyadmin-bundle/src/Controller/AdminControllerTrait.php:416)
  at EasyCorp\Bundle\EasyAdminBundle\Controller\EasyAdminController->searchAction()
     (vendor/easycorp/easyadmin-bundle/src/Controller/AdminControllerTrait.php:912)
  at EasyCorp\Bundle\EasyAdminBundle\Controller\EasyAdminController->executeDynamicMethod('search<EntityName>Action')
     (vendor/easycorp/easyadmin-bundle/src/Controller/AdminControllerTrait.php:86)
  at EasyCorp\Bundle\EasyAdminBundle\Controller\EasyAdminController->indexAction(object(Request))
     (src/Controller/Admin/AdminController.php:129)
  at AppBundle\Controller\Admin\AdminController->indexAction(object(Request))
     (vendor/symfony/http-kernel/HttpKernel.php:163)
  at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), 1)
     (vendor/symfony/http-kernel/HttpKernel.php:75)
  at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), 1, true)
     (vendor/symfony/http-kernel/Kernel.php:202)
  at Symfony\Component\HttpKernel\Kernel->handle(object(Request), 1, true)
     (vendor/bref/symfony-bridge/src/BrefKernel.php:48)
  at Bref\SymfonyBridge\BrefKernel->handle(object(Request))
     (vendor/symfony/runtime/Runner/Symfony/HttpKernelRunner.php:35)
  at Symfony\Component\Runtime\Runner\Symfony\HttpKernelRunner->run()
     (vendor/autoload_runtime.php:35)
  at require_once('/var/www/html/vendor/autoload_runtime.php')
     (public/index.php:7)

Cant install 2.1.1 version

composer update martin-georgiev/postgresql-for-doctrine:2.1.1 --dry-run
Loading composer repositories with package information
Updating dependencies
Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - calcurates/calcurates is present at version dev-master and cannot be modif
ied by Composer
    - martin-georgiev/postgresql-for-doctrine[v2.1.1] cannot be installed as tha
t would require removing calcurates/calcurates[dev-master]. They both replace sy
mfony/polyfill-php72 and thus cannot coexist.
    - Root composer.json requires martin-georgiev/postgresql-for-doctrine ^2.1 -
> satisfiable by martin-georgiev/postgresql-for-doctrine[v2.1.1].

see symfony/flex#839 and this commit 57414d7
the package should not use the replace section with symfony/polyfill*.

problem with null

this line would be a problem with array entries that are null, which is valid for the postgres arrays

postgresql-for-doctrine/src/MartinGeorgiev/Doctrine/DBAL/Types/SmallIntArray.php

Line 32 in 6267c56

return (int) $item;

Add support for DBAL v4

See the release statement.

Current version can't be required by a default symfony webapp installation.

Jsonb type limited convert

JsonB type has now limitation on convertToPhpValue function. You can't convert to some custom php classes now like this:

public function convertToPHPValue($value, AbstractPlatform $platform): Currency

Any reason behind limiting convert to only simple php types?

Arithmetic expression inside greatest() leads to infinite parsing loop

I noticed that using an arithmetic operation inside greatest() causes parser to enter an infinite loop.

My doctrine/dbal is at version 2.12.1.

Consider this working example:

$result = $this->doctrine
    ->getManagerForClass(Foo::class)
    ->createQueryBuilder()

      // Here e.g. "sqrt(30) * 100" triggers infinite loop but plain "sqrt(30)" will work just fine.
      ->select('
          greatest(
              sqrt(30) * 100,
              sqrt(11) * 150
          )
          AS score
      ')
      ->from(Foo::class, 'a')
      ->getQuery()
      ->getResult()
      ;

var_dump($result);

My understanding of the parser is limited but I managed to make this behavior stop by overriding $commonNodeMapping in Greatest.php. I have no idea what this will break in exchange...

class Greatest extends BaseVariadicFunction
{
    // Default seems to be 'StringPrimary'.
    protected $commonNodeMapping = 'SimpleArithmeticExpression';

    protected function customiseFunction(): void
    {
        $this->setFunctionPrototype('greatest(%s)');
    }
}

Error: Class 'TO_TSQUERY' is not defined

@martin-georgiev
Hi, could you help me please transfrom this is native query to doctrine query builder or dql ? Because I faced with error when I try to do it myself :(

line 0, col 43 near 'TO_TSQUERY('Ba:*')': Error: Class 'TO_TSQUERY' is not defined.

SELECT                         
products_alias.id,
ts_rank_cd(to_tsvector('english',coalesce(name,'')|| ' '), query_search) AS rank

FROM shop products_alias 

JOIN to_tsquery('Ba:*') query_search
ON to_tsvector('english',coalesce(name,'')|| ' ') @@ query_search

GROUP BY id, query_search.query_search ORDER BY rank DESC
LIMIT 10
OFFSET 0;

I just try did query builder but faced with error

        $qb = $this->createQueryBuilder('s');

        $qb
            ->select('s')
            ->join(
                'TO_TSQUERY(\'Ba\')',
                'query_search',
                Join::ON,
                'TO_TSVECTOR(english,coalesce(name,\'\')||\' \')) TSMATCH query_search'
            )
            ->groupBy('query_search')
            ->setFirstResult(0)
            ->setMaxResults(10);

in Parser dql looks like this

SELECT s FROM App\Entity\Shop s INNER JOIN TO_TSQUERY('Ba:*') query_search ON TO_TSVECTOR(english,coalesce(name,'')||' ')) TSMATCH query_search GROUP BY query_search

ans the same problem with DQL

line 0, col 86 near 'TO_TSQUERY('test')': Error: Class 'TO_TSQUERY' is not defined.

        $query = $this->getEntityManager()
            ->createQuery('
                SELECT a FROM
                App\Entity\Shop a
                JOIN TO_TSQUERY(\'test\') query                
                ON TSMATCH(TO_TSVECTOR(\'english\',coalesce(name,\'\')|| \' \'), query_search)
            ');

UPDATE

I debuggint it and found something, in WHERE block it works

        $query = $this->getEntityManager()
            ->createQuery('
                SELECT a FROM
                App\Entity\Shop a
                WHERE TSMATCH(TO_TSVECTOR(coalesce(a.name,\'\')), TO_TSQUERY(\'test\')) = TRUE
            ');

but parser not parse it, dql looks like this
SELECT s FROM App\Entity\Shop s
and sql like this
SELECT s0_.id AS id_0, s0_.name AS name_1, s0_.created_at AS created_at_2, s0_.updated_at AS updated_at_3 FROM shop s0_

Could you help me please, maybe I missed something simple ?

ARRAY with multiple arguments

I'd like to use this kind of postgres code Array_to_string(ARRAY['a', 'b', 'c'], ';') which gives a;b;c

but this does not parse in dql :

ARRAY_TO_STRING( ARRAY( 'a', 'b', 'c' ), ';')
-> [Syntax Error] line 0, col 70: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got ','

Unknown database type text[] requested, Doctrine\DBAL\Platforms\PostgreSQL100Platform may not support it.

I was already using this extension for some functions and tried using the text[] type but doctrine thrown this exception when I tried to use it in an entity.

I was able to fix that by extending TextArray and adding this method:

    /**
     * @return array<string>
     */
    public function getMappedDatabaseTypes(AbstractPlatform $platform): array
    {
        return [self::TYPE_NAME];
    }

is there something wrong with my config ? or maybe that’s something caused by the latest version of doctrine ? if not do you think this method should be added to BaseType ?

doctrine/dbal: 2.10.2
doctrine/orm: 2.7.3
doctrine/doctrine-bundle: 2.1.0
symfony: 5.1.*

Bug: stored JSON value may be integer/float/string

We are storing variable type of data in JSONB field. It can be object, integer, float or string. So we have an issue with Jsonb and primitive types. I think array type must be removed from these methods.

https://github.com/martin-georgiev/postgresql-for-doctrine/blob/master/src/MartinGeorgiev/Doctrine/DBAL/Types/Jsonb.php#L45
https://github.com/martin-georgiev/postgresql-for-doctrine/blob/master/src/MartinGeorgiev/Doctrine/DBAL/Types/JsonTransformer.php#L33

heavy composer package with uneeded files

I noticed this package ends up being on the heavy side as it also ships development tools such as two versions of local-php-security-checker that gets installed by composer

Packages usually define excluded files from the packages, leaving only what is needed for runtime, to avoid wasting network/disk resources :

with .gitattributes, e.g. see doctrine: https://github.com/doctrine/dbal/blob/3.3.x/.gitattributes
with composer archive exclusion: https://getcomposer.org/doc/04-schema.md#archive

Thanks

Jsonb::convertToPHPValue(): Return value must be of type array|string|int|float|null, bool returned

after upgrade to https://github.com/martin-georgiev/postgresql-for-doctrine/releases/tag/v2.0.0

Uncaught PHP Exception TypeError: "MartinGeorgiev\Doctrine\DBAL\Types\Jsonb::convertToPHPValue(): Return value must be of type array|string|int|float|null, bool returned

JsonTransformer::transformToPostgresJson may return bool value.

getResults() returns arrays instead of objects

Normally queries return a doctrine Collection, so you can iterate through a list of entities, but when I add a where clause, only arrays are returned

        $queryBuilder = $instanceRepository->createQueryBuilder('i')
            ->where('JSON_GET_OBJECT(i.attributes, :attr) = :attrValue')
            ->setParameter('attr', sprintf('{%s}', $field->getCode()))
            ->setParameter('attrValue', $value);
        foreach ($queryBuilder->getQuery()->getResult() as $result) {
            dump($result);
        }

If I get rid of the where and set parameter methods, $result is an entity, but when them, the result is an associative array. Is there any way to have the result returned as an entity?

float[] type

Not really an issue, but a suggestion, I've implemented FloatArray locally for a project:

class FloatArray extends BaseArray
{
    protected const TYPE_NAME = 'float[]';

    /**
     * @param mixed $item
     */
    public function isValidArrayItemForDatabase($item): bool
    {
        return (\is_float($item) || \is_int($item) || \is_string($item))
            && preg_match('/^-?[0-9]+(?:\.[0-9]+)?$/', (string) $item);
    }

    /**
     * @param float|int|string|null $item Whole number
     */
    public function transformArrayItemForPHP($item): ?float
    {
        if (null === $item) {
            return null;
        }

        $isInvalidPHPInt = false === preg_match('/^-?[0-9]+(?:\.[0-9]+)?$/', (string) $item);
        if ($isInvalidPHPInt) {
            throw new ConversionException(sprintf('Given value of %s content cannot be transformed to valid PHP float.', var_export($item, true)));
        }

        return (float) $item;
    }
}

In that case, I do not verify the number of digits (https://www.postgresql.org/docs/current/datatype-numeric.html) depending on real (float4) or double precision (float8).
Doctrine natively not handle float4 (real) but only floa8(double precision), PHP have a precision of 14 digits. That's why I do not do the check (but in external library like here, seem important to do, if the user change the precision to 16 for exemple).

Name	Storage Size	Description	Range
real	4 bytes	variable-precision, inexact	6 decimal digits precision
double precision	8 bytes	variable-precision, inexact	15 decimal digits precision

It seems there also have a min/max value like done with Integer array:

On most platforms, the real type has a range of at least 1E-37 to 1E+37 with a precision of at least 6 decimal digits. The double precision type typically has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits. Values that are too large or too small will cause an error. Rounding might take place if the precision of an input number is too high. Numbers too close to zero that are not representable as distinct from zero will cause an underflow error.

Check the min/max is not really difficult I think, do the same like done with IntegerArray
Check the number of digits seen a more little difficult: convert to string, count number chars after the ".", and if scientific notation use the "E" ?

jsonb_build_object support?

Hello! Do you have support for jsonb_build_object function?

InArray use ANY instead of ANY_OF

MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\InArray use MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Any by calling ANY as function name.

The documentation examples use ANY_OF as alias for the Any function, triggering a type error when using InArray.

Either detect the alias used for Any class (prefered), or use ANY_OF (breaking change for code already using InArray).

Thank you for your work.

array_agg doesn't support ORDER BY

When I use the function like this array_agg(d.startDate ORDER BY d.startDate DESC), I get en error
"[Syntax Error] line 0, col 64: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got 'ORDER'"
Please fix it.

Can't use this package with PHP 7.4

$ composer require martin-georgiev/postgresql-for-doctrine

Using version ^1.5 for martin-georgiev/postgresql-for-doctrine
...

Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - Root composer.json requires php ^8.0 but your php version (7.4.12) does not satisfy that requirement.

I see that in your composer.json is specified:

"php": "^7.2|^8.0",

I think there should be a double pipe. Proof. And the same on stackoverflow, see the comment there.

Enum type

Hi, I have a question. Is it possible to add enum type? I really like your work, it contains most missing features of postgres database. Only one thing missing and that's enum. It's just idea. It's could save me another library or some coding, if u add that to this lib.

Invalid integration doc for symfony

Hi guys!

In your doc, described integration with symfony you write:

doctrine:
    orm:
        entity_managers:
            your_connection:
                dql:
                    string_functions:
                        # alternative implementation of ALL() and ANY() where subquery is not required, useful for arrays
                        ALL_OF: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\All
                        ANY_OF: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Any

But this not working for symfony 4.*

I solved the problem via:

doctrine:
    orm:
        dql:
            string_functions:
                PG_ALL: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\All
                PG_ANY: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Any

Check this, maybe for different connection need other config, but your config not working with default connection.

Add aggregate functions

https://www.postgresql.org/docs/current/functions-aggregate.html

array_agg
json_agg
jsonb_agg
json_object_agg
jsonb_object_agg
string_agg
xmlagg

Dependency Dashboard

This issue lists Renovate updates and detected dependencies. Read the Dependency Dashboard docs to learn more.

Other Branches

These updates are pending. To force PRs open, click the checkbox below.

Lock file maintenance

Open

These updates have all been created already. Click a checkbox below to force a retry/rebase of any.

Update Composer QA tooling dependencies (friendsofphp/php-cs-fixer, phpstan/phpstan, phpunit/phpunit, rector/rector)

Ignored or Blocked

These are blocked by an existing closed PR and will not be recreated unless you click a checkbox below.

Update dependency phpunit/phpunit to v11

Detected dependencies

composer

composer.json

php ^8.1

doctrine/dbal ~2.10||~3.0||~4.0

doctrine/orm ~2.14||~3.0

ekino/phpstan-banned-code ^1.0

friendsofphp/php-cs-fixer ^3.57.1

php-coveralls/php-coveralls ^2.7.0

phpstan/phpstan ^1.11.1

phpstan/phpstan-phpunit ^1.4.0

phpunit/phpunit ^10.5.20

qossmic/deptrac ^2.0.0-alpha

rector/rector ^1.0.5

symfony/cache ^6.4||^7.0

github-actions

.github/workflows/auto-request-review.yml

necojackarc/auto-request-review v0.13.0

.github/workflows/ci.yml

actions/checkout v4

shivammathur/setup-php v2

actions/cache v4

Check this box to trigger a request for Renovate to run again on this repository

Wanted to add your package in PHP 8.0.12 project, but PHP version requirement is not met

I wanted to use your package in a project using PHP version 8.0.12.

Your package needs at least PHP 7.2 so I thought it would work, but composer stops the process and give me this :

Problem 1
- martin-georgiev/postgresql-for-doctrine v1.5.0 requires php ^7.2 -> your php version (8.0.12) does not satisfy that requirement.
- martin-georgiev/postgresql-for-doctrine[v1.5.1, ..., v1.5.2] require doctrine/dbal ~2.5 -> found doctrine/dbal[v2.5.0, ..., 2.13.4] but the package is fixed to 3.1.3 (lock file version) by a partial update and that version does not match. Make sure you list it as an argument for the update command.
- Root composer.json requires martin-georgiev/postgresql-for-doctrine ^1.5 -> satisfiable by martin-georgiev/postgresql-for-doctrine[v1.5.0, v1.5.1, v1.5.2].

Do you know why does the PHP requirements are not me ?

Thanks for letting me know, and fix bug if it is one

Transformation to PostgresTextArray does not escape slash

\MartinGeorgiev\Utils\DataStructure::transformPHPArrayToPostgresTextArray does not escape slash in a string. E.g.

        print_r(\MartinGeorgiev\Utils\DataStructure::transformPHPArrayToPostgresTextArray(
            ['"Quotes"', 'Slashes\\', "'SingleQuotes", 'Normal'])
        );

Then PostgreSQL 12.4

select unnest('{"\"Quotes\"","Slashes\","''SingleQuotes","Normal"}'::varchar[]);
ERROR:  malformed array literal: "{"\"Quotes\"","Slashes\","'SingleQuotes","Normal"}"
LINE 1: select unnest('{"\"Quotes\"","Slashes\","''SingleQuotes","No...
                      ^
DETAIL:  Unexpected array element.

Please consider using

$escapedText = '"'.\addcslashes($text, '"\\').'"';

As this would return what's exected:

select unnest('{"\"Quotes\"","Slashes\\","''SingleQuotes","Normal"}'::varchar[]);
    unnest
---------------
 "Quotes"
 Slashes\
 'SingleQuotes
 Normal
(4 rows)

Postgres 14-specific JSON syntax

In reviewing the documentation for Postgres 14, I saw this:

PostgreSQL has supported manipulating JSON data since the release of PostgreSQL 9.2, though retrieval of values used a unique syntax. PostgreSQL 14 now lets you access JSON data using subscripts, e.g. a query like SELECT ('{ "postgres": { "release": 14 }}'::jsonb)['postgres']['release']; now works. This aligns PostgreSQL with syntax that is commonly recognized for retrieving information from JSON data. The subscripting framework added to PostgreSQL 14 can be generally extended to other nested data structures, and is also applied to the hstore data type in this release.

Range types, also first released in PostgreSQL 9.2, now have support for noncontiguous ranges through the introduction of the "multirange" data type. A multirange is an ordered list of ranges that are nonoverlapping, which lets developers write simpler queries for dealing with complex sequences of ranges. The range types native to PostgreSQL (dates, times, numbers) support multiranges, and other data types can be extended to use multirange support.

Are there parts of this bundle that alter the query syntax based on version? This is probably a minor enhancement that would only be relevant for debugging, but I'm curious if there's any benefit, beyond the syntax change, to leveraging this new feature from postgres 14.

JsonbArray returns malformed result

I have a field that has jsonb[] type. I want to add multidimensional array data to that field. But PostgreSQL returns an error. I tried to find what is wrong but i couldn't succeed.

You can reproduce with that data:

[
  [ 
    'id'=>5,
    'text'=>'test'
  ]
]

error:

  SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  malformed array literal: "{{"id":5,"test":"test"}}" 
DETAIL:  Unexpected array element.

There is a start point for finding what is wrong: https://dba.stackexchange.com/questions/54283/how-to-turn-json-array-into-postgres-array/54289#54289