Doctrine adds extra queries during hydration causing n+1 problem with "normal" one to one and self-referenced relations

Question

News are related to each other using one-to-many self-referencing approach (one news is parent and can have many children). What's more each News has got normal (non self-referenced) one to one relation with Event and Gallery . When I run simple DQL:

SELECT n FROM App\Entity\News n WHERE n.parent = :id

and then hydrate results by getResults method with default HYDRATION_OBJECT value set, extra queries are made somewhere inside getResults method.

SELECT t0.* FROM event t0 WHERE t0.news_id = 2 AND ((t0.deleted_at IS NULL));
SELECT t0.* FROM gallery t0 WHERE t0.news_id = 2 AND ((t0.deleted_at IS NULL));
SELECT t0.* FROM event t0 WHERE t0.news_id = 1 AND ((t0.deleted_at IS NULL));
SELECT t0.* FROM gallery t0 WHERE t0.news_id = 1 AND ((t0.deleted_at IS NULL));

Where news_id = 1 and news_id = 2 are children of news selected by first query.

News has got also not self-referenced one to many relations (I ignored them here), but hydration make no extra queries about them. The problem occurs only when there is parent relation involved in where statement.

How to reproduce

// news Entity
/**
 * @ORM\Entity(repositoryClass="App\Repository\NewsRepository")
 * @ORM\Table(uniqueConstraints={@UniqueConstraint(name="news_slug_deleted", columns={"slug","deleted_at"})})
 */
class News {
    use SoftDeleteableEntity;

    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;
    /**
     * @ORM\Column(type="string", length=255)
     */
    private $title;

    /**
     * @Gedmo\Slug(fields={"title"})
     * @ORM\Column(length=128)
     */
    private $slug;
/**
     * @ORM\OneToOne(targetEntity="App\Entity\Gallery", mappedBy="news", cascade={"persist", "remove"}, orphanRemoval=true)
     *
     * @var Gallery
     */
    private $gallery;

    /**
     * @ORM\OneToOne(targetEntity="App\Entity\Event", mappedBy="news", cascade={"persist", "remove"}, orphanRemoval=true)
     *
     * @var Event
     */
    private $event;
    /**
     * One News has Many News.
     * @ORM\OneToMany(targetEntity="News", mappedBy="parent")
     */
    private $children;

    /**
     * Many News have One News.
     * @ORM\ManyToOne(targetEntity="News", inversedBy="children")
     * @ORM\JoinColumn(name="parent_id", referencedColumnName="id", nullable=true)
     */
    private $parent;
}

/**
 * @ORM\Entity(repositoryClass="App\Repository\EventRepository")
 * @Gedmo\SoftDeleteable()
 */
class Event
{
    use SoftDeleteableEntity;

    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;
    /**
     * @ORM\OneToOne(targetEntity="App\Entity\News", inversedBy="event", cascade={"persist"})
     * @ORM\JoinColumn(nullable=false)
     */
    private $news;

Gallery entity is simmilar to Event so I ignored it here.

// News controller

public function index(NewsRepository $newsRepository, $slug)
{
        $news = $newsRepository->findOneBy(['slug' => $slug]);
        $newsRepository->getConnectedNews($news->getId());
}

// news repository

class NewsRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, News::class);
    }
    public function getConnectedNews($newsId) {
        $query = $this->createQueryBuilder('n')->andWhere('n.parent = :id')->setParameter('id', $newsId);
        return $query->getQuery()->getResult(AbstractQuery::HYDRATE_OBJECT);
    }
}

Hydration of news which has got 20 children ends up with: 20 * 2 + 1 (n*r+1) queries, where:

• (n) 20 is the number of children
• (r) 2 is the number of one to one relations
• 1 is the basic query

I want to prevent that extra queries to one to one relations made by Doctrine. Is it Doctrine bug or unwanted behaviour or I made a mistake somewhere?

All in all I want to just get all self-referenced children without one-to-one relations as I didn't ask to retrieve them so it should use just one query to get all children news without making additional queries for each retrieved 'news' object and each it's one to one relation.

Answer 1

You have a couple inverse OneToOne relationships in that entity.

Inverse OneToOne relationships cannot be lazy-loaded by Doctrine, and can easily become a performance problem.

If you really need to have those relationships mapped on the inverse side (and not only on the owning side) make sure to make the appropriate joins explicitly, or mark those associations as FETCH=EAGER so that Doctrine makes the joins for you.

E.g. a query that would avoid the dreaded "n+1" problem would be:

SELECT n, g, e
    FROM App\Entity\News n
    LEFT JOIN n.gallery g
    LEFT JOIN n.event e
WHERE n.parent = :id

You can read more about the N+1 problem in Doctrine here or here.