Top level EXISTS query using Doctrine ORM

Question

I have a query such as this in plain SQL (of course the original is more complex, but still simple enough that I'm quite sure that that part is correct at least):

SELECT EXISTS (SELECT 1 FROM mytable WHERE 1 = 1)

The question is: How do I do this using Doctrine ORM DQL?

My current state looks as follows:

$queryBuilder = $this->em->createQueryBuilder();
$subQueryBuilder = $this->em->createQueryBuilder();

$subQueryBuilder
    ->select('1')
    ->from(MyEntity::class, 'b')
    ->where($subQueryBuilder->expr()->eq('1', '1'))
;

return (bool) $queryBuilder
    ->select('EXISTS(' . $subQueryBuilder->getDQL() . ')')
    ->getQuery()
    ->getSingleScalarResult()
;

This will throw a parsing error, as EXISTS is an unknown function (and there is no built-in function to cover this). Using a native query didn't work either, but I might have messed this up, so am grateful for a correct example including a result set mapping.

There are answers for similar questions on SO, but I haven't found this exact problem.

Query with EXISTS for Doctrine Symfony2 (answers the case where EXISTS is used in the WHERE part)
Doctrine2 DBAL Exists query answers the right question but only for Doctrine DBAL, not ORM.

Thanks for your help!

score 0 · Accepted Answer · answered Jan 19 '21 at 09:26

OK, despite feeling a bit wrong, I solved the problem by using a native query. Here it is for reference (still open for improvement though):

$subQueryBuilder = $this->em->createQueryBuilder();

$subQueryBuilder
    ->select('1')
    ->from(MyEntity::class, 'b')
    ->where($subQueryBuilder->expr()->eq('foo', '?'))
;

$rsm = new ResultSetMappingBuilder($this->em);
$rsm->addScalarResult('x', 'x', 'boolean');

$query = $this->em
    ->createNativeQuery('SELECT EXISTS(' . $subQueryBuilder->getQuery()->getSQL() . ') as x', $rsm)
;

return (bool) $query
    ->setParameter(1, 'bar')
    ->getSingleScalarResult()
;

Evgeny Ruban · Answer 2 · 2022-07-01T16:42:42.740

Found a bit better way and it supports complex criterias also

protected function existsBy(array $criteria): bool
{
    $entityPersister = $this->entityManager->getUnitOfWork()->getEntityPersister($this->entityClass);
    $subSql = $entityPersister->getSelectSQL($criteria);
    $subSql = \preg_replace('/^(SELECT).*(FROM)/', '$1 1 $2', $subSql);

    [$params, $types] = $entityPersister->expandParameters($criteria);

    return $this->entityManager->getConnection()
        ->executeQuery('SELECT EXISTS(' . $subSql . ')', $params, $types)
        ->fetchOne();
}

Where $this-entityClass is a FQCN of the class, like Entity::class

Top level EXISTS query using Doctrine ORM

2 Answers2