Doctrine QueryBuilder delete with joins
It may be better to run a query with IN condition rather than iterating.
$ids = $this->createQueryBuilder('product')->join('..your joins..')->where('..your wheres..')->select('product.id')->getQuery()->getResult();$this->createQueryBuilder('product') ->where('product.id in (:ids)') ->setParameter('ids', $ids) ->delete() ->getQuery() ->execute();
- Benefits: runs faster, no need to iterate
- Drawbacks: you can't hook into preRemove
As to heated "where to put it" debate, dare to put it in the controller if you like. That's completely up to you. However, it may be more useful to you in the future if you land the code in the dedicated doctrine repository class. It should be very easy to do and makes it easy to change / maintain.
It looks like DQL doesn't support this sort of delete statement. The BNF from the Doctrine documentation indicates that a delete_statement
must take the form
delete_clause [where_clause]
Where delete_clause
is defined as:
"DELETE" "FROM" abstract_schema_name [["AS"] identification_variable]
So I can provide a schema and a where clause, but no joins.
A way to accomplish this might be to first query the entities you want to delete using the joins:
$qb = $this->entityManager->createQueryBuilder();$query = $qb->select('\SANUS\Entity\ProductHolePattern', 'php') ->innerJoin('php.holePattern', 'hp') ->innerJoin('hp.holePatternType', 'hpt') ->where('hpt.slug = :slug AND php.product=:product') ->setParameter('slug','universal') ->setParameter('product',$this->id) ->getQuery();$results = $query->execute();
And then delete the entities you found in the result:
foreach ($results as $result) { $this->entityManager->remove($result);}
Be sure to call
$this->entityManager->flush();
at the appropriate place in your application (typically the controller).