Doctrine QueryBuilder - Exclude articles linked to specific themes

Question

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!

Can you just retrieve your desired result like this? `$res = $this->getDoctrine()->getRepository()->findBy(array('theme_id' != 35));` — matt_jay, Aug 04 '14 at 19:05
there is no field 'theme_id' on article, I'm using a third table (article <-> article_theme <-> theme) — gobtronic, Aug 05 '14 at 07:37

score 1 · Accepted Answer · answered Aug 06 '14 at 07:53

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

Doctrine QueryBuilder - Exclude articles linked to specific themes

1 Answers1

Linked