TranslationWalker does not run when Query Cache is enabled

[flip111]

I had a bug with translatable, it was firing queries even though registering the walker and hinting the right locale was done properly. After tracing the problem i came to the conclusion that Query Caching should be disabled for queries using Translatable.
To disable the query cache on a query: $query->useQueryCache(false);

Reasoning:
If query cache is used the Parser is never invoked:
https://github.com/doctrine/doctrine2/blob/5c44dd38234f2e143e376ac94d2e7f945d7f9328/lib/Doctrine/ORM/Query.php#L248

If the Parser does not run then no custom output walkers are checked:
https://github.com/doctrine/doctrine2/blob/4f585a3d6372ef1115a30663fe0005f611decd76/lib/Doctrine/ORM/Query/Parser.php#L357

I think the difference between a HINT_CUSTOM_TREE_WALKERS (SQL Walker) and HINT_CUSTOM_OUTPUT_WALKER (output walker) is that the first changes the actual DQL query and the latter always has to be done afterwards.

This is possibly a limitation of Doctrine

To do: gedmo translatable should automatically turn off query caching when it needs to register an output walker

[sfblaauw]

You solved?
Do you use:

$query->setHint(
    \Gedmo\Translatable\TranslatableListener::HINT_TRANSLATABLE_LOCALE,
    'en' // take locale from session or request etc.
);

[flip111]

solved?: used workaround to disable cache
used hint?: yes "and hinting the right locale was done properly"

[acasademont]

Yep, same here. Even though the query is executed correctly with the translation joins the "skipOnLoad" method is not called and the translation objects are fetched...

[l3pp4rd]

hints are used in cache key, it also is mentioned in the documentation. should be clear, read carefully

[acasademont]

@l3pp4rd I have already read the docs, and I have even contributed extensively in the past to the Translatable component and I perfectly know how it works, no need to be rude here.

It has nothing to do with hints. When the query is cached the custom translatable hydrators are not executed (these hydrators are set on the TranslationWalker and the walker is obviously never run when the query is already cached) and therefore the setSkipOnLoad method is not called and the TranslatableListener does again all the work.

The workaround is to manually set the custom translatable hydrators when executing the query.

So I would kindly ask you to reopen the issue.

[flip111]

@l3pp4rd
I believe only the original query is cached (inside the querycache, not the resultcache). The post-processing done by hints on the query yields a new query ... and so i think this new query (with join for translate table) is NOT cached in the query cache. If this is not true hints are used in cache key please be more explicit to the actual code where the modified query is cached.

@acasademont i don't understand your workaround, what do you mean with custom translatable hydrator ?

[acasademont]

When the query is run without caching, the TranslatableWalker fires and this walker is responsible for appending all the necessary joins. Besides, it also changes the default ObjectHydrator by a custom ObjectHydrator. This custom ObjectHydrator the only thing that does is a call to setSkipOnLoad on the TranslatableListener.

When the postLoad event in the TranslatableListener kicks in it checks for this skipOnLoad property. If the property is not set, it will run the normal behaviour of querying the translation table to translate the entity. When we already have the needed information in the query results thanks to the walker sql statements there's no need to perform these actions, that's where the performance improves A LOT.

BUT, if the query is cached, the TranslationWalker is not run, and the custom ObjectHydrators are not set and the skipOnLoad property is not set and we run the queries again. This is a performance disaster as we are already joining the translation tables AND fetching this info again.

[flip111]

@acasademont ah yes, i remember now (given i made this issue 1.5 years ago). I think your workaround is better than mine since you can still use the querycache. Perhaps a nice idea if you could write a small snippet how to set the custom hydrator, that way other people can benefit (i'm not working with Doctrine anymore myself).

And about performance ... yes this is the major issue. The amount of queries this generate is huge and definitively noticeable in normal use.

[l3pp4rd]

@acasademont hm misunderstood the issue. probably we need a better way to deal with skipOnLoad behavior if that is a cause in general to reload the field translations. as far as I remember the older versions of ORM were not causing this problem.

[pmishev]

Are there any new ideas regarding this issue?
In certain scenarios the workaround proposed by @acasademont for setting custom hydrator when you execute the query is not feasible, because you have no control over the query. An example is when using Symfony forms:

        $builder->add('sectorLinks', EntityType::class, [
            'class'         => 'AppBundle:Sector',
            'query_builder' => function (EntityRepository $er) {
                return $er->createQueryBuilder('s')->orderBy('s.left', 'ASC');
            }

When executing the query, the queryCache is hit and the custom object hydrator is not set.

I could set globally query_cache_driver: void, which would solve this problem, but turning off the query cache would have performance implications on its own, so I don't consider it a solution.

[jrmyio]

Just stumbled across this problem using Doctrine ORM version v2.5.4 and DoctrineExtensions v2.4.1.
Sadly took me 4h to find out I had to add this one liner :

$query->useQueryCache(false);

I was querying the ORM with translations (using StofDoctrineExtensions) where the request locale was not the same as the locale in the query hint. The first query worked, the ones after that didn't get the correct translation (they defaulted to the request locale).

On all queries I used:

     $query->setHint(\Gedmo\Translatable\TranslatableListener::HINT_TRANSLATABLE_LOCALE, $locale);
     $query->setHint(
                \Doctrine\ORM\Query::HINT_CUSTOM_OUTPUT_WALKER,
                'Gedmo\\Translatable\\Query\\TreeWalker\\TranslationWalker'
            );