Top level EXISTS query using Doctrine ORM

Tags: , ,



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.

Thanks for your help!

Answer

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


Source: stackoverflow