0

I have a Symfony3 project using Doctrine 2.6 to manage the database.

I get an Integrity constraint violation error when I call the remove method the EntityManager in a certain order.

I've always considered this method (as well as persist) to work no matter in which order entities are passed to them, I was wrong.

The database runs on MySQL and has the following structure:

Database schema

The goal is to remove a Menu entity. I've simplified the case so the Menu is only linked to one Offer, and one Component.

The offer is linked to the same Component as the menu.

So it looks like this:

- Menu [#12 Menu Of the Day]
|  - Component [#23 Dessert]
|  |  - Product [#14 Pineapple]
|  - Offer [#42 Dessert only]
|  |  - Component [#23 Dessert]
|  |  |  - Product [#14 Pineapple]

Now this is the code that crashes :

$em = $this->doctrine->getManager();
$menu = $em->getRepository(Menu::class)->findOneBy(['ref' => 'V0dQ']);

// Remove menu components (ex: starter, dish, dessert, etc.)
$menuComponents = $menu->getComponents();
$menuComponent = $menuComponents[0];
$menu->removeComponent($menuComponent);

// Remove components products
$componentsProducts = $menuComponent->getComponentProducts();
$componentProduct = $componentsProducts[0];
$menuComponent->removeComponentProduct($componentProduct);

// Remove offers (ex: start + dish, dish + dessert, etc.)
$offers = $menu->getOffers();
$offer = $offers[0];
$menu->removeOffer($offer);

// Same as menu components
$offerComponents = $offer->getComponents();
$offerComponent = $offerComponents[0];
$offer->removeComponent($offerComponent);

// Everything MUST be persisted/removed in one place.
$em->remove($componentProduct);
$em->remove($menuComponent);
$em->remove($offer);
$em->remove($offerComponent);
$em->remove($menu);

$em->flush();

I works by putting the call to remove($menuComponent) after the call to remove($offer), like so: 

$em->remove($componentProduct);
$em->remove($offer);
$em->remove($menuComponent);
$em->remove($offerComponent);
$em->remove($menu);

This is a raw example of the problem. In reality I have a pretty complex "Api engine" (like Api Platform) in which everything is handled in a generic way.

I need a clear understanding of what happens to abstract a solution to any case in the future, that's where I call for your help.

Do you see a simple rule to determine which entity must be passed to remove() first?

Stnaire
  • 1,100
  • 1
  • 18
  • 30
  • Pls read all the answers in the duplicate topic, they describe different options on handling deletetions in multiple tables with parent-child relationships. – Shadow Jan 05 '18 at 23:46
  • Thanks but the question you reference is a simple OneToMany relationship where the author inverted the foreign key. I have no problem with this kind of relations. The problem here is that I have two entities referencing the same one (Component) and I can't figure out a logical way to determine which one should be removed first. When removing the menu, should I remove the component or the offer first ? If I choose the offer, it works, otherwise it crashes. That's not the same problem. – Stnaire Jan 06 '18 at 08:11
  • Nope, you have the same simple parent- child relationship, just you have multiple of them. The same rules apply as in the other case. – Shadow Jan 06 '18 at 08:27
  • You're right, it's only a matter of finding which entity should be removed first. It still blows my mind that Doctrine doesn't do it internally, but whatever. I've made a function in my Api to order generically any set of entities based on their relations, I would have posted the code in case it can help others but I can't post an answer. It may be because it is marked as duplicate.. – Stnaire Jan 06 '18 at 10:50
  • I sugges you to read the answers in the duplicate question. There are db level solutions that can help you. – Shadow Jan 06 '18 at 11:25
  • The question you keep referring to is about setting foreign keys in mysql. I do not set the foreign keys manually, Doctrine does it when the schema is updated. And as I already said, the author of the question inverted its foreign keys, and 90% of the answers are about correcting that, the other 10% are about disabling foreign keys checks, I don't see how it helps. And again, I've already solved my problem by ordering the entities manually before calling `remove()`, and I can't post an answer because of the duplicate thing that I still don't understand. – Stnaire Jan 07 '18 at 09:22

0 Answers0