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…<\/p>\n\n\n\n
In pure SQL you would write something like this:<\/p>\n\n\n\n
DELETE \nFROM shopping_basket s \nWHERE s.last_updated < DATE_SUB(NOW(), INTERVAL 7 DAY) \nAND WHERE NOT EXISTS (\n SELECT * \n FROM shopping_basket_item i \n WHERE i.shopping_basket_id = s.id\n)<\/code><\/pre>\n\n\n\nBut how would you do that in a Symfony Repository method?<\/p>\n\n\n\n
Well, actually it’s pretty simple, and yet a bit tricky.<\/p>\n\n\n\n
The main part (the DELETE) is easy: say, we’re in the ShoppingBasketRepository <\/code>and create a method “deleteAllAbandonedAndEmpty<\/code>“<\/p>\n\n\n\npublic function deleteAllAbandonedAndEmpty()\n{\n return $this->createQueryBuilder('s')\n ->delete()\n ->andWhere('lastUpdated < :sevendaysago)\n ->setParameter('sevendaysago', new \\DateTime('-7 days'))\n ->andWhere(\/* hmm here the subquery - but how? *\/)\n ->getQuery()\n ->getResult()\n ;\n}<\/code><\/pre>\n\n\n\nWell, 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')<\/code> doesn’t do the trick. <\/p>\n\n\n\nWhy? Because this always includes the current Entity with 'x'<\/code> as it’s alias also into that subquery.<\/p>\n\n\n\nBut here comes the Repositories internal entity manager to help.<\/p>\n\n\n\n
As $this->createQueryBuilder('x')<\/code> is actually a shortcut and resolves to<\/p>\n\n\n\n$this->_em->createQueryBuilder()\n ->select($alias) \/\/ alias being the 'x' passed from $this->createQueryBuilder\n ->from($this->_entityName, $alias);<\/code><\/pre>\n\n\n\nBut in our subquery we want to set the from ourselves, so we simply have to avoid the shortcut and build it “from scratch” \ud83d\ude09 – 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<\/code> – (what exactly depends on your namespace for the Entities).
So our subquery-builder would look something like this:<\/p>\n\n\n\n$sqb = $this->_em->createQueryBuilder()\n ->select('i')\n ->from('App:ShoppingBasketItem', 'i')\n ->andWhere('i.shoppingBasket = \/* hmm, what exactly? *\/')\n;<\/code><\/pre>\n\n\n\nYes, 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 \ud83d\ude09 ): 's'<\/code><\/p>\n\n\n\nSo this is the complete method. Note, that we first had to finish the assignment of the $qb<\/code> variable before we can use it again for the ->expr<\/code> calls!<\/p>\n\n\n\npublic function deleteAllAbandonedAndEmpty()\n{\n $sqb = $this->_em->createQueryBuilder()\n ->select('i')\n ->from('App:ShoppingBasketItem', 'i')\n ->andWhere('i.shoppingBasket = s')\n ;\n\n $qb = $this->createQueryBuilder('s')\n ->delete()\n ->andWhere('lastUpdated < :sevendaysago)\n ->setParameter('sevendaysago', new \\DateTime('-7 days'))\n ;\n $qb \n ->andWhere(\n $qb->expr()->not(\n $qb->expr()->exists(\n $sqb->getDQL()\n )\n )\n )\n ;\n\n return $qb\n ->getQuery()\n ->getResult()\n ;\n}<\/code><\/pre>\n\n\n\nI 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)<\/code> not being equal to new \\DateTime('-7 days')<\/code> – that’s not what this is about \ud83d\ude09<\/p>\n\n\n