Symfony 2 DQL query to find unstranslated records

Question

I have to find unstranled records of one of my entity, let's call it Product.

I have settled one Product entity with all the translatable attributes and the locale (title/text/locale). I also have settled one ProductParent entity with all the attributes that don't need translation (image). ProductParent is linked to Product through a ManyToOne relation (Product being the owner of the relation).

Product looks like : id | title | text | parent_id | locale

ProductParent looks like : id | image

What I need now is being able to fetch which records in Product have not been translated into a given locale ('es'), 'en' being the 'default' locale. So I need to find all the records which only have a locale = en.

I've started a DQL query but it's not working at all and it's really not my "cup of tea".

SELECT a 
FROM ProductParent a
LEFT JOIN Product b
ON a.id = b.parent_id
WHERE b.locale
IN ("en", "es")
GROUP BY a.id
HAVING COUNT * < 2

Can anyone help ?

Answer 1

Using this query works for me :

public function findUnstranslated($default, $locale)
{
    return $this->createQueryBuilder('p')
        ->leftJoin('p.parent', 'parent')
        ->where('p.locale IN (:locale)')
        ->setParameter('locale', [$default, $locale])
        ->groupBy('parent.id')
        ->having('COUNT(parent.id) < 2')
        ->getQuery()
        ->getResult();
}