Doctrine OneToOne always loaded on querybuilder

Question

I have Partner entity with two relation:

/**
 * @var PartnerSettings
 * @ORM\OneToOne(targetEntity="PartnerSettings", mappedBy="partner", cascade={"persist", "remove"}, fetch="LAZY")
 */
private $settings;

/**
 * @var PartnerRating
 * @ORM\OneToOne(targetEntity="PartnerRating", mappedBy="partner", cascade={"persist", "remove"}, fetch="LAZY")
 */
private $rating;

...getRepository(Partner::class)->findAll() work correctly, one query was made,but when I create queryBuilder:

return $this->createQueryBuilder('p')
    ->getQuery()
    ->getResult();

doctrine make 31 queries(i have 10 partners)... in debug toolbar i saw select queries to settings and rating for every partner. I don't want it in this case. Additionally, in every querybuilder where I used join to partners, setting and rating are selected too.

answer

->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true);

did the job

score 1 · Answer 1 · answered Feb 27 '18 at 13:03

This behaviour is known as the N + 1 selects problem. To reduce database queries, you might want to consider the following approach.

First, retrieve all partners:

$partners = $em->createQueryBuilder()
    ->select("p")
    ->from("Parent", "p")
    ->where(/*...*/)
    ->setParameter(/*...*/)
    ->indexBy("p.id")
    ->getQuery()->getResult();

Now load all children at once, in two queries:

$settings = $em->createQueryBuilder()
    ->select("s")
    ->from("PartnerSetting", "s")
    ->where("IDENTITY(s.partner) IN (?1)")
    ->setParameter(1, array_keys($partners))
    ->getQuery()->getResult();

$ratings = $em->createQueryBuilder()
    ->select("r")
    ->from("PartnerRating", "r")
    ->where("IDENTITY(r.partner) IN (?1)")
    ->setParameter(1, array_keys($partners))
    ->getQuery()->getResult();

Doctrine will now have all of the retrieved entities are stored in memory. So when, for example, you do a $parnter->getRatings(), you don’t trigger a new DB query, instead the entity is filled from memory.

score 1 · Answer 2 · answered Jun 12 '20 at 14:59

1

just define the table AND the relation in the select

$qb->select('g', 'gi');

and now it works

answered Jun 12 '20 at 14:59

Lucien Herbiet

51
1
5

l13 · Answer 3 · 2018-02-27T13:07:32.693

0

Just mapped relation: @ORM\OneToOne(targetEntity="ENTITY", mappedBy="MAPPEDBY", fetch="EAGER")

edited Feb 27 '18 at 13:07

answered Feb 27 '18 at 12:36

l13

529
3
11

`eagerCursor` is for ODM, not ORM. – lxg Feb 27 '18 at 12:53
1

If you want to fix your answer, please edit it directly instead of commenting. – lxg Feb 27 '18 at 13:06

Doctrine OneToOne always loaded on querybuilder

3 Answers3