Doctrine QueryBuilder - Exclude articles linked to

问题:

I'm working on a Symfony2 project using Doctrine2.

I have a 'article' and a 'theme' table in a many-to-many relationship. I am trying to get every articles except those linked to theme 35.

$query = $this->createQueryBuilder('art')
        ->join('art.themes', 'the')
        ->where('the != '.35)
        ->getQuery()
        ->getResult();

This request only works when my article has only one theme. If the article has more than one (for example theme 35 + theme 36), it's not excluded from the results.

How can I fix this?

Here is the request I want to use in SQL :

SELECT id, title, theme_id FROM article, article_theme WHERE article.id = article_theme.article_id AND 35 NOT IN (SELECT theme_id FROM article_theme WHERE article_id = article.id);

Thank you for your help!

回答1:

I ended up doing something like this :

// First we get all the articles with theme 35
$qbFirstStep = $this->getEntityManager()->createQueryBuilder();

$qbFirstStep->select('aa.id')
    ->from('AP\NewsBundle\Entity\Article', 'aa')
    ->leftJoin('aa.themes', 'the')
    ->where('the.id = 35');

// Then we get all articles where article.id not in ids of the first request
$qbFinal = $this->getEntityManager()->createQueryBuilder();

$qbFinal->select('bb')
    ->from('AP\NewsBundle\Entity\Article', 'bb')
    ->where($qbFinal->expr()->notIn('bb.id', $qbFirstStep->getDQL()));

return $qbFinal->getQuery()->getResult();

Surely not the cleanest way to do it but it works