Hybrid databases with Symfony and Doctrine

SQL versus NoSQL

If you work with Symfony and Doctrine, the overwhelming likelihood is that you've been building your applications to work with conventional, relational data models. This is the default approach for most web applications, and it's a tried and tested method that has been around for decades.

In this approach, if we imagine we have a Restaurant entity, we would have a corresponding restaurant table in our database. We might then add properties to the data model, such as name, address, phone, and so on. These would then be represented as columns in the restaurant table.

Then, we might add a Menu entity, which would have a corresponding menu table in the database. This would have a restaurant_id column, which would be a foreign key to the restaurant table. This would allow us to link a Menu to a Restaurant.

As our data models grow, we might end up adding all sorts of related entities linked to a Restaurant, each with its own table and foreign key relationships.

I've worked on projects where as requirements evolved, we've ended up with dozens of tables, each with multiple foreign key relationships. This can make it difficult to manage the data model, and it can also have a negative impact on performance - particularly if you use Doctrine's default lazy loading approach.

The rise of NoSQL

In recent years, NoSQL ("Not only SQL") databases have become increasingly popular. NoSQL databases are designed to be more flexible than traditional relational databases, and they are particularly well-suited to handling large volumes of unstructured or semi-structured data.

NoSQL databases are often used in scenarios where data is constantly changing, and where the data model is likely to evolve over time. They are also commonly used in applications that require high throughput and horizontal scalability.

Common choices for NoSQL databases include MongoDB, CouchDB, and Redis.

But what if you want to take advantage of the flexibility and scalability of NoSQL databases, while still retaining the benefits of a relational database? This is where hybrid databases come in.

The JSON data type

In recent years, many relational databases have added support for the JSON data type. This allows you to store JSON data in a column in a relational database table. Not as text, but as actual data which can be efficiently stored, queried, and indexed.

This is a game-changer, because it allows you to combine the flexibility of NoSQL with the power of a relational database.

In this tutorial, we'll look at how to use the native JSONB data type in PostgreSQL to create a hybrid database that combines the best of both worlds, using a simple example based on our Restaurant entity.

We'll be building a Restaurant entity that has a number of JSON fields, including opening hours, menu, and services offered.

We'll also create a form to manage the example entity, which looks like the following:

Restaurant form using hybrid data model

Set up Symfony

For this tutorial, I'm going to assume you're familiar with creating a new Symfony project, running your database server via Docker or other stack of your choice, the basics of sticking in entities, controllers and Twig templates, and running a local server.

If you're not, you can find a tutorial on how to do this on the Symfony website.

We'll be using Postgres 15 as our database, which is the default version in the docker-compose file included in the Symfony skeleton.

So spin yourself up a new, blank Symfony 6.3 project with Postgres, and let's get started.

Define our hybrid data model

We're going to create a Restaurant which will have an ID and name, as any normal relational entity would. But we're also going to add data about opening hours, menu and services offered which will all take the form of JSON data.

We'll see not just how to store and query this data, but how to leverage the power of Symfony and Doctrine to map these fields to fully fleshed-out models to which we can apply constraints, use in forms, and so on.

My suggestion is that you create the entities for this tutorial with the make:entity command, as this will automatically generate the repository classes for you. You can then add the annotations and other code as we go along, before generating your database schema migrations.

Create the Restaurant entity

The restaurant entity is pretty simple. We'll give it an ID, a name, and a few JSON fields for opening hours, menu and services.

<?php

namespace App\Entity;

use App\Attribute\HydrateFromJson;
use App\Model\MenuItem;
use App\Repository\RestaurantRepository;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

use Symfony\Component\Validator\Constraints as Assert;

#[ORM\Entity(repositoryClass: RestaurantRepository::class)]
class Restaurant
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 255)]
    private ?string $name = null;

    #[ORM\Column(type: Types::JSON, options: ['jsonb' => true, 'default' => '[]'])]
    #[Assert\All(constraints: [
        new Assert\Collection(fields: [
            'day' => [
                new Assert\NotBlank(),
                new Assert\Choice(choices: ['monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday'])
            ],
            'open' => [
                new Assert\NotBlank(message: 'Please specify an opening time or mark as closed.', allowNull: true),
                new Assert\Time()
            ],
            'close' => [
                new Assert\NotBlank(message: 'Please specify a closing time or mark as closed.', allowNull: true),
                new Assert\Time()
            ],
            'closed' => [
                new Assert\Optional(new Assert\Type(type: 'bool')),
            ]
        ])
    ])]
    private array $openingHours;

    #[ORM\Column(type: Types::JSON, options: ['jsonb' => true, 'default' => '[]'])]
    #[Assert\All(constraints: [
        new Assert\Type(type: MenuItem::class)
    ])]
    #[Assert\Valid]
    #[HydrateFromJson(class: MenuItem::class, isCollection: true)]
    private array $menu;

    #[ORM\Column(type: Types::JSON, options: ['jsonb' => true, 'default' => '[]'])]
    #[Assert\All(constraints: [
        new Assert\Type(type: 'array'),
        new Assert\All(constraints: [
            new Assert\Type(type: 'string')
        ])
    ])]
    private array $services;

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getName(): ?string
    {
        return $this->name;
    }

    public function setName(string $name): self
    {
        $this->name = $name;

        return $this;
    }

    public function getOpeningHours(): array
    {
        return $this->openingHours;
    }

    public function setOpeningHours(array $openingHours): self
    {
        $this->openingHours = $openingHours;

        return $this;
    }

    public function getMenu(): array
    {
        return $this->menu;
    }

    public function setMenu(array $menu): self
    {
        $this->menu = array_values($menu);

        return $this;
    }

    public function getServices(): array
    {
        return $this->services;
    }

    public function setServices(array $services): self
    {
        $this->services = $services;

        return $this;
    }
}

Notice that the actual PHP type of the JSON fields is array. This is because Doctrine will automatically convert the JSON data to and from PHP arrays for us. We can then use the Symfony validator to validate the data, and we can use a special attribute which we will define later on, HydrateFromJson, to automatically convert the JSON data to a collection of objects.

This means in our application code, much like with related entities following a normal relational model, we can work with fully-fledged objects rather than indistinct arrays.

Create the MenuItem model

We don't need entities for our JSON columns, since these related objects don't have their own tables. But we do want models of the data, so we can use them in our application code.

The key trick with these models is that they're going to implement the JsonSerializable interface, which will allow us to automatically convert them to JSON. We're then also going to implement a fromArray method, which will allow us to convert from JSON to an object when we're hydrating our entities.

We're also adding some validation constraints on the model, which will be used when we're validating the data in our forms.

<?php

namespace App\Model;

use App\Enum\MenuCategory;

use Symfony\Component\Validator\Constraints as Assert;

class MenuItem implements \JsonSerializable
{
    #[Assert\NotBlank]
    public string $name = '';
    #[Assert\PositiveOrZero]
    public int $price = 0;
    #[Assert\NotBlank]
    public string $description = '';
    public MenuCategory $category = MenuCategory::Main;
    public bool $vegetarian = false;
    public bool $vegan = false;
    #[Assert\All(
        constraints: [
            new Assert\NotBlank(),
            new Assert\Type('string'),
            new Assert\Length(max: 255),
        ]
    )]
    public array $allergens = [];
    #[Assert\All(
        constraints: [
            new Assert\NotBlank(),
            new Assert\Type('string'),
            new Assert\Length(max: 255),
        ]
    )]
    public array $keywords = [];

    public function __construct(
        string $name = '',
        int $price = 0,
        string $description = '',
        MenuCategory $category = MenuCategory::Main,
        bool $vegetarian = false,
        bool $vegan = false,
        array $allergens = [],
        array $keywords = []
    ) {
        $this->name = $name;
        $this->price = $price;
        $this->description = $description;
        $this->category = $category;
        $this->vegetarian = $vegetarian;
        $this->vegan = $vegan;
        $this->allergens = $allergens;
        $this->keywords = $keywords;
    }

    public function toArray(): array
    {
        return [
            'name' => $this->name,
            'price' => $this->price,
            'description' => $this->description,
            'category' => $this->category->value,
            'vegetarian' => $this->vegetarian,
            'vegan' => $this->vegan,
            'allergens' => $this->allergens,
            'keywords' => $this->keywords,
        ];
    }

    public static function fromArray(array $data): static
    {
        $data['category'] = MenuCategory::tryFrom($data['category']) ?? MenuCategory::Main;
        return new static(
            $data['name'] ?? '',
            $data['price'] ?? 0,
            $data['description'] ?? '',
            $data['category'],
            $data['vegetarian'] ?? false,
            $data['vegan'] ?? false,
            $data['allergens'] ?? [],
            $data['keywords'] ?? [],
        );
    }

    public function jsonSerialize(): array
    {
        return $this->toArray();
    }
}

In the above code, we're using the MenuCategory enum, which we'll define later on. We're also using the tryFrom method on the enum, which will return the enum value if it exists, or null if it doesn't. This is useful for when we're converting from JSON to an object, since depending on the source of the input, we can't guarantee that the JSON data will be valid.

Create the MenuCategory enum

We're going to use an enum for the menu category, since this is a fixed set of values.

<?php

namespace App\Enum;

enum MenuCategory: string
{
    case Starter = 'starter';
    case Main = 'main';
    case Dessert = 'dessert';
}

Create the Service and ServiceGroup entities

Our restaurant includes a list of services, which are stored as a JSON array. We're going to create a Service entity, which will define lists of services from which individual restaurants can choose. Services will belong to a ServiceGroup, which will allow us to group related services together.

This allows us to explore the power of using hybrid data models in a bit more detail.

<?php

namespace App\Entity;

use App\Repository\ServiceRepository;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: ServiceRepository::class)]
class Service
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 255)]
    private ?string $name = null;

    #[ORM\ManyToOne(inversedBy: 'services')]
    private ?ServiceGroup $serviceGroup = null;

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getServiceGroup(): ?ServiceGroup
    {
        return $this->serviceGroup;
    }

    public function setServiceGroup(?ServiceGroup $serviceGroup): self
    {
        $this->serviceGroup = $serviceGroup;

        return $this;
    }

    public function getName(): ?string
    {
        return $this->name;
    }

    public function setName(string $name): self
    {
        $this->name = $name;

        return $this;
    }
}
<?php

namespace App\Entity;

use App\Repository\ServiceGroupRepository;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: ServiceGroupRepository::class)]
class ServiceGroup
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 255)]
    private ?string $name = null;

    #[ORM\OneToMany(mappedBy: 'serviceGroup', targetEntity: Service::class)]
    private Collection $services;

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

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getName(): ?string
    {
        return $this->name;
    }

    public function setName(string $name): self
    {
        $this->name = $name;

        return $this;
    }

    /**
     * @return Collection<int, Service>
     */
    public function getServices(): Collection
    {
        return $this->services;
    }

    public function addService(Service $service): self
    {
        if (!$this->services->contains($service)) {
            $this->services->add($service);
            $service->setServiceGroup($this);
        }

        return $this;
    }

    public function removeService(Service $service): self
    {
        if ($this->services->removeElement($service)) {
            if ($service->getServiceGroup() === $this) {
                $service->setServiceGroup(null);
            }
        }

        return $this;
    }
}

Create a Restaurant form

To recap, at this point we have three entities; Restaurant, Service and ServiceGroup. We also have one enum type, MenuCategory and one model class, MenuItem.

To properly handle our form design, we're going to need to create a number of form types and a few data transformers.

Create the RestaurantType form type

<?php

namespace App\Form;

use App\Entity\Restaurant;
use App\Entity\Service;
use App\Form\Transformer\RestaurantOpeningHoursTransformer;
use App\Form\Transformer\RestaurantServicesTransformer;
use App\Repository\ServiceRepository;
use Symfony\Bridge\Doctrine\Form\Type\EntityType;
use Symfony\Component\Form\AbstractType;
use Symfony\Component\Form\Extension\Core\Type\CollectionType;
use Symfony\Component\Form\Extension\Core\Type\SubmitType;
use Symfony\Component\Form\FormBuilderInterface;
use Symfony\Component\OptionsResolver\OptionsResolver;

class RestaurantType extends AbstractType
{
    public function __construct(private readonly ServiceRepository $serviceRepository)
    {
    }

    public function buildForm(FormBuilderInterface $builder, array $options): void
    {
        $builder
            ->add('name')
            ->add('openingHours', OpeningHoursType::class, [
                'label' => 'Opening hours',
                'label_attr' => ['class' => 'fieldset-label'],
            ])
            ->add('menu', CollectionType::class, [
                'entry_type' => MenuItemType::class,
                'entry_options' => [
                    'label' => false,
                ],
                'allow_add' => true,
                'allow_delete' => true,
                'label' => 'Menu',
                'label_attr' => ['class' => 'fieldset-label'],
            ])
            ->add('services', EntityType::class, [
                'class' => Service::class,
                'choice_label' => 'name',
                'expanded' => true, // Render checkboxes
                'multiple' => true, // Allow multiple selection
                'group_by' => function (Service $service) {
                    return $service->getServiceGroup()->getName();
                },
                'query_builder' => function (ServiceRepository $er) {
                    return $er->createQueryBuilder('s')
                        ->innerJoin('s.serviceGroup', 'g')
                        ->orderBy('g.name', 'ASC')
                        ->addOrderBy('s.name', 'ASC');
                },
            ])
            ->add('save', SubmitType::class)
        ;

        $builder->get('services')->addModelTransformer(new RestaurantServicesTransformer($this->serviceRepository));
        $builder->get('openingHours')->addModelTransformer(new RestaurantOpeningHoursTransformer());
    }

    public function configureOptions(OptionsResolver $resolver): void
    {
        $resolver->setDefaults([
            'data_class' => Restaurant::class,
        ]);
    }
}

In keeping with idiomatic Symfony, our form type is as simple as possible. We're using the EntityType form type to render a list of checkboxes for the services field. We're also using a couple of data transformers to convert the openingHours and services fields into the correct format both from the database to the form, and vice-versa.

Let's create the other couple of embedded form types we need.

Create the OpeningHoursType form type

For each day of the week, our form will allow the user to specify an opening time, closing time, or check a box to indicate that the restaurant is closed on that day.

<?php

namespace App\Form;

use Symfony\Component\Form\AbstractType;
use Symfony\Component\Form\Extension\Core\Type\CheckboxType;
use Symfony\Component\Form\Extension\Core\Type\TimeType;
use Symfony\Component\Form\FormBuilderInterface;
use Symfony\Component\Form\FormEvent;
use Symfony\Component\Form\FormEvents;
use Symfony\Component\OptionsResolver\OptionsResolver;

class OpeningHoursType extends AbstractType
{
    public function buildForm(FormBuilderInterface $builder, array $options): void
    {
        $days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'];
        $i = 0;
        foreach ($days as $day) {
            $fieldName = strtolower($day);
            $builder->add($fieldName . '_open', TimeType::class, [
                'label' => $day . ' open',
                'input' => 'string',
                'input_format' => 'His',
                'widget' => 'single_text',
                'required' => false,
                'html5' => true,
            ]);
            $builder->add($fieldName . '_close', TimeType::class, [
                'label' => $day . ' close',
                'input' => 'string',
                'input_format' => 'His',
                'widget' => 'single_text',
                'required' => false,
                'html5' => true,
            ]);
            $builder->add($fieldName . '_closed', CheckboxType::class, [
                'label' => $day . ' closed',
                'required' => false,
            ]);
            $i++;
        }

        $builder->addEventListener(FormEvents::SUBMIT, function (FormEvent $event) use ($days) {
            $data = $event->getData();
            foreach ($days as $day) {
                $fieldName = strtolower($day);
                if ($data[$fieldName . '_closed']) {
                    $data[$fieldName . '_open'] = null;
                    $data[$fieldName . '_close'] = null;
                }
            }
            $event->setData($data);
        });
    }

    public function configureOptions(OptionsResolver $resolver): void
    {
        $days = ['monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday'];
        $errorMap = [];
        foreach (range(0, 6) as $i) {
            $errorMap['[' . $i . '][open]'] = $days[$i] . '_open';
            $errorMap['[' . $i . '][close]'] = $days[$i] . '_close';
            $errorMap['[' . $i . '][closed]'] = $days[$i] . '_closed';
        }
        $resolver->setDefaults([
            'error_mapping' => $errorMap,
        ]);
    }
}

We're using a FormEvents::SUBMIT event listener to convert the closed checkbox into a null value for the open and close fields. This is because we don't want to store a time value in the database if the restaurant is closed on that day.

We're also using a custom error_mapping option to map the errors from the openingHours field of our entity to the correct fields in the form. This is because the openingHours field is a collection of array entries, and Symfony doesn't know how to map the errors from the collection to the correct fields in the form, since this form type isn't mapped to a data class.

Of course, this only makes sense if we have a way to transform the data from the form structure into the correct structure for the openingHours field of our entity. Let's create a data transformer for that.

Create the RestaurantOpeningHoursTransformer data transformer

<?php

namespace App\Form\Transformer;

use Symfony\Component\Form\DataTransformerInterface;

class RestaurantOpeningHoursTransformer implements DataTransformerInterface
{

    public function __construct()
    {
    }

    public function transform(mixed $value): array
    {
        $openingHours = [];
        if (is_array($value)) {
            foreach ($value as $day) {
                $openingHours[$day['day'] . '_open'] = $day['open'];
                $openingHours[$day['day'] . '_close'] = $day['close'];
                $openingHours[$day['day'] . '_closed'] = $day['closed'];
            }
        }
        return $openingHours;
    }

    public function reverseTransform(mixed $value): array
    {
        $days = ['monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday'];
        $data = [];
        foreach ($days as $day) {
            $keys = ['open', 'close', 'closed'];
            $valueKeys = array_map(fn ($key) => $day . '_' . $key, $keys);
            $dayData = ['day' => $day];
            $dayData += array_combine($keys, array_map(fn ($key) => $value[$key], $valueKeys));
            $data[] = $dayData;
        }
        return $data;
    }
}

In a data transformer, the transform method is used to convert the data from the database into the correct format for the form, and the reverseTransform method is used to convert the data from the form into the correct format for the database. In this case, we're converting the openingHours field from an array of arrays into an array of key-value pairs, and vice-versa.

In short, in our database JSON column, opening hours looks like this:

[
  {
    "day": "monday",
    "open": "090000",
    "close": "170000",
    "closed": false
  },
  {
    "day": "tuesday",
    "open": "090000",
    "close": "170000",
    "closed": false
  },
  {
    "day": "wednesday",
    "open": "090000",
    "close": "170000",
    "closed": false
  },
  {
    "day": "thursday",
    "open": "090000",
    "close": "170000",
    "closed": false
  },
  {
    "day": "friday",
    "open": "090000",
    "close": "170000",
    "closed": false
  },
  {
    "day": "saturday",
    "open": "090000",
    "close": "170000",
    "closed": false
  },
  {
    "day": "sunday",
    "open": null,
    "close": null,
    "closed": true
  }
]

Whereas for our form, the data looks like this:

{
  "monday_open": "090000",
  "monday_close": "170000",
  "monday_closed": false,
  "tuesday_open": "090000",
  "tuesday_close": "170000",
  "tuesday_closed": false,
  "wednesday_open": "090000",
  "wednesday_close": "170000",
  "wednesday_closed": false,
  "thursday_open": "090000",
  "thursday_close": "170000",
  "thursday_closed": false,
  "friday_open": "090000",
  "friday_close": "170000",
  "friday_closed": false,
  "saturday_open": "090000",
  "saturday_close": "170000",
  "saturday_closed": false,
  "sunday_open": null,
  "sunday_close": null,
  "sunday_closed": true
}

Create the MenuItemType form type

Unlike the OpeningHoursType form type, the MenuItemType form type is mapped to a data class, so we don't need to create a data transformer for it. Our data class doesn't need to be a Doctrine entity! We can use the MenuItem model class that we created earlier and Symfony will automatically map the form data to the model for us.

<?php

namespace App\Form;

use App\Enum\MenuCategory;
use App\Model\MenuItem;
use Symfony\Component\Form\AbstractType;
use Symfony\Component\Form\CallbackTransformer;
use Symfony\Component\Form\Extension\Core\Type\CheckboxType;
use Symfony\Component\Form\Extension\Core\Type\EnumType;
use Symfony\Component\Form\Extension\Core\Type\MoneyType;
use Symfony\Component\Form\Extension\Core\Type\TextType;
use Symfony\Component\Form\FormBuilderInterface;
use Symfony\Component\OptionsResolver\OptionsResolver;

class MenuItemType extends AbstractType
{
    public function buildForm(FormBuilderInterface $builder, array $options): void
    {
        $arrayToStringTransformer = new CallbackTransformer(
            function ($allergensAsArray) {
                return ($allergensAsArray === null) ? '' : implode(',', $allergensAsArray);
            },
            function ($allergensAsString) {
                return ($allergensAsString === null) ? [] : explode(',', $allergensAsString);
            }
        );

        $builder
            ->add('name', null, [
                'empty_data' => '',
            ])
            ->add('price', MoneyType::class, [
                'divisor' => 100,
                'currency' => 'GBP',
            ])
            ->add('description', null, [
                'empty_data' => '',
            ])
            ->add('category', EnumType::class, [
                'class' => MenuCategory::class,
            ])
            ->add('vegetarian', CheckboxType::class, [
                'required' => false,
            ])
            ->add('vegan', CheckboxType::class, [
                'required' => false,
            ])
            ->add(
                $builder->create('allergens', TextType::class, [
                    'help' => 'Separate allergens with commas',
                    'required' => false,
                ])
                ->addModelTransformer($arrayToStringTransformer)
            )
            ->add(
                $builder->create('keywords', TextType::class, [
                    'help' => 'Separate keywords with commas',
                    'required' => false,
                ])
                ->addModelTransformer($arrayToStringTransformer)
            )
        ;
    }

    public function configureOptions(OptionsResolver $resolver): void
    {
        $resolver->setDefaults([
            'data_class' => MenuItem::class,
        ]);
    }
}

All we've done here is add a simple transformer to convert a comma-separated string into an array of strings, and vice-versa, for our allergens and keywords fields. We've also added a divisor and currency to the price field, so that the form will accept a price in pounds and pence, but the model will store the price in pence only. This helps us to avoid floating point rounding errors.

Create the RestaurantServicesTransformer data transformer

The RestaurantServicesTransformer data transformer is used to convert the services field in the Restaurant entity, which is stored as JSON, in to an array of Service objects which is used by the RestaurantType form, where services is a normal EntityType. The transformer also converts the array of Service objects back into an array which will be stored as JSON in the database.

<?php

namespace App\Form\Transformer;

use App\Repository\ServiceRepository;
use Symfony\Component\Form\DataTransformerInterface;

class RestaurantServicesTransformer implements DataTransformerInterface
{
    public function __construct(private readonly ServiceRepository $serviceRepository)
    {
    }

    public function transform(mixed $value)
    {
        $formServices = [];
        if (is_array($value)) {
            foreach ($value as $group => $services) {
                foreach ($services as $service) {
                    $serviceObject = $this->serviceRepository->findOneBy(['name' => $service]);
                    if ($serviceObject) {
                        $formServices[] = $serviceObject;
                    }
                }
            }
        }
        return $formServices;
    }

    public function reverseTransform(mixed $value)
    {
        $data = [];
        foreach ($value as $service) {
            $group = $service->getServiceGroup();
            $data[$group->getName()][] = $service->getName();
        }
        return $data;
    }
}

Create the RestaurantController

Let's create a simple controller endpoint in your project to create and process the form. All I've done in my demo is manually look up the first entry in the Restaurant table and use it to populate the form.

// ... inside route endpoint
    $restaurant = $repo->find(1);
    $form = $this->createForm(RestaurantType::class, $restaurant);
    $form->handleRequest($request);
    if ($form->isSubmitted() && $form->isValid()) {
        $repo->add($restaurant, true);
    }
    return $this->render('json_demo/index.html.twig', [
        'form' => $form->createView(),
    ]);

Render the form

Now we can render the form in our Twig template. I've used the form_row function to render each field individually, so I can control the layout of the form. Our services field is also rendered using a loop to go through the groups created by the group_by option on the EntityType.

I'm also using the Symfony Stimulus bridge to add the ability to create new menu items via the CollectionType.

{{ form_start(form) }}
{{ form_row(form._token) }}
{{ form_row(form.name) }}
<legend class="fieldset-label">Menu</legend>
{% for item in form.menu %}
    {{ form_row(item, {attr: {class: 'card card-body mb-3'}})}}
{% endfor %}
<div class="mb-3" {{ stimulus_controller('menu-item') }}
        data-menu-item-index-value="{{ form.menu|length > 0 ? form.menu|last.vars.name + 1 : 0 }}"
        data-menu-item-prototype-value="{{ form_widget(form.menu.vars.prototype)|e('html_attr') }}"
>
    <div {{ stimulus_target('menu-item', 'collectionContainer') }}></div>
    <button type="button" class="btn btn-primary" {{ stimulus_action('menu-item', 'addCollectionElement') }}>Add menu item</button>
</div>
{{ form_row(form.openingHours) }}
{% for group, choices in form.services.vars.choices %}
    <legend class="fieldset-label">{{ group }}</legend>
    {% for key,choice in choices %}
        {{ form_row(form.services[key]) }}
    {% endfor %}
{% endfor %}
{{ form_end(form) }}

Convert JSON fields to hydrated models automatically with Doctrine

The final step is to configure Doctrine to automatically convert the JSON fields in our entities into hydrated models.

We're using two things to do this: first, the #[HydrateFromJson] attribute on the Restaurant entity, and second, an event listener which will hook in to Doctrine's postLoad event, fired whenever an entity is loaded from the database.

First we define the attribute:

<?php

namespace App\Attribute;

use Attribute;

#[Attribute(Attribute::TARGET_PROPERTY)]
class HydrateFromJson
{
    public function __construct(public readonly string $class, public readonly bool $isCollection = false)
    {
    }
}

Then we create our event listener:

<?php

namespace App\EventListener;

use App\Attribute\HydrateFromJson;
use App\Entity\Restaurant;
use App\Model\MenuItem;
use Doctrine\Bundle\DoctrineBundle\Attribute\AsDoctrineListener;
use Doctrine\ORM\Event\LifecycleEventArgs;
use Doctrine\ORM\Events;

#[AsDoctrineListener(Events::postLoad)]
class EntityDataTransformer
{
    public function __construct(private array $attributes = [])
    {
    }

    public function postLoad(LifecycleEventArgs $args): void
    {
        $entity = $args->getObject();
        if (!is_object($entity)) {
            return;
        }

        $class = get_class($entity);
        if (isset($this->attributes[$class])) {
            foreach ($this->attributes[$class] as $property => $attribute) {
                $isCollection = $attribute['isCollection'];
                $class = $attribute['class'];
                $value = $entity->{'get' . ucfirst($property)}();
                if ($isCollection) {
                    $collection = [];
                    foreach ($value as $item) {
                        $collection[] = $class::fromArray($item);
                    }
                    $value = $collection;
                } else {
                    $value = $class::fromArray($value);
                }
                $entity->{'set' . ucfirst($property)}($value);
            }
            return;
        }

        $reflectionClass = new \ReflectionClass($entity);
        $reflectionProperties = $reflectionClass->getProperties();
        foreach ($reflectionProperties as $reflectionProperty) {
            $attributes = $reflectionProperty->getAttributes(HydrateFromJson::class);
            if (count($attributes) > 0) {
                $attribute = $attributes[0]->getArguments();
                $class = $attribute['class'];
                $isCollection = $attribute['isCollection'];
                $property = $reflectionProperty->getName();
                $this->attributes[$reflectionClass->getName()][$property] = $attribute;
                $value = $entity->{'get' . ucfirst($property)}();
                if ($isCollection) {
                    $collection = [];
                    foreach ($value as $item) {
                        $collection[] = $class::fromArray($item);
                    }
                    $value = $collection;
                } else {
                    $value = $class::fromArray($value);
                }
                $entity->{'set' . ucfirst($property)}($value);
            }
        }
    }
}

What this class does is look for any properties on the entity which have the #[HydrateFromJson] attribute, and then convert the JSON data into a hydrated model. It also caches the attributes it finds so it doesn't have to do the reflection again should another entity of the same type be loaded later in the same request.

For each property found, it will call the fromArray method on the model class, which we defined earlier. If the attribute tells our listener this property is a collection, it will loop through each item in the collection and convert it to a model the same way.

The event listener utilises the #[AsDoctrineListener] attribute to tell Doctrine to register it as a listener for the postLoad event.

Putting it all together

Now we can see our form in action. Here's the form rendered in the browser again:

Restaurant form using hybrid data model

Here's an example of an error message when the form is submitted with invalid data:

Restaurant form showing error on opening hours

And here's the data that gets saved to the database:

Restaurant data with JSON columns in database

Finally, for reference, here's my example service and service_group tables:

Services data in database Service groups data in database

Conclusion

Hopefully this has been a useful demonstration of how to use Symfony forms with JSON data. I've tried to keep the example as simple as possible, but it should be easy to extend it to suit your needs.

I've used this hybrid approach very successfully to build complex forms and models with dynamic fields and collections, enhancing performance where we would otherwise be writing complex joins or executing multiple queries to retrieve the data we need.

It's also been a life-saver when working with shifting requirements, as it's easy to add new fields to the JSON data without having to make changes to the database schema.

One thing I haven't covered is querying the data in the database. This is (or can be) a bit more complex but short version is you just need to write native queries with Doctrine and use the ResultSetMapping class to map the results back to your models. I may cover this in more detail in a future post.


Comments

Add a comment

All comments are pre-moderated and will not be published until approval.
Moderation policy: no abuse, no spam, no problem.

You can write in _italics_ or **bold** like this.

starck Monday 17 July 2023, 10:06

Thank you, great demo!

Recent posts


Saturday 10 February 2024, 17:18

The difference between failure and success isn't whether you make mistakes, it's whether you learn from them.

musings coding

Monday 22 January 2024, 20:15

Recalling the time I turned down a job offer because the company's interview technique sucked.

musings

SPONSORED AD

Buy this advertising space. Your product, your logo, your promotional text, your call to action, visible on every page. Space available for 3, 6 or 12 months.

Get in touch

Friday 19 January 2024, 18:50

Recalling the time I was rejected on the basis of a tech test...for the strangest reason!

musings

Monday 28 August 2023, 11:26

Why type hinting an array as a parameter or return type is an anti-pattern and should be avoided.

php

Saturday 17 June 2023, 15:49

Leveraging the power of JSON and RDBMS for a combined SQL/NoSQL approach.

php