Database serialization

Hi, Habr!

Once I was sitting and trying to give the front JSON with real estate objects that had a lot of dependencies. Symfony 4, knp pagination and JMSSerializer were on the back, well, in principle, standard things, but the problem is that when you try to give an object with all nested entities and collections, everything starts to slow down at the level of serialization of this data.

First you need to make a request to the database, then the serializer will gradually tighten everything else, then it will all be wrapped in JSON and only then everything will return to the front.

Idea

I had an idea, why not return JSON directly to the front from the back directly from the database, yes, write a drop dead SQL, but you can make a tool that does it for you. I started writing the idea, the githaba repository , based on the data model from the doctrine, the OneToOne, ManyToOne, OneToMany and ManyToMany connections. Also, this tool can be easily screwed to Symfony 4 and it will set itself up; in the end, you only need to inject the QueryBuilderFactory factory and get QueryBuilder from there for the desired table according to the entity class.

Also, my serializer uses serialization groups that you can specify with the Expose annotation on the entity field, do not forget to hang the Table annotation on the entity and specify the table alias, it is better to use the ones that you usually specify.

SQL generation example

<?php use \Mash\MysqlJsonSerializer\QueryBuilder\Table\JoinStrategy\FieldStrategy; use \Mash\MysqlJsonSerializer\Wrapper\FieldWrapper; use \Mash\MysqlJsonSerializer\QueryBuilder\Table\Table; use \Mash\MysqlJsonSerializer\Wrapper\Mapping; use \Mash\MysqlJsonSerializer\QueryBuilder\QueryBuilder; $oneToManyTable = (new Table('advert_group', 'adg', 'adg_id')) ->addSimpleField('adg_id') ->addSimpleField('adg_name') ; $table = (new Table('estate', 'est', 'est_id')) ->addSimpleField('est_id') ->addSimpleField('est_name') ->addOneToManyField($oneToManyTable, 'advert_groups', new FieldStrategy('adg_estate')); $mapping = new Mapping(); $mapping ->addMap($table, 'est_id', 'id') ->addMap($table, 'est_name', 'name') ->addMap($oneToManyTable, 'adg_id', 'id') ->addMap($oneToManyTable, 'adg_name', 'name'); $builder = new QueryBuilder($table, new FieldWrapper($mapping)); $builder ->setOffset(2) ->setLimit(1); $sql = $builder->jsonArray(); 

As a result, the following SQL will be generated:

 SELECT JSON_ARRAYAGG(JSON_OBJECT('id',est_res.est_id,'name',est_res.est_name,'advert_groups',(SELECT JSON_ARRAYAGG(JSON_OBJECT('id',adg.adg_id,'name',adg.adg_name)) FROM advert_group adg INNER JOIN estate est_2 ON est_2.est_id = adg.adg_estate WHERE est_2.est_id = est_res.est_id))) FROM (SELECT * FROM estate est LIMIT 1 OFFSET 2) est_res 

Result:

 [{"id": 3, "name": "Москва, окская улица, 3к1", "advert_groups": [{"id": 10, "name": "avito-1115362430"}]}] 

Results

Full instructions for use will be added to the githab repository soon. As a result, when I screwed it in my project, I received very fast responses from the REST API and at the same time I was able to give a lot of objects with a large number of nested dependencies, for example, what I wanted to get through JMSSerializer I received for 40 seconds, now for 230 millisek, and this is on condition that Kernel Subscriber reads annotations in the fields of entities through reflection, I want to implement it soon via cache Symfony.

The article will be supplemented ...

The second part of