Doctrine QueryBuilder - 排除与特定主题相关的文章 [英] Doctrine QueryBuilder - Exclude articles linked to specific themes

问题描述

我正在使用Doctrine2进行Symfony2项目。

I'm working on a Symfony2 project using Doctrine2.

我有一个多对多关系的文章和主题表。
我试图获得除主题35之外的所有文章。

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();

只有当我的文章只有一个主题时，此请求才有效。如果文章不止一个（例如主题35 +主题36），则不会从结果中排除。

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.

我如何解决这个问题？

这是我要在SQL中使用的请求：

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);

感谢您的帮助！

推荐答案

我最终做了这样的事情：

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 - 排除与特定主题相关的文章的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！