Problems with Doctrine QueryBuilder

Question

This is my first project with Symfony / Doctrine

I have this entity called Nuclei

class Nuclei
{
    /**
     * @var int
     *
     * @ORM\Id
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\GeneratedValue(strategy="NONE")
     */
    private $id;

    /**
     * @var Utenti
     *
     * @ORM\OneToOne(targetEntity="Utenti", mappedBy="nucleo")
     *
     */
    private $utente;

    /**
     * @var Comuni
     *
     * @ORM\ManyToOne(targetEntity="Comuni", inversedBy="nuclei", fetch="EAGER")
     * @ORM\JoinColumn(name="idcomune", referencedColumnName="id")
     */
    private $comune;

    /**
     * @var Tag
     *
     * @ORM\OneToMany(targetEntity="Tag", mappedBy="nucleo", fetch="EAGER")
     * @ORM\JoinColumn(name="idnucleo", referencedColumnName="id")
     */
    private $codicitag;

    /**
     * @var NucleiStatistiche
     *
     * @ORM\OneToOne(targetEntity="NucleiStatistiche", fetch="EAGER")
     * @ORM\JoinColumn(nullable=true, name="id", referencedColumnName="id")
     */
    private $statistiche;

I usually query this entity using findBy method and works perfectly

$params['nuclei'] = $this->getDoctrine()->getRepository(Nuclei::class)->findBy(
    array('comune' => $this->user->getComune()->getId())
);

Using this entities to print a simple html table.

Now i need to switch to QueryBuilder because i should define conditional params used for a term search, but have many problems, this throw 33 queries!!

    $query = $this->getDoctrine()->getManager()->createQueryBuilder()
        ->select('n')
        ->from('App\Entity\Nuclei','n')
        ->innerJoin('n.comune', 'c')
        ->innerJoin('n.statistiche', 'ns')
        ->innerJoin('n.codicitag', 't')
        ->where('c.id = :id_comune')
        ->setParameter('id_comune', $this->user->getComune()->getId())
        ->setMaxResults(10)
        ->getQuery();

I debug it, and i see many many queries repeated for each record

score 0 · Answer 1 · answered Jul 05 '20 at 16:13

I understand, Doctrine documentation talks about why it has to perform an extra query when fetching the inverse side of a one-to-one relation.

IF you want to avoid extra query you should include them into main QueryBuilder!

So it will be

$query = $this->getDoctrine()->getManager()->createQueryBuilder()
    ->select('n, t, u, ns')
    ->from('App\Entity\Nuclei','n')
    ->innerJoin('n.utente', 'u')
    ->innerJoin('n.comune', 'c')
    ->innerJoin('n.statistiche', 'ns')
    ->innerJoin('n.codicitag', 't')
    ->where('n.comune = :id_comune')
    ->setParameter('id_comune', $this->user->getComune()->getId());

Problems with Doctrine QueryBuilder

1 Answers1