Doctrine EXSIT and NOT EXIST

Question

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

my query

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)

as I supposed

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

and error

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

UPDATE

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)

UPDATE

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

there is my entity

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;

and many to many 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;

DQL yes, when get SQL I faced with error, I updated my question — shuba.ivan, Jun 04 '20 at 16:43
your dql query has a part which essentially reads "WHERE ... AND NOT (subquery)", which semantically makes no sense, instead of a subquery, you have to have something that evaluates to a scalar - hence the error "exptected literal, got SELECT". your query builder code reflects the problem as well. — Jakumi, Jun 05 '20 at 11:12

score 0 · Answer 1 · answered Jun 07 '20 at 21:34

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

Reference: Doctrine Query Builder nested orX and andX conditions with join

Doctrine EXSIT and NOT EXIST

1 Answers1

Linked