Doctrine 2: Cache in One-to-Many associations

Association fetch-modes

The query you present only fetches "parent" Category entities, which get hydrated with an uninitialized collection for the children. When accessing that collection (by iterating over those children for example), Doctrine will load the collection, thus perform another query. It will do that for all parent categories hydrated by the first query.

Setting fetch-mode to EAGER only changes the moment these queries are done. Doctrine will do them right after hydrating the parent categories, it won't wait until you access the collection (like with fetch-mode LAZY). But it will still do those queries.

Fetch-join query

The simplest way to tell Doctrine to query and hydrate the categories with their children is to do a "fetch join" query:

$queryBuilder = $this->em->createQueryBuilder();$queryBuilder    ->select('p', 'c') // [p]arent, [c]hild    ->from('App\Entity\Category', 'p')    ->leftJoin('p.children', 'c')    ->where('p.parent IS NULL')    ->orderBy('p.priority', 'ASC');$query = $queryBuilder->getQuery();$query    ->useResultCache(true, 900, 'categories')$result = $query->getResult();

Note the select() and leftJoin() calls here.

You also don't need to alter the fetch-mode of the association (by calling setFetchMode()), because the query itself will tell Doctrine to do what you want.

The result of this is that Doctrine will perform 1 query if it isn't cached yet (or the cache is stale), or 0 queries if it is cached (and still fresh).

Assumptions

The property $parentId (in Category) is renamed to $parent. This property will contain the parent Category entity, or null, but never an id.

The property $childrenId is renamed to $children. This property will contain a collection of Category entities (which might be empty), but never a collection (or array) of ids, and certainly never a single id.

The query I suggest above takes these renames into account.

I'm completely ignoring the fact that right after your "edit" a new Attribute entity has sprung into existence. It isn't relevant to your question or this answer IMHO.

More levels

It looks/sounds like your Categories only use 2 levels (parents and children). When you introduce more levels (grandchildren, etc), reading this model can become very inefficient very quickly.

When going for 3 or more levels, you might want to look into the Nested Set model. It's heavier on the writes, but highly optimized for reads.

The DoctrineExtensions library has support for this, and there's also a Symfony Bundle.

According to the docs: http://doctrine-orm.readthedocs.io/en/latest/reference/dql-doctrine-query-language.html#temporarily-change-fetch-mode-in-dql I think that setFetchMode - EAGER will not work for you as this will produce extra queries that will not be cached.

Why don't you load your categories with attributes?

$result = $this->em->createQueryBuilder()    ->select('c, a.name, a.value')    ->from('App\Entity\Category', 'c')    ->innerJoin('App\Entity\Attribute', 'a', 'WITH', 'a.id = c.attribute')    ->where('c.parentId IS NULL')    ->orderBy('c.priority', 'ASC')    ->getQuery()    ->useResultCache(true, 900, 'categories')    ->getResult();

And it should work as expected.