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.

Sophomore

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.

Ahrendt

This is a pretty old question and I can just barely remember posting it. I agree that this approach is much faster. I don't really understand where the idea of putting the code into the controller came from. Location was not part of my original question. I'm switching this to the accepted answer. – Sophomore

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.

Sophomore

Answer 3

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).

Ostraw

Just two things to mention. 1. This can be very expensive. 2. Don't dare to put such a logic into a controller! – Perfumery

3rd thing - This is prone to race conditions. – Hypersensitive

The whole point of building a delete query is so you don't have to hydrate all the objects you're trying to delete and looping over them. So this can be very expensive depending on how many objects you're trying to delete... – Neat

Answer 4

@chris-hanson already pointed out that Doctrine doesn't support delete statements with joins (because it is no standard SQL).

But I found a solution to achieve something similar with one query, by using a delete statement with a subquery.

$query = $this->getEntityManager()->createQuery(<<<DQL
    DELETE FROM \SANUS\Entity\ProductHolePattern outer_php
    WHERE outer_php.id IN (
        SELECT php.id FROM \SANUS\Entity\ProductHolePattern php
        INNER JOIN php.holePattern hp
        INNER JOIN hp.holePatternType hpt
        WHERE hpt.slug = :slug AND php.product = :product
    )
DQL);
$query->setParameter('slug', 'universal');
$query->setParameter('product', $this->id);
$query->execute();

The advantage compared to the solution of @kshishkin is the reduction to one query and you do not have to pass the result of the first query as parameters to the second.

The latter may be problemattic if you get a lot of IDs as result from the first query, which have to be transferred from the database server to PHP, processed by PHP and then transferred back to the server.

It is much faster if the database can handle those IDs internally.

The query can also be generated by the query builder, but building subqueries with it is very cumbersome ;-) (if you need an idea how to do it, you may look here).

Binford

Answer 5

On Symfony2 please try:

foreach ($results as $result) {
  $em->remove($result);
}

$em->flush();

Thats all.

Welch

In cases where you need a more nuanced delete (e.g. the original question), this default $em->remove($entity) method won't work. – Opportina