Howto: Symfony doctrine subquery exists

In SQL Subqueries are pretty common: you need only those records where a certain condition is dependend on the existance or non-existance of records in an associated table.
I.e. you want to delete all shoppingbaskets in the database, which are older than, let’s say 7 days but only if they do not have any items in them…

In pure SQL you would write something like this:

DELETE 
FROM shopping_basket s 
WHERE s.last_updated < DATE_SUB(NOW(), INTERVAL 7 DAY) 
AND WHERE NOT EXISTS (
    SELECT * 
    FROM shopping_basket_item i 
    WHERE i.shopping_basket_id = s.id
)

But how would you do that in a Symfony Repository method?

Well, actually it’s pretty simple, and yet a bit tricky.

The main part (the DELETE) is easy: say, we’re in the ShoppingBasketRepository and create a method “deleteAllAbandonedAndEmpty“

public function deleteAllAbandonedAndEmpty()
{
    return $this->createQueryBuilder('s')
        ->delete()
        ->andWhere('lastUpdated < :sevendaysago)
        ->setParameter('sevendaysago', new \DateTime('-7 days'))
        ->andWhere(/* hmm here the subquery - but how? */)
        ->getQuery()
        ->getResult()
    ;
}

Well, you have to create another QueryBuilder for the subquery and insert its DQL as an expression in the 2nd andWhere clause.
And here it becomes a bit tricky, as a simple $this->createQueryBuilder('x') doesn’t do the trick.

Why? Because this always includes the current Entity with 'x' as it’s alias also into that subquery.

But here comes the Repositories internal entity manager to help.

As $this->createQueryBuilder('x') is actually a shortcut and resolves to

$this->_em->createQueryBuilder()
     ->select($alias) // alias being the 'x' passed from $this->createQueryBuilder
     ->from($this->_entityName, $alias);

But in our subquery we want to set the from ourselves, so we simply have to avoid the shortcut and build it “from scratch” 😉 – But what exactly do we have to put in instead of $this->_entityName? To be honest, here PHPStorm came to my help, and after I startet typing the classname, suggested the correct syntax to me: App:ShoppingBasketItem – (what exactly depends on your namespace for the Entities).
So our subquery-builder would look something like this:

$sqb = $this->_em->createQueryBuilder()
     ->select('i')
     ->from('App:ShoppingBasketItem', 'i')
     ->andWhere('i.shoppingBasket = /* hmm, what exactly? */')
;

Yes, next big question, in the original SQL we simply compared the shopping_basket_id from the shopping_basket_item table with the id from the shopping_basket table, but in the doctrine QueryBuilder we don’t handle the ids explicitly, we rather handle objects. So in this case it’s actually rather simple: we use the alias of the parent table (um, Entity 😉 ): 's'

So this is the complete method. Note, that we first had to finish the assignment of the $qb variable before we can use it again for the ->expr calls!

public function deleteAllAbandonedAndEmpty()
{
    $sqb = $this->_em->createQueryBuilder()
        ->select('i')
        ->from('App:ShoppingBasketItem', 'i')
        ->andWhere('i.shoppingBasket = s')
    ;

    $qb = $this->createQueryBuilder('s')
        ->delete()
        ->andWhere('lastUpdated < :sevendaysago)
        ->setParameter('sevendaysago', new \DateTime('-7 days'))
    ;
    $qb 
        ->andWhere(
            $qb->expr()->not(
                $qb->expr()->exists(
                    $sqb->getDQL()
                )
            )
        )
   ;

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

I hope you get the idea and can adopt it to your requirements – and please don’t start an argument about DATE_SUB(NOW(); INTERVALL 7 DAY) not being equal to new \DateTime('-7 days') – that’s not what this is about 😉

This post is also available in: German