Doctrine Translatable how to only fetch records with a translation

Question

I'm using Symfony2 + Doctrine + Translatable from DoctrineExtensions.

I have an entity article which has a couple of translatable fields like Title and Content. What is happening now is that I've created a bunch of articles in Chinese language, but have not added a translation for English. This is giving me some problems when I try to display a top 8 most recent articles on my homepage... When I view my homepage in English, it will show a bunch of title-less articles there (the Chinese articles that dont have a translation in English).

I found a solution for this in the Translatable extension by using ORM query Hint: "\Doctrine\ORM\Query::HINT_CUSTOM_OUTPUT_WALKER".

This lets you directly query the translated fields of your entity which are actually stored in ext_translations and not in the entity's table.

As a result the code I use to fetch the articles for the homepage looks like this:

 public function getNewestArticles($limit = 1){
    $dql = 
    "SELECT a FROM NewsBundle:Article a 
     WHERE a.published = 1 
     AND a.title != ''
     ORDER BY a.created_at DESC";
    $query = $this->_em->createQuery($dql);
    $query->setMaxResults($limit);
    $query->setHint(
        \Doctrine\ORM\Query::HINT_CUSTOM_OUTPUT_WALKER,
        'Gedmo\\Translatable\\Query\\TreeWalker\\TranslationWalker'
    );

    $result = $query->getResult();

    return $result;
}

This actually works, but the generated query is so inefficient that it slows down the page a lot... I'm talking 1800 ms for that query. I've also tried to just put the fields I need in the select statement, which improves the performance, but still not enough.

Any ideas?

score 0 · Answer 1 · answered Apr 04 '14 at 08:56

you can try to optimize the query manually. use locale as the parameter in your repository

public function getNewestArticles($locale, $limit = 1){
   //your own superfast query
}

in the controller then set the locale with

$locale = $request->getLocale();
$em->getNewestArticles($locale, $limit);

additional question: have you set indexes on your tables?

Doctrine Translatable how to only fetch records with a translation

1 Answers1