boldtrn / jsonbbundle Goto Github PK

A bundle to extend the Doctrine2 functionality to the Postgresql type: Jsonb. It plays well with Symfony.

License: MIT License

PHP 100.00%

jsonbbundle's Introduction

JsonbBundle

Doctrine implemented the jsonb datatype with Doctrine DBAL 2.6. I recommend using the official Doctrine implementation. If you cannot upgrade feel free to use this bundle. It still works for me in my current production setting. I will upgrade to the doctrine implementation at some point in time, as well. Doctrine Mapping Matrix

This bundle extends Doctrine to use the jsonb datatype that ships with Postgresql 9.4. This bundle is fully compatible with Symfony, but you do not have to use Symfony (see the composer.json for dependencies). Please make sure you have Postgresql with a version of at least 9.4 installed before using this bundle. The Bundle allows to create Jsonb fields and use the @>,? and the #>> operator on the Jsonb field. Other Operations can be easily added.

I recently discovered the power of NativeQueries (http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html). Right now I only use NativeQueries when querying. An example is shown below.

Installation

Step 1: Download the Bundle

Open a command console, enter your project directory and execute the following command to download the latest stable version of this bundle:

$ composer require "boldtrn/jsonb-bundle:~1.1"

Step 2: Add the new Types and Functions to the Config

# config.yml
doctrine:
    dbal:
        types:
          jsonb: Boldtrn\JsonbBundle\Types\JsonbArrayType
        mapping_types:
          jsonb: jsonb
    orm:
        dql:
            string_functions:
                JSONB_AG:   Boldtrn\JsonbBundle\Query\JsonbAtGreater
                JSONB_HGG:  Boldtrn\JsonbBundle\Query\JsonbHashGreaterGreater
                JSONB_EX:   Boldtrn\JsonbBundle\Query\JsonbExistence

Note: There were people having issues with the above configuration. They had the following exception:

[Symfony\Component\Config\Definition\Exception\InvalidConfigurationException]  
  Unrecognized options "dql" under "doctrine.orm"

This was fixed by changing the dql part in the following (add the entity_managers between orm and dql):

doctrine:
    orm:
        entity_managers:
            dql:

Step 3: Create a Entity and Use the Jsonb Type

/**
 * @Entity
 */
class Test
{

    /**
     * @Id
     * @Column(type="string")
     * @GeneratedValue
     */
    public $id;

    /**
     * @Column(type="jsonb")
     *
     * Usually attrs is an array, depends on you
     *
     */
    public $attrs = array();

}

Step 4.1: Write a Repository Method using a NativeQuery

$q = $this
            ->entityManager
            ->createNativeQuery(
                "
        SELECT t.id, t.attrs
        FROM Test t
        WHERE t.attrs @> 'value'
        "
            , $rsm);

You only need to setup the $rsm ResultSetMapping according to the Doctrine documentation.

Step 4.2: Write a Repository Method that queries for the jsonb using the custom JSONB_FUNCTIONS

This example shows how to use the contains statement in a WHERE clause. The = TRUE is a workaround for Doctrine that needs an comparison operator in the WHERE clause.

$q = $this
            ->entityManager
            ->createQuery(
                "
        SELECT t
        FROM E:Test t
        WHERE JSONB_AG(t.attrs, 'value') = TRUE
        "
            );

This produces the following Query:

SELECT t0_.id AS id0, t0_.attrs AS attrs1 FROM Test t0_ WHERE (t0_.attrs @> 'value') = true

This example shows how to query for a value that is LIKE %d% The result could be data like:

 id |                 attrs                 
----+--------------------------------------
  4 | {"a": 1, "b": {"c": "abcdefg", "e": true}}

        $q = $this
            ->entityManager
            ->createQuery(
                "
        SELECT t
        FROM E:Test t
        WHERE JSONB_HGG(t.attrs , '{\"b\",\"c\"}') LIKE '%d%'
        "
            );

This produces the following Query:

SELECT t0_.id AS id0, t0_.attrs AS attrs1 FROM Test t0_ WHERE (t0_.attrs #>> '{\"object1\",\"object2\"}') LIKE '%a%'

Further Information

The ? operator is implemented by calling its function jsonb_exists(column_name, value) since Doctrine will consider it a parameter placeholder otherwise. The same must be done if you want to implement ?| and ?& operators, using jsonb_exists_any(column_name, value) and jsonb_exists_all(column_name, value) respectively

jsonbbundle's People

Contributors

Stargazers

Watchers

Forkers

valepu dunglas jezgomez jumblesale d-ph adrienbrault gencer symforge mktsolutions elyanory 01coding midieuminable pierreboissinot juanluisgarciaborrego

jsonbbundle's Issues

Error when excuting doctrine:schema:update

Hi I create my entitiy with jsonb field.
When I excute doctrine:schema:update --force I get the following error
[Doctrine\DBAL\DBALException] Unknown column type "jsonb" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType(). You can get a list of all the known types with \Doctrine\DBAL\Types\Type::getTypesMap(). If this error occurs during database introspection then you might have forgot to register all database types for a Doctrine Type. Use AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#g etMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot some mapping information.

Doctrine doesn't fill jsonb attributes of 2 or more classes who have fields with equal names

Hi, just noticed this bug.

2 classes with fields of same name, in one of them Doctrine couldn't bring the jsonb values:

/**
 * Entity.
 *
 * @ORM\Table(name="entity")
 * @ORM\Entity
 */
class Entity
{
    /**
     * @var array
     *
     * @ORM\Column(name="field", type="jsonb", nullable=true)
     */
    private $field;

    /**
     * Set field.
     *
     * @param jsonb $field
     *
     * @return Entity
     */
    public function setField($field)
    {
        $this->field= $field;

        return $this;
    }

    /**
     * Get field.
     *
     * @return jsonb
     */
    public function getField()
    {
        return $this->field;
    }
}

/**
 * SecondEntity.
 *
 * @ORM\Table(name="second_entity")
 * @ORM\Entity
 */
class SecondEntity
{
    /**
     * @var array
     *
     * @ORM\Column(name="field", type="jsonb", nullable=true)
     */
    private $field;

    /**
     * Set field.
     *
     * @param jsonb $field
     *
     * @return SecondEntity
     */
    public function setField($field)
    {
        $this->field= $field;

        return $this;
    }

    /**
     * Get field.
     *
     * @return jsonb
     */
    public function getField()
    {
        return $this->field;
    }
}

formTypes:

class EntityType extends AbstractType
{
    /**
     * @param FormBuilderInterface $builder
     * @param array $options
     */
    public function buildForm(FormBuilderInterface $builder, array $options)
    {
        parent::buildForm($builder, $options);

        $builder
            ->add('field', 'collectionfield', array(
                'label' => 'entity.name',
                'label_attr' => [
                    'add_button' => 'entity.add',
                    'delete_button' => 'entity.delete',
                ]
            ))
        ;
    }

    /**
     * @param OptionsResolver $resolver
     */
    public function configureOptions(OptionsResolver $resolver)
    {
        $resolver->setDefaults(array(
            'data_class' => 'AppBundle\Entity\Entity'
        ));
    }

    /**
     * @return string
     */
    public function getName()
    {
        return 'appbundle_entity';
    }

}

class SecondEntityType extends AbstractType
{
    /**
     * @param FormBuilderInterface $builder
     * @param array $options
     */
    public function buildForm(FormBuilderInterface $builder, array $options)
    {
        parent::buildForm($builder, $options);

        $builder
            ->add('field', 'collectionfield', array(
                'label' => 'secondEntity.name',
                'label_attr' => [
                    'add_button' => 'secondEntity.add',
                    'delete_button' => 'secondEntity.delete',
                ]
            ))
        ;
    }

    /**
     * @param OptionsResolver $resolver
     */
    public function configureOptions(OptionsResolver $resolver)
    {
        $resolver->setDefaults(array(
            'data_class' => 'AppBundle\Entity\SecondEntity'
        ));
    }

    /**
     * @return string
     */
    public function getName()
    {
        return 'appbundle_second_entity';
    }

}

views is just the {{ form_row(form.field) }} on each of them

it saves on the DB, but if you make a simple $entity = $em->getRepository('AppBundle:SecondEntity')->findOneBy(['id' => $id]); the jsonb fields return [ ](empty array)

i think is a jsonb managing related bug, has I'm using your bundle I thought of posting it here

my solution at the moment was renaming the fields, and it went ok.

can't implement JSONB "key exist" functions

Hi,
I'm trying to implement the JSONB functions that check for key existance http://www.postgresql.org/docs/9.4/static/functions-json.html ( ?, |?, &? ) using your code as starting point i made this:

class HasElement extends FunctionNode
{
    public $leftHandSide = null;
    public $rightHandSide = null;

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->leftHandSide = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->rightHandSide = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return '(' .
        $this->leftHandSide->dispatch($sqlWalker) . " ? " .
        $this->rightHandSide->dispatch($sqlWalker) .
        ')';
    }
}

But it doesn't work since doctrine translates the question mark as a parameter and i get this error

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "$2"
LINE 1: ...id = r1_.id WHERE r0_.file_id = $1 AND (r0_.attrs $2 $3)

I have tried escaping, replacing with unicode sequence, passing the question mark as parameter, but none of that worked. I have asked the question on Stackoverflow too http://stackoverflow.com/questions/31055658/custom-doctrine-dql-function-with-question-mark-being-converted-into-parameter but no answer
Do you think there is a way to implement these functions or should we forward the issue to the Doctrine team?

Get a value in JSON

Hi,

I wanted to know how i can get a value in JSON like : SELECT * FROM products WHERE (data->>'in_stock')::integer > 0;

For the moment i have this, but the result of query is empty :
$rsm = new ResultSetMapping();
$query = $this->_em->createNativeQuery("SELECT p.attributes FROM Product p WHERE (p.attributes->>'price')::float > ?;", $rsm);
$query->setParameter(1, 9);
$result = $query->getResult();

Thanks.

Missing LICENSE file

Hi, and thanks for this bundle!

According to the composer.json file, this project is under the MIT license, but the LICENSE file is missing (so GitHub cannot detect the license).

Doctrine\DBAL\Types\JsonArrayType is deprecated use JsonType instead

I had a warning regarding Boldtrn\JsonBundle\Types\JsonbArrayType when running tests saying that it extends JsonArrayType which is deprecated.

So here is a patch i hope will do the trick for this.

in boldtrn/jsonb-bundle/TypesJsonbArrayType.php :

class JsonbArrayType extends JsonType instead of class JsonbArrayType extends JsonArrayType

and en additional method to implement in the same class

    /**
     * {@inheritdoc}
     */
    public function requiresSQLCommentHint(AbstractPlatform $platform)
    {
        return true;
    }

Hope it will work for you :)