Doctrine 2: Cache in One-to-Many associations

Question

I'm trying to use doctrine cache from Common package, but I can't get it working with one-to-many, many-to-one accosiations. I'll explain later what I want to do.

My configuration:

'configuration' => array(
    'orm_default' => array(
        'metadata_cache'    => 'filesystem',
        'query_cache'       => 'filesystem',
        'result_cache'      => 'filesystem',
        'hydration_cache'   => 'filesystem',
    )
),

My entity

class Category
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     *
     * @var string
     */
    protected $id;

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=100, nullable=false)
     */
    protected  $name;

    /**
     * @var integer
     *
     * @ORM\ManyToOne(targetEntity="Category", inversedBy="childrenId", fetch="EAGER")
     * @ORM\JoinColumn(name="parent_id", referencedColumnName="id")
     */
    protected $parentId;


    /**
     * @ORM\OneToMany(targetEntity="Category", mappedBy="parentId", fetch="EAGER")
     */
    protected $childrenId;
}

My DQL

$result = $this->em->createQueryBuilder()->select('c')
    ->from('App\Entity\Category', 'c')
    ->where('c.parentId IS NULL')
    ->orderBy('c.priority', 'ASC')
    ->getQuery()
    ->setFetchMode("App\Entity\Category", "parentId", \Doctrine\ORM\Mapping\ClassMetadata::FETCH_EAGER);
    ->useResultCache(true, 900, 'categories')
    ->getResult();

I have 28 categories, 15 of them have parentId.
Above query executes 29 SQL queries, but Doctrine store in cache only 1, So when I run again this query, it executes 28 queries.

Any idea what am I doing wrong? missing some cache configuration? missing some methods in DQL? I would like to cache all queries not only one- main query.

Edit

I would like to use query result in loop, like this:

foreach($result as $row)
{
    $categories[]['attr'] = $row->getAttribute()->getName();
    $categories[]['value'] = $row->getAttribute()->getValue();
}

but this way cache won't work, so currently I'm using:

foreach($result as $row)
{
        $attributes = $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.id = :catId')
            ->setParameter('catId', $row['id'])
            ->getQuery()
            ->useResultCache(true, 900, $categoryName.'-attributes')
            ->getArrayResult();
}

But I would rather work on objects then on arrays, but I can't cuz if I use object and it has association then this association will not be cached. So ideally would be some way to cache object + ALL his associations.

Answer 1

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.