inanzzz | Using a subquery within HAVING clause in doctrine QueryBuilder

Example below shows us how we can use a subquery within HAVING clause in QueryBuilder.

QueryBuilder

public function findMessagesOlderThanOneDayWithNoReplies()
{
    $qb0 = $this->createQueryBuilder('m0');
    $qb1 = $this->createQueryBuilder('m1');

    $qb1->select('IDENTITY(m1.inReplyTo)')
        ->where('m1.inReplyTo IS NOT NULL')
        ->groupBy('m1.inReplyTo');

    $qb0->select('m0.id, m0.senderId')
        ->where('m0.inReplyTo IS NULL')
        ->andWhere('m0.senderId IS NOT NULL')
        ->andWhere('DATE_DIFF(CURRENT_DATE(), m0.createdAt) > 1')
        ->groupBy('m0.senderId')
        ->having($qb0->expr()->notIn('m0.id', $qb1->getDQL()));

    echo $qb0->getDQL();

    return $qb0->getQuery()->getResult();
}

The function CURRENT_DATE() checks only yyyy-mm-dd part so if you want yyyy-mm-dd h:i:s then you are better of using part below as replacement.

->andWhere('DATE_DIFF(:now, m0.createdAt) > 1')
->setParameter('now', new DateTime('now'))

Without IDENTITY keyword, error below occurs.

[Semantical Error] line 0, col 261 near 'inReplyTo FROM': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

Result

SELECT m0.id,
       m0.senderId
FROM Application\WebsiteBundle\Entity\Message m0
WHERE m0.inReplyTo IS NULL
  AND m0.senderId IS NOT NULL
  AND DATE_DIFF(CURRENT_DATE(), m0.createdAt) > 1
GROUP BY m0.senderId
HAVING m0.id NOT IN
  (SELECT IDENTITY(m1.inReplyTo)
   FROM Application\WebsiteBundle\Entity\Message m1
   WHERE m1.inReplyTo IS NOT NULL
   GROUP BY m1.inReplyTo)

Actual aim

As you can see above, we have an extra check for GROUP BY m0.senderId in DQL. It is because Doctrine forces us to have it in test environment where the most people would use Sqlite database.

SELECT id, sender_id
FROM message
WHERE in_reply_to IS NULL
AND created_at <= NOW() + INTERVAL 1 DAY
HAVING id NOT IN (
    SELECT in_reply_to
    FROM message
    WHERE in_reply_to IS NOT NULL
    GROUP BY in_reply_to
)