在CakePHP中更新和删除唯一连接关系 [英] Updating and removing unique join relationships in CakePHP
问题描述
编辑：我需要帮助的是删除topics_posts表中主题和帖子之间的所有关系，以便清除关系并删除旧关系。
在我的CakePHP应用程序中，我有一个帖子和主题（主题是唯一的，有一个ID），它们通过Topic_Posts相互链接，处理帖子和主题之间的关系。
In my CakePHP application I have Posts and Topics (topics are unique and have an id) and they are linked to each other via Topic_Posts which handles the relationships between a post and a topic.
用户编辑具有关系的帖子并保存它，而不是仅修改它将在Topic_posts表中复制的关系，并且如果用户从帖子中删除主题，则不会删除它们！
However if a user edits a post that has relationships and they save it, instead of just amending the relationships it will duplicate them in the Topic_posts table and also does not remove them if the user removes a topic from a post!
什么是最好的处理方法？我听说过关于删除该帖子的所有关系，然后重新添加它们是最干净，最好的方式来处理所述情况，但是我又该怎么做呢？
What is the best way of dealing with this? I have heard talk about removing ALL the relationships for that post and then re-adding them is the cleanest and best way to to deal with said scenario but again how do I do this?
这是处理主题保存的代码（它会检查主题是否重复），但不会检查它们是否为重复关系，也不会删除关系。
This is the code that handles the saving of the topics (which does check that topics are NOT duplicated) but doesn't check that they are duplicated relationships NOR does it remove relationships.
public function savePostTopics($postId, $topics)
{
// Explode the topics by comma, so we have an array to run through
$topics = explode(',', $topics);
// Array for collecting all the data
$collection = array();
foreach($topics as $topic)
{
// Trim it so remove unwanted white spaces in the beginning and the end.
$topic = trim($topic);
// Make it all lowercase for consistency of tag names
$topic = strtolower($topic);
// Check if we already have a topic like this
$controlFind = $this->find(
'first',
array(
'conditions' => array(
'title' => $topic
),
'recursive' => -1
)
);
// No record found
if(!$controlFind)
{
$this->create();
if(
!$this->save(
array(
'title' => $topic
)
)
)
{
// If only one saving fails we stop the whole loop and method.
return false;
}
else
{
$temp = array(
'TopicPost' => array(
'topic_id' => $this->id,
'post_id' => $postId
)
);
}
}
else
{
$temp = array(
'TopicPost' => array(
'topic_id' => $controlFind['Topic']['id'],
'post_id' => $postId
)
);
}
$collection[] = $temp;
}
return $this->TopicPost->saveMany($collection, array('validate' => false));
}
以下是关联：
Post.php
class Post extends AppModel
{
public $name = 'Post';
public $belongsTo = 'User';
public $hasMany = array('Answer');
// Has many topics that belong to topic post join table... jazz
public $hasAndBelongsToMany = array(
'Topic' => array('with' => 'TopicPost')
);
}
Topic.php
class Topic extends AppModel
{
public $hasMany = array(
'TopicPost'
);
}
TopicPost.php
class TopicPost extends AppModel {
public $belongsTo = array(
'Topic', 'Post'
);
}
编辑：我做了以下操作，使两列彼此独立：
I have done the following to make the two columns unique against each other:
`id` int(11) unsigned NOT NULL auto_increment,
`topic_id` int(11) NOT NULL,
`post_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_row` (`topic_id`,`post_id`)
但是当我做一个更新我得到一个SQL错误，所以基本上Cake不是正确处理这...如何解决这个问题，因为它只是部分解决了问题，通过防止数据被重复！另外，如何处理删除主题，因为我想从topic_posts删除关系，但不知道如何做。
but when I do an update I get a SQL error so basically Cake isn't handling this properly... How do I fix this issue as it only partly solves the problem by preventing the data being repeated! Also how do I deal with when removing topics as I want the relationship removing from topic_posts but no idea how to do it?
Database Error
Error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-107' for key 2
SQL Query: INSERT INTO `db52704_favorr`.`topic_posts` (`topic_id`, `post_id`) VALUES (2, 107)
推荐答案
首先，您不需要Topic_Posts中的列 id 。您应该删除它，而改为使用：
First of all you don't need the column id in Topic_Posts. You should remove it and instead use this:
`topic_id` int(11) NOT NULL,
`post_id` int(11) NOT NULL,
PRIMARY KEY (`topic_id`, `post_id`)
这将意味着添加到帖子中的每个主题只能添加一次（它实际上是您现在所拥有的，只有很多更新）。
This will mean that every topic that is added to a post can only be added once (it's effectively what you've got now, only a lot tidier).
您更新帖子时出现SQL错误的原因是您在Topic_Posts中的帖子中添加了每个主题，而不管它们之前是否存在。
The reason you're getting an SQL error when you update a post is because you're adding every topic to the post in Topic_Posts regardless of whether or not they existed before.
您的代码片段为：
- 对于用户已添加到帖子中的每个主题，执行以下操作：
- 如果主题不存在，请将其添加到主题表（您似乎忘记将其添加到Topic_Posts表格中）
- 如果该主题确实存在，请将其添加到Topic_Posts表（不检查它是否已存在）
- 如果主题不存在，请将其添加到主题表，然后将其添加到Topic_Posts表（因为它是Topic_Posts中的外键，必须创建它
- 如果此主题不存在... 如果在Topic_Posts表中不存在此帖子，
- For each topic the user has added to the post, do this:
- If the topic doesn't exist, add it to the Topic table and then add it to the Topic_Posts table (since it's a foreign key in Topic_Posts, you must create it in the Topic table first)
- If the topic does exist...
- If it doesn't exist for this post in the Topic_Posts table, add it, otherwise ignore it
另一种做法是
An alternative way of doing it to handle the removal of topics could be:
- 删除当前帖子的Topic_Posts中的所有行
- 对于用户已添加到帖子中的每个主题，执行以下操作：
- 如果主题不存在，请将其添加到主题表，
- 如果主题存在，请将其添加到Topic_Posts表中
您不必担心在Topic_Posts中检查该帖子是否存在主题因为第一步是删除该帖子的所有主题。
You don't need to worry about checking if a topic exists for that post in Topic_Posts this way as the first step is to remove all topics for that post.
希望有帮助。
这篇关于在CakePHP中更新和删除唯一连接关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
