2

I need some help understanding how cascade works with Symfony2 entity associations, in particular the "remove" option. Because when I try to remove an entity, there is a database foreign key conflict and it will not delete from the database.

I have two test entities:

/**
* @ORM\Entity
* @ORM\Table(name="test")
*/
class Test extends Entity\Base {

    /**
    * This is a OneToOne Unidirectional association, just so that we can get the
    * current published version easily, based on the publishedId.
    * @ORM\OneToOne(targetEntity="\Gutensite\CmsBundle\Entity\View\TestVersion")
    * @ORM\JoinColumn(name="publishedId", referencedColumnName="id")
    */
    protected $published;

    /**
     * @ORM\Column(type="integer", nullable=true)
     */
    protected $publishedId = NULL;

    /**
     * This is the regular OneToMany Bi-Directional Association, for all the versions.
 * @ORM\OneToMany(targetEntity="\Gutensite\CmsBundle\Entity\View\TestVersion", mappedBy="view", cascade={"persist", "remove"}, orphanRemoval=true)
 */
protected $versions;
}

/**
* @ORM\Entity
* @ORM\Table(name="test_version")
*/
class TestVersion extends Entity\Base {


    /**
    * @ORM\ManyToOne(targetEntity="\Gutensite\CmsBundle\Entity\View\Test", inversedBy="versions")
    * @ORM\JoinColumn(name="viewId", referencedColumnName="id")
    */
    protected $view;

    /**
    * The primary view entity that this version belongs to.
    * @ORM\Column(type="integer", nullable=true)
    */
    protected $viewId;

}

*Note: I originally had a bidirectional OneToOne relationship that had cascade={"persist", "remove"} but realized that wasn't needed. Either way, the problem is exactly the same.

In a controller, I create a test entity like this:

$view = new Test();
$version = new TestVersion();
// associate the version with the view
$view->addVersion($version);
// mark this version as the published version (this is where the problem happens, though)
$view->setPublished($version);
$em->persist($view);
$em->flush();

And this creates one primary record for test and one associated version in test_version:

|---------------------|
| table: test         |
|---------------------|
| id    | publishedId |
| 100   |   500       |
|---------------------|

|---------------------|
| table: test_version |
|---------------------|
| id    | viewId |
| 500   |   100       |
|---------------------|

*Note: the test_version.viewId is set in a manual line via my test.addVersion() method that calls $version->setView($this).

But when I go to remove the primary entity in test:

$view = $em->getRepository("GutensiteCmsBundle:View\Test")->find($request->query->get('id'));
$em->remove($view);
$em->flush();

It tries to delete the main test entity but fails because it is referenced by the test_version.viewId. 

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`cms`.`test_version`, CONSTRAINT `FK_635D070BCEB972AA` FOREIGN KEY (`viewId`) REFERENCES `test` (`id`))

And if I manually try to delete the test_version record in the database, it also fails with a similar error:

ERROR 1451: 1451: Cannot delete or update a parent row: a foreign key constraint fails (`cms`.`test`, CONSTRAINT `FK_D87F7E0C628CCE00` FOREIGN KEY (`publishedId`) REFERENCES `test_version` (`id`)) SQL Statement: DELETE FROM `cms`.`test_version` WHERE `id`='17'

Soooooo..... what is the point of having cascade={"remove"} if it doesn't actually delete the related entity (or the parent entity for that matter)?

The only way I can delete these records is if I unset the $view->publishId which breaks the foreign key association overlap.

$view = $em->getRepository("GutensiteCmsBundle:View\Test")->find($request->query->get('id'));
$view->setPublishedId(NULL);
$em->persist($view);
$em->flush();
$em->remove($view);
$em->flush();

So apparently this is caused because of an overlap between the OneToOne and the OneToMany associations. I can live with this necessity of unsetting the publishedId before deleting a view. But I would love feedback from an expert to know if this seems problematic. Or a suggestion for a better solution for those associations. However, I have other records (like multiple routes) that point to the single view entity, so that is why the versions are out on their own entity (also to keep the bulk of the data in another table for optimization). So my model seems quite logical, e.g. I do want a quick easy way to reference the published version. And I thought that a uni-directional relationship would not cause problems. But it does...

Chadwick Meyer
  • 7,041
  • 7
  • 44
  • 65
  • In order the cascade remove be effective I would suggest to set `onDelete="CASCADE"` instead; this will let the Database to handle it which is more reliable – Javad May 07 '14 at 21:39
  • Is it possible you are referencing an older version of Symfony and Doctrine? Because it seems like the current format for the annotation is cascade={"persist", "remove"} (which you can see on the Test entity), and as described here: http://docs.doctrine-project.org/en/2.0.x/reference/working-with-associations.html#transitive-persistence-cascade-operations Or is what you are suggesting some alternative? – Chadwick Meyer May 07 '14 at 22:05
  • 1
    No I am not referencing an older version, even in the link you posted it mentions you can use **onDelete** instead which will let it be handled by Database. *To rely on the database level cascade operations for the delete operation instead, you can configure each join column with the **onDelete** option. See the respective mapping driver chapters for more information.* – Javad May 07 '14 at 22:09
  • Even if you want to use current orphan removal, I think you need to set `cascade={"remove"}` in both side of relationship – Javad May 07 '14 at 22:10
  • I am unsure about setting cascade={"remove"} on the ManyToOne side, because if a TestVersion is removed I don't want it to delete the primary Test entity. But I admit, I don't really know what that means. I don't see examples of it being set on both sides of the relationship. So I assumed it was set only on the side you want to cascade down. Do you know of any documentation about this? It's not listed in the official docs anywhere I can see (it doesn't provide that much detail unfortunately...) – Chadwick Meyer May 07 '14 at 23:28
  • 1
    I don't know a perfect documentation for that but I can refer to [http://www.krueckeberg.org/notes/d2.html#cascade-and-associations] and the *Warning* Section below this link [http://www.krueckeberg.org/notes/d2.html#the-downside-to-entitymanager-find] which is a complex of all doctrine2 documentation; hopefully it helps – Javad May 08 '14 at 01:17
  • FYI, according to this answer, you do not want cascade={"remove"} on the child, or it will remove the parent: http://stackoverflow.com/questions/6328535/on-delete-cascade-with-doctrine2 He also provides useful distinction between the two methods onDelete="Cascade" (at the database level) and cascade={"remove"} (at the ORM work unit level). People say they prefer onDelete="Cascade" usually, but if you need other actions to be called before the item is deleted from the database, you use the other. But it sounds like they should do the same thing, just depends what is doing the deleting. – Chadwick Meyer May 08 '14 at 18:43

0 Answers0