CODEWITHSUNDEEP
phpmysqldoctrine-ormquery-builder
Chris Hanson
Chris Hanson

Reputation: 2083

Doctrine QueryBuilder delete with joins

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.

Upvotes: 22

Views: 25728

Answers (5)

stollr
stollr

Reputation: 7183

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

Upvotes: 1

kshishkin
kshishkin

Reputation: 584

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.

Upvotes: 23

Gabriel
Gabriel

Reputation: 1

On Symfony2 please try:

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

$em->flush();

Thats all.

Upvotes: -6

Danielle Suurlant
Danielle Suurlant

Reputation: 161

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

Upvotes: 3

Chris Hanson
Chris Hanson

Reputation: 2083

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.

Upvotes: 17

Related Questions