How to customize the keys and values in json_array in Doctrine?

Question

I'm using the json_array in my entity to store the transit cities (Symfony 3.1 project). An extract from my Entity is as follows:

class Travel {

    /**
     * @var integer
     *
     * @ORM\Column(name="travel_id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $travelId;

    /* OTHER VARIABLES HERE */

    /**
    * @var integer
    * @ORM\Column(name="transit_cities", type="json_array", nullable=true)
    */
    protected $transitCities;

    /* OTHER VARIABLES HERE */    

    public function __construct() {
        $this->transitCities = new ArrayCollection();
    }

    /* GETTERS AND SETTERS HERE */    

}

And in my form, I am using ChoiceType::class with allow_add set to true. Users will have option to add new values and remove the existing ones:

$builder->add('transitCities', CollectionType::class, array(
                'entry_type'    => null,
                'prototype'     => true,
                'allow_add'     => true,
                'allow_delete'  => true,
                'delete_empty'  => true,
                'error_bubbling'=> false,
                'entry_options' => array(
                    'attr'      => array('class' => 'transit-cities')
                ),
            ))

This works pretty well (have not set-up anything special inside the controller) with the default successful form submission. The persisted data have such structure:

[
    0 => "City 1",
    1 => "City 2",
    // and so on
]

where the keys (0 and 1 in this example) are auto generated by Doctrine. But I would like to use my own keys rather than the default index numbers and preferably also add other relevant information, as shown below:

[
    {'123' => 'City 1', 'dateTime' => 'Travel-date 1', 'opt'=> 1},
    {'45' => 'City 2', 'dateTime' => 'Travel-date 2', 'opt'=> 2},
    // and so on
]

123 and 45 could be the FK for city-ids while dateTime and opt could be other related information of each row.

==== DETAILS ADDED ===

Here is my City entity:

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Cities
 *
 * @ORM\Table(name="city")
 * @ORM\Entity
 * @ORM\Entity(repositoryClass="AppBundle\Repository\CityRepository")
 */
class City
{
    /**
     * @var string
     * @ORM\Column(name="name", type="string", length=70, nullable=false)
     */
    private $name;

    /**
     * @var string
     *
     * @ORM\Column(name="country", type="string", length=32, nullable=false)
     */
    private $country;

    /**
     * @var integer
     *
     * @ORM\Column(name="city_id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $cityId;


    /* CONST */
    public function __toString() {
        return $this->name;
    }

    /* OTHER PARAMETERS HERE */


    /* GETTERS AND SETTERS */    
    /**
     * Set name
     *
     * @param string $name
     *
     * @return City
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }


    /**
     * Get cityId
     *
     * @return integer
     */
    public function getCityId()
    {
        return $this->cityId;
    }


}

and I want to make use of the cityId for json_key. Example, if the user chose city "London" with corresponding cityId as say 100 and again city "Amsterdam" with cityId 115, then key-pair in json_array should be like:

{'100' => 'London', 'dateTime' => '2000-01-01', 'opt'=>1}

{'115' => 'Amsterdam', 'dateTime' => '2000-01-02', 'opt'=> 4}

where, dateTime is picked by user with js-date-picker and opt is un-mapped any arbitrary value just for example. Transit-Cities can be added and removed by user as per their choice (is already functioning).

How can this be achieved? Please suggest.

Wilt · Accepted Answer · 2016-09-26 09:53:08Z

3

You could create a custom mapping type to facilitate your custom json array.

From the documentation:

<?php
namespace My\Project\Types;

use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Platforms\AbstractPlatform;

/**
 * My custom json city type.
 */
class JsonCityType extends Type
{
    const MYTYPE = 'json_city_type';

    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        // Get json array type
        return $platform->getJsonTypeDeclarationSQL('json_array');
    }

    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        // This is executed when the value is read from the database. Make your conversions here, optionally using the $platform.
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        // This is executed when the value is written to the database. Make your conversions here, optionally using the $platform.
    }

    public function getName()
    {
        return self::MYTYPE; // modify to match your constant name
    }
}

Use like this:

/**
* @var integer
* @ORM\Column(name="transit_cities", type="json_city_type", nullable=true)
*/
protected $transitCities; // ;

And you need to register your custom type in doctrine config:

'doctrine' => array(
    'configuration' => array(
        'orm_default' => array(
            'types' => array(
                'json_city_type' => My\Project\Types\JsonCityType::class
            )
        )
    )
)

I leave the logic for customizing the keys and values inside convertToPHPValue and convertToDatabaseValue up to you since I have no idea where you will get your data from.

answered Sep 26, 2016 at 9:53

Wilt

44.9k15 gold badges163 silver badges216 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ren Over a year ago

Do I put the configuration in YML format under config.yml? I tried that but I got Unrecognized option "configuration" under "doctrine". Am I doing it right? Besides, I added more details of my entities, so maybe you can hint me with structuring the convertToPHPValue() and convertToDatabaseValue() now.

Wilt Over a year ago

@Ren, After you updated your question, I would suggest you make a City entity for your city data instead. In case of city name and city id a dedicated table sounds more appropriate.

Ren Over a year ago

Earlier I had City entity in $transitCities but I need other fields (dateTime, opt etc ), so I switched to the json_array. Do you mean it is better to create a new entity CityTransit, dedicated for transits only?

Ren · Accepted Answer · 2016-10-04 00:43:19Z

I tried several things with data-type json_array but nothing worked as intended. Therefore to make things simpler, I switched to simple_array data-type and added 3 new properties instead; namely cityId, dateTime and opt. So my new entity class looks something like the following:

class Travel {

    /**
     * @var integer
     *
     * @ORM\Column(name="travel_id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $travelId;

    /* OTHER PROPERTIES HERE */

    /**
     * @var integer
     *
     * @ORM\Column(name="transit_cities", type="simple_array", nullable=true)
     */
    protected $transitCities;

    /**
     * @var integer
     *
     * @ORM\Column(name="date_time", type="simple_array", nullable=true)
     */
    protected $dateTime;


    /**
     * @var integer
     *
     * @ORM\Column(name="opt", type="simple_array", nullable=true)
     */
    protected $opt;


    /* OTHER PROPERTIES + GETTERS AND SETTERS HERE */    

}

Though I missed the flexibility of adding new property to json_array any time in the future; nevertheless, the good thing now is that searching is much simpler with WHERE XX IN array() clauses.

Collectives™ on Stack Overflow

How to customize the keys and values in json_array in Doctrine?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related