While this is still a valid way to reduce number of queries, my example uses EntityType in forms, I myself avoid it in my code and I suggest you to read this.

This is a quick reminder for everyone to optimize their queries and not to forget about that. I was reading Speed Up Your Database 300 Times and at slide number 6 there it was “Use JOIN to get all related data in one go.” the one thing I forgot that I shouldn’t, Use JOIN. I read about that long time ago on KnpUniversity (even if you do not have a subscription you can read every tutorial with code included, be sure to check it out).

The first thing I did was to check my current application I was working on. It wasn’t a big application, it was a form with a lot of fields, Entity translations and images but then I saw it in web profiler, that form was making around 60 queries. After reading everything from web profiler I saw that for every store I listed with →findAll() doctrine was making additional query when I asked for city where store was, so I created method in my Entity repository:

StoreRepository.php
<?php

namespace AppBundle\Repository;

use Doctrine\ORM\EntityRepository;

class StoreRepository extends EntityRepository
{
public function getAllStores()
{
$qb =$this->getEntityManager()->createQueryBuilder()
->select('store')
->from('AppBundle:Store', 'store')
->leftjoin('store.city', 'city')
return $qb->getQuery()->getResult(); } } I replaced every →findAll() and →findBy() with custom repository methods but that wasn’t enough, there was still some additional queries. EntityType Field was the next problem, I wasn’t aware that if you write something like this inside of your FormType: <?php use AppBundle\Entity\Category; use Symfony\Bridge\Doctrine\Form\Type\EntityType; // ... public function buildForm(FormBuilderInterface$builder, array $options) {$builder
'class' => Category::class
]);
}

you get the same problem as before, for everything that was a relationship inside of Category Entity doctrine will create a query for every info you request, in my case it was translations. Code for categories became:

<?php

use AppBundle\Entity\Category;
use AppBundle\Repository\CategoryRepository;
use Symfony\Bridge\Doctrine\Form\Type\EntityType;
// ...
public function buildForm(FormBuilderInterface $builder, array$options)
{
$builder ->add('category', EntityType::class, [ 'class' => Category::class, 'query_builder' => function (CategoryRepository$er) {
return $er->getAllCategories(); } ]); } The only difference from Store Repository example from before is that EntityType requested QueryBuilder object instead of results, so that looked like: CategoryRepository.php <?php namespace AppBundle\Repository; use Doctrine\ORM\EntityRepository; class CategoryRepository extends EntityRepository { public function getAllCategories() {$qb = $this->getEntityManager()->createQueryBuilder() ->select('category') ->from('AppBundle:Category', 'category') ->leftjoin('category.translations', 'categoryTranslations') ->addSelect('categoryTranslations'); return$qb; //returns just QueryBuilder
}
}

I did this for every EntityType field and at the end, my form was making just 6 queries, 10 times less than when I started. I hope this helps you and you do not forget to do this, I know that after this I won’t forget.