CODEWITHSUNDEEP
phpsymfonydoctrine-orm
eagleoneraptor
eagleoneraptor

Reputation: 1227

Check integrity constraint violation with Doctrine 2

I'm trying to clean a collection of entities (that is, a table, in the relational jargon), those entities are instances of Color, and I want to run a script that uses Doctrine 2 to delete all unreferenced Color's. The problem is that Color is referenced by many other entities in the system. So, I could think of two choices:

  1. Iterate over all colors and check manually for each one if exists references to a specific Color on other entities on my system, and, if there is no reference, delete it.
  2. Iterate over all colors and try to delete one, and, if there is a reference to that color, catch the exception trowed by EM caused by Integrity constraint violation, so, I do ignore the failed color and continue with next.

Obviously, the second choice is more simple than the first one, so I've tried that. The problem I had is that, when an entity removing fails, EM throw an exception, but also close the Entity Manager and I cannot use it anymore to delete the remaining colors!

I check commit method for UnitOfWork class at Doctrine 2 and in fact...

try {
    ... //Execute queries

    $conn->commit();
} catch (Exception $e) {
    $this->em->close();
    $conn->rollback();

    throw $e;
}

Is there any way to accomplish what I'm trying to do (maybe reopening Entity Manager)?
Do you believe that there is any better way?
Why Doctrine has this behaviour?

Also please note that I'm using Doctrine 2 with Symfony 2

Upvotes: 1

Views: 1941

Answers (2)

Jovan Perovic
Jovan Perovic

Reputation: 20191

Ideally, you would define orphanRemoval in your relations so you wouldn't need to bother with the removal. Other than that I can think of 3 solutions right now..

First, do you use CASCADE in ON DELETE when referencing colors table?

1)

If you do use CASCADE I would suggest implementing Doctrine's batch processing as described here: BULK DELETE but here your $batchSize would be exactly 1.

2)

I, somehow, see the above solution as not-so-efficient and you could also achieve this by:

  • Going through the entities that reference the Color entity and collect id of Color into the array.
  • If that array is not empty, execute: DELETE FROM AcmeDemoBundle:Color c WHERE c.id IN (:ids) and set ids parameter as usual.

3)

There is another solution if you do not use CASCADE. You could execute raw sql:

DELETE IGNORE FROM color;

Here, referenced colors would not be deleted and would fire an error but due to IGNORE it would end up only as warning.

Hope some of this helps....

Upvotes: 1

Zeljko
Zeljko

Reputation: 5158

Something like this:

// somewhere in ColorRepository.php

$this->createQueryBuilder("o")
    ->leftJoin("o.Relation1", "r1")
    ->leftJoin("o.Relation2", "r2)
    ->where("r1 IS NULL AND r2 IS NULL")
    ->delete() ;

This should load your entities (Color) only if they don't have relations with r1 and r2 anymore and delete them.

Upvotes: 0

Related Questions