教义存在而又不存在 [英] Doctrine EXSIT and NOT EXIST

问题描述

我有需要转换为教义查询生成器的查询，但是遇到错误，如何正确执行?

I have query which I need to transform to doctrine query builder, but I faced with error, how to do it right ?

我的查询

SELECT id FROM category
WHERE 
  EXISTS(SELECT 1 FROM category_relations WHERE main_category_id = category.id)
  AND
  NOT EXISTS(SELECT 1 FROM category_relations WHERE sub_category_id = category.id)

如我所想

    $subYes = $this->getEntityManager()->createQueryBuilder();
    $subYes->select("1");
    $subYes->from(CategoryRelations::class,"cr_y");
    $subYes->andWhere('cr_y.mainCategory = c');

    $subNot = $this->getEntityManager()->createQueryBuilder();
    $subNot->select("1");
    $subNot->from(CategoryRelations::class,"cr_n");
    $subNot->andWhere('cr_n.subCategory = c');

    $qb = $this->createQueryBuilder('c');
    $qb
        ->select('c')
        ->where($qb->expr()->exists($subYes->getDQL()))
        ->andWhere($qb->expr()->not($subNot->getDQL()));


    $query = $qb->getQuery();
    $DQL = $query->getDQL();
    $SQL = $query->getSQL();
    $result = $query->getResult();

和错误

[Syntax Error] line 0, col 140: Error: Expected Literal, got 'SELECT'

更新

SELECT c FROM App\Entity\Category c 
WHERE EXISTS(SELECT 1 
FROM App\Entity\CategoryRelations cr_y WHERE cr_y.mainCategory = c) 
AND NOT(SELECT 1 FROM App\Entity\CategoryRelations cr_n WHERE cr_n.subCategory = c)

更新

我内部加入mainCategory和subCategory，但仍然遇到错误

I inner join mainCategory and subCategory but still faced with error

$subYes = $this->getEntityManager()->createQueryBuilder();
$subYes
    ->select("cr_y")
    ->from(CategoryRelations::class,"cr_y")
    ->innerJoin('cr_y.mainCategory', 'cr_ym')
    ->where($subYes->expr()->eq('cr_ym.id', 'c.id'));

$subNot = $this->getEntityManager()->createQueryBuilder();
$subNot
    ->select("cr_n")
    ->from(CategoryRelations::class,"cr_n")
    ->innerJoin('cr_y.subCategory', 'cr_nm')
    ->where($subNot->expr()->eq('cr_nm.id', 'c.id'));

$qb = $this->createQueryBuilder('c');
$qb
    ->select('c')
    ->where($qb->expr()->exists($subYes->getDQL()))
    ->andWhere($qb->expr()->not($subNot->getDQL()));


$query = $qb->getQuery();
$DQL = $query->getDQL();
$SQL = $query->getSQL();
$result = $query->getResult();

有我的实体

class Category

/**
 * @ORM\OneToMany(targetEntity="CategoryRelations", mappedBy="subCategory")
 * @ORM\Cache("NONSTRICT_READ_WRITE")
 */
private $subCategoryRelations;

/**
 * @ORM\OneToMany(targetEntity="CategoryRelations", mappedBy="mainCategory")
 * @Annotation\Groups({Category::SERIALIZED_GROUP_RELATIONS_LIST})
 * @ORM\Cache("NONSTRICT_READ_WRITE")
 */
private $mainCategoryRelations;

以及许多对许多 CategoryRelations

class CategoryRelations
{
    use TimestampableEntity;

    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="Category", inversedBy="subCategoryRelations", cascade={"persist"})
     * @Annotation\Groups({Category::SERIALIZED_GROUP_RELATIONS_LIST})
     * @ORM\Cache("NONSTRICT_READ_WRITE")
     */
    private $subCategory;

    /**
     * @ORM\ManyToOne(targetEntity="Category", inversedBy="mainCategoryRelations", cascade={"persist"})
     * @ORM\Cache("NONSTRICT_READ_WRITE")
     */
    private $mainCategory;

推荐答案

您可以使用查询生成器中的"not()/in()"方法添加所需的过滤器/约束，并且子句看起来像

You could use "not()/in()" methods from query builder to add your desired filters/constraints and your clauses would look like as

$subYes = $this->createQueryBuilder("cr")
                         ->select("cr.mainCategory")
                         ->from("CategoryRelations","cr")
                         ->getDQL();

$subNot = $this->createQueryBuilder("cr")
                          ->select("cr.subCategory")
                          ->from("CategoryRelations","cr")
                          ->getDQL();

$qb = $this->createQueryBuilder("c");
 $qb->select("c")
    ->from("Category", "c");
    ->where(
        $qb->expr()->not(
            $qb->expr()->in(
            "c.id",
            $subNot
            )
        )
    )
    ->andWhere(
        $qb->expr()->in(
        "c.id",
        $subYes
        )   
    );
$query = $qb->getQuery();
$DQL = $query->getDQL();
$SQL = $query->getSQL();
$result = $query->getResult();

参考:Doctrine Query Builder嵌套带有连接的orX和andX条件

这篇关于教义存在而又不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！