与带有Symfony和Doctrine的ON DELETE CASCADE的多对多关系 [英] Many to many relation with ON DELETE CASCADE with Symfony and Doctrine
问题描述
我希望与Symfony和Doctrine建立简单的多对多关系.这实际上是一个单向一对多关联，可以通过连接表映射为
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:
在Content.orm.yml文件中:
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
这将产生以下SQL命令:
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);
但是您可以看到，FOREIGN KEY指令没有"ON DELETE CASCADE"，即使我尝试放置找到的所有YAML注释.
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();
这会产生以下异常.
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`))
那么，我做错了什么?还是如何强迫教义在多对多关系中施加"ON DELETE CASCADE"?
So, what I am doing wrong? Or how to force to Doctrine to put "ON DELETE CASCADE" in many to many relations?
我唯一的肮脏解决方法是删除SQL查询并重新构建自己，但我需要Doctrine在schema:update中创建查询，以免打补丁:
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
解决方法.我需要将onDelete:CASCADE放在JoinColumns下
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
推荐答案
我从未使用过 YAML 格式来定义我的实体和关系，所以我不知道它是否相同，但是带有注释的 onDelete 选项属于 @ORM \ JoinColumn 注释:
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:快速搜索后，我找到了您的答案: https://stackoverflow.com/a/8330495/5192753
PS: After a quick search I've found your answer: https://stackoverflow.com/a/8330495/5192753
这篇关于与带有Symfony和Doctrine的ON DELETE CASCADE的多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
