Many to many relation with ON DELETE CASCADE with Symfony and Doctrine
Solution 1
I've never used the YAML
format to define my entities and relations so I don't know if it is the same, but with annotations the onDelete
option belongs to the @ORM\JoinColumn
annotation:
/**
* @var \AppBundle\Entity\Actor $actor
*
* @ORM\ManyToOne(targetEntity="Actor", inversedBy="fields")
* @ORM\JoinColumn(name="actor_id", referencedColumnName="id", nullable=false, onDelete="cascade")
*/
protected $actor = null;
PS: After a quick search I've found your answer: https://stackoverflow.com/a/8330495/5192753
Solution 2
You have to add 'JoinColumn' definitions in the 'joinColumns' and 'inverseJoinColumns' like this:
/**
* @var Collection|null
* @ManyToMany(targetEntity="...")
* @JoinTable(name="...",
* joinColumns={@JoinColumn(name="`...`", referencedColumnName="id", onDelete="CASCADE")},
* inverseJoinColumns={@JoinColumn(name="`...`", referencedColumnName="id", onDelete="CASCADE")}
* )
*/
There you can add onDelete cascade (DB level) definitions
shakaran
>>>English:<<< I am a student of Computer Engineering in Spain. I use Ubuntu and I like to support the world of free software. Currently developing a project on an online role-playing game as RPG-based web interfaces and aspirations motor 3D based Blender. The website of the project: www.apogeus.es Other projects: Tivion - A simple player for online TV streaming. www.shakaran.net/blog/tivion Quijost - A really cheap and professional hosting www.quijost.com >>>Spanish<<< Soy un estudiante de Ingeniería Informática en España. Utilizo Ubuntu y me gusta apoyar el mundo del software libre. Actualmente desarrollo un proyecto sobre un juego de rol online como RPG basado en interfaces web y con aspiraciones de motor en 3D basado en Blender. La pagina web del proyecto: www.apogeus.es Otros proyectos: Tivion - Un simple reproductor para canales de TV streaming. www.shakaran.net/blog/tivion Quijost - Un hospedaje realmente barato y profesional www.quijost.com
Updated on June 18, 2022Comments
-
shakaran almost 2 years
I want a simple many to many relation with Symfony and Doctrine. This is really a unidirectional one-to-many association can be mapped through a join table as the docs indicate I am using a YAML file for configure this with the following code:
In file Content.orm.yml:
manyToMany: comments: cascade: ["persist","remove"] onDelete: CASCADE options: cascade: remove: true persist: true #refresh: true #merge: true #detach: true orphanRemoval: false orderBy: null targetEntity: Comment joinTable: name: content_comments joinColumns: content_id: referencedColumnName: id inverseJoinColumns: comment_id: referencedColumnName: id unique: true
This produce the following SQL commands:
$ php app/console doctrine:schema:update --dump-sql | grep -i "comment\|content" CREATE TABLE comment (id INT AUTO_INCREMENT NOT NULL, text LONGTEXT NOT NULL, content_id INT NOT NULL, creation_date DATETIME NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; CREATE TABLE contents (id INT AUTO_INCREMENT NOT NULL, user INT DEFAULT NULL, user_id INT NOT NULL,file VARCHAR(255) DEFAULT NULL, INDEX IDX_B4FA11778D93D649 (user), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; CREATE TABLE content_comments (content_id INT NOT NULL, comment_id INT NOT NULL, INDEX IDX_D297CC584A0A3ED (content_id), UNIQUE INDEX UNIQ_D297CC5F8697D13 (comment_id), PRIMARY KEY(content_id, comment_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; ALTER TABLE contents ADD CONSTRAINT FK_B4FA11778D93D649 FOREIGN KEY (user) REFERENCES users (id); ALTER TABLE content_comments ADD CONSTRAINT FK_D297CC584A0A3ED FOREIGN KEY (content_id) REFERENCES contents (id); ALTER TABLE content_comments ADD CONSTRAINT FK_D297CC5F8697D13 FOREIGN KEY (comment_id) REFERENCES comment (id);
But as you can see, the FOREIGN KEY instructions doesn't have the parte "ON DELETE CASCADE", even I try to put all the YAML annotations that I found.
Because in code, I am trying to delete a "content" entity and all the "comments" associated with this code:
$comments = $content->getComments(); // Remove first the parent $entity_manager->remove($content); $entity_manager->flush(); // Remove the childs foreach($comments as $comment) { $entity_manager->remove($comment); } $entity_manager->flush();
This produce the following exception.
An exception occurred while executing 'DELETE FROM comment WHERE id = ?' with params [1]:\n\nSQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`bb2server`.`content_comments`, CONSTRAINT `FK_D297CC5F8697D13` FOREIGN KEY (`comment_id`) REFERENCES `comment` (`id`))
So, what I am doing wrong? Or how to force to Doctrine to put "ON DELETE CASCADE" in many to many relations?
My only dirty workaround it drop the SQL query and rebuild myself, but I need that Doctrine create the query in schema:update for avoid my patching:
mysql> show create table content_comments; +------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | content_comments | CREATE TABLE `content_comments` ( `content_id` int(11) NOT NULL, `comment_id` int(11) NOT NULL, PRIMARY KEY (`content_id`,`comment_id`), UNIQUE KEY `UNIQ_D297CC5F8697D13` (`comment_id`), KEY `IDX_D297CC584A0A3ED` (`content_id`), CONSTRAINT `FK_D297CC584A0A3ED` FOREIGN KEY (`content_id`) REFERENCES `contents` (`id`), CONSTRAINT `FK_D297CC5F8697D13` FOREIGN KEY (`comment_id`) REFERENCES `comment` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE content_comments DROP FOREIGN KEY FK_D297CC5F8697D13; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE content_comments ADD CONSTRAINT FK_D297CC5F8697D13 FOREIGN KEY (`content_id`) REFERENCES `contents` (`id`) ON DELETE CASCADE; Query OK, 10 rows affected (0.07 sec) Records: 10 Duplicates: 0 Warnings: 0
Edit: workaround. I need to put the onDelete: CASCADE under the JoinColumns
manyToMany: comments: cascade: ["persist","remove"] onDelete: CASCADE options: cascade: remove: true persist: true #refresh: true #merge: true #detach: true orphanRemoval: false orderBy: null targetEntity: Comment joinTable: name: content_comments joinColumns: content_id: referencedColumnName: id onDelete: CASCADE inverseJoinColumns: comment_id: referencedColumnName: id unique: true onDelete: CASCADE
-
FabienChn over 6 yearsThis doesn't work for many to many relationships when using @ORM\JoinTable instead of JoinColumn