Doctrine QueryBuilder delete with joins

Question

I'm trying to use the Doctrine QueryBuilder to perform the following SQL query:

DELETE php FROM product_hole_pattern php
INNER JOIN hole_pattern hp ON php.hole_pattern_id = hp.id
INNER JOIN hole_pattern_type hpt ON hp.hole_pattern_type_id = hpt.id
WHERE php.product_id = 4 AND hpt.slug='universal';

I have this

$qb = $this->entityManager->createQueryBuilder();
$query = $qb->delete('\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();

but I get:

[Semantical Error] line 0, col 50 near 'hpt.slug = :slug': Error: 'hpt' is not defined.

The DQL that comes with the error message is:

DELETE \SANUS\Entity\ProductHolePattern php 
WHERE hpt.slug = :slug AND php.product=:product

So the joins seem to be omitted completely.

Answer 1

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.

Answer 2

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.