Too many db queries with collectionOperations

[CHenzel]

First of all thank you for this good bundle.

It works very well.

But looking more closely at the number of doctrine queries via the symfony profiler.
I notice that there are a lot of duplicate requests when we get the translations.

Example: if the collection contains 6 elements, we have 6 times the same request.

SELECT t0.id AS id_1, t0.title AS title_2, t0.content AS content_3, t0.meta_description AS meta_description_4, t0.seo_title AS seo_title_5, t0.slug AS slug_6, t0.locale AS locale_7, t0.translatable_id AS translatable_id_8 FROM blog_post_translation t0 WHERE (t0.locale = ? AND t0.translatable_id = ?)
Parameters:
[▼
  "en"
  1
]

I had the same problem with Gedmo\Translatable and I used the TranslationWalker. Would it be possible to use something equivalent?

use Doctrine\ORM\Query;
use Gedmo\Translatable\Query\TreeWalker\TranslationWalker;
...
$query->setHydrationMode(TranslationWalker::HYDRATE_OBJECT_TRANSLATION);
$query->setHint(Query::HINT_REFRESH, true);

Thanks

[antonioperic]

@CHenzel do you maybe have some solution for this?

[antonioperic]

@CHenzel ping for this one

[CHenzel]

@antonioperic
Make an TranslationWalker is too complex

It's more easier to make that manually

Some solutions with api-platform

Create an HydrationExtension

final class HydrationExtension implements ContextAwareQueryCollectionExtensionInterface, QueryItemExtensionInterface
{
    private $patranslator; //Locastic\ApiPlatformTranslationBundle\Translation\Translator

    public function __construct(
        PaTranslator $translator,
    ) {
....
        $this->patranslator = $translator;
....
    }
    /**
     * {@inheritdoc}
     */
    public function applyToCollection(QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null, array $context = [])
    {
        $this->addHydrationToCollection($queryBuilder, $resourceClass, $operationName, $context);
    }

    /**
     * {@inheritdoc}
     */
    public function applyToItem(QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, array $identifiers, string $operationName = null, array $context = [])
    {
        $this->addTranslationHydrationToItem($queryBuilder, $resourceClass, $operationName);
    }

    /**
     * @param QueryBuilder $queryBuilder
     * @param string       $resourceClass
     * @param string       $operationName
     * @param mixed        $context
     */
    private function addTranslationHydrationToCollection(QueryBuilder $queryBuilder, string $resourceClass, string $operationName, $context)
    {
        $locale = $this->patranslator->loadEntityCurrentLocale($resourceClass);
        $rootAlias = $queryBuilder->getRootAliases()[0];
        if (
            BlogPost::class === $resourceClass ||
           Tag::class === $resourceClass ||
           ......
        ) {
            $queryBuilder->addSelect('t');
            //Manage filter
            $findFilterTranslations = false;
            if (isset($context['filters']) && is_array($context['filters'])) {
                foreach ($context['filters'] as $key => $value) {
                    if (Utils::starts_with($key, 'translations.')) {
                        $findFilterTranslations = true;
                        break;
                    }
                }
            }
            if ($findFilterTranslations ) {
                $queryBuilder->leftJoin($rootAlias.'.translations', 't', Join::WITH, 't.locale = :locale');
                $queryBuilder->setParameter('locale', $locale);
            } else {
                $queryBuilder->leftJoin($rootAlias.'.translations', 't', Join::WITH);
            }
            //You can join other object link to translations
            if (Tag::class === $resourceClass) {
                $queryBuilder->addSelect('ti');
                $queryBuilder->leftJoin('t.image', 'ti', Join::WITH);
            }
        }
    }

    /**
     * @param QueryBuilder $queryBuilder
     * @param string       $resourceClass
     * @param string       $operationName
     */
    private function addTranslationHydrationToItem(QueryBuilder $queryBuilder, string $resourceClass, string $operationName)
    {
        $rootAlias = $queryBuilder->getRootAliases()[0];
        if (
            BlogPost::class === $resourceClass ||
            Tag::class === $resourceClass ||
            .....
        ) {
            $queryBuilder->addSelect('t');
            $queryBuilder->leftJoin($rootAlias.'.translations', 't', Join::WITH);

            if (Tag::class === $resourceClass) {
                $queryBuilder->addSelect('ti');
                $queryBuilder->leftJoin('t.image', 'ti', Join::WITH);
            }
            ......
        }
    }

    /**
     * @param QueryBuilder $queryBuilder
     * @param string       $resourceClass
     * @param string       $operationName
     * @param mixed        $context
     */
    private function addHydrationToCollection(QueryBuilder $queryBuilder, string $resourceClass, string $operationName, $context)
    {
        $this->addTranslationHydrationToCollection($queryBuilder, $resourceClass, $operationName, $context);
    }
}

Other tips : when doctrine need to automatically JOIN missing values (translations), you can make two queries to pre-hydrate what doctrine needs on the second query.

Example nested Tree with translations

...
        //First Query to get childs with translations
        $queryBuilder = $repo->createQueryBuilder('ca')
        ->select('ca', 't')
        ->leftjoin('ca.translations', 't')
        ->where('ca.parent IS NOT NULL')
        ...;
        $query = $queryBuilder->getQuery();
        $query->getResult(); //Results will be not use but pre-hydrate childs translations for second query
....
       //Second Query 
        $queryBuilder = $repo->createQueryBuilder('ca')
        ->where....;

        $query = $queryBuilder->getQuery();
        $tree = $query->getResult('tree'); //Result return by controller

We have just two queries in symfony profiler and if we use redis cache it's zero.
It's not the best solutions but it's working