MySQL:有没有一种方法可以在不过于详细的情况下更新多对多关联表?

huangapple go评论82阅读模式
英文:

MySQL: is there a way to update a many to many join table without getting really granular?

问题

我有一个问题,我有两个表之间的M:M关系,一个连接表将它们各自的id作为外键存储到它们各自表的主键中。

例如:

CREATE TABLE todo (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    title TEXT NOT NULL,
    slug TEXT NOT NULL,
    description VARCHAR(100) NOT NULL,
    user_id INT(10) UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE category (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    name TEXT,
    slug TEXT,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE todo_category (
    todo_id INT(10) UNSIGNED NOT NULL,
    category_id INT(10) UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`todo_id`, `category_id`),
    CONSTRAINT `f_todo_category_todo` FOREIGN KEY (`todo_id`) REFERENCES `todo` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `f_todo_category_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

我可以创建一个具有多个类别的待办事项。我还可以选择该待办事项,或者选择所有待办事项以及它们各自的类别 - 并且我可以只使用1-2个查询来完成这两个操作。

当我尝试更新一个todo时,我也可以使用REPLACE INTO查询在todo_category表上更新类别。我知道这将删除并重新创建这些行,但我认为在这种情况下这是可以接受的,因为通常不会有超过几个类别。然而,这种方法的问题是,如果没有添加新的类别,而是删除了一些类别,怎么办?这就是我的问题...

是否有一种标准的查询方法可以做到这一点(在一个查询中更新todo_category表,删除不存在的id,并添加新的id),或者这是我必须使用代码逻辑并为自己的情况执行多个查询的地方,例如下面的示例?

我目前尝试的方法如下(我认为这非常糟糕,因为它需要很多查询和很多代码行):

  1. 更新待办事项
  2. 选择刚刚更新的待办事项,以便稍后使用其ID
  3. 选择与#2中获取的ID对应的所有类别(我们从这些结果中创建一个已存在的类别ID数组)
  4. 选择具有与请求中发送的ID匹配的todo_id以及与ID匹配的类别ID的所有类别(这些将是表单发送的ID,用于更新此待办事项及其类别)
  5. 对比#3中的类别ID和#4中的类别ID,查看哪些是新的,哪些不再存在(已删除),并从这些结果创建两个新数组
  6. 执行INSERT查询,将新的类别添加到todo_category表以反映新的ID
  7. 执行DELETE查询,删除请求中不再存在于待办事项的类别

如你所见,这非常详细,并且需要很多查询,使得代码中的Update方法比其他所有方法都要大得多,这让我觉得有更好的方法。

编辑

这是我的代码,虽然不完整(仅供参考,但我认为这并不重要,因为我已经解释了我在上面做了什么):

// Update makes changes to an existing item.
func Update(db Connection, title string, description string, slug string, idsFromRequest category.IDList, userID string) (sql.Result, error) {
    // Update the todo
    newSlug := helper.StringToSlug(title, true)
    result, err := db.Exec(fmt.Sprintf(`
        UPDATE %v
        SET title = ?,
            slug = ?,
            description = ?
        WHERE slug = ?
            AND user_id = ?;
    `, table), title, newSlug, description, slug, userID)
    if err != nil {
        return result, err
    }

    // Get the todo to reference its ID
    t, _, err := BySlug(db, newSlug)
    if err != nil {
        return result, err
    }

    // Get the categories the todo currently has
    cats, _, err := category.ByTodoID(db, t.ID)
    if err != nil {
        return result, err
    }

    // Get the ids from all of the categories
    var idsAlreadyExisting []string
    for _, cat := range cats {
        idsAlreadyExisting = append(idsAlreadyExisting, cat.ID)
    }

    // Get the ids to be added (don't yet exist) and the ids to be deleted (not present in the request)
    idsToBeAdded, idsToBeDeleted := diff(idsAlreadyExisting, idsFromRequest)

    // Create the query for adding the category ids for the todo in todo_category
    query := `INSERT INTO todo_category (todo_id, category_id)`
    for i, id := range idsToBeAdded {
        if i == 0 {
            query += ` VALUES`
        }
        query += fmt.Sprintf(` (%s, %s)`, fmt.Sprint(t.ID), id)
        if i == len(idsToBeAdded) - 1 {
            query += ";"
        } else {
            query += ","
        }
    }
    result, err = db.Exec(query)

    // Delete query goes here...

    return result, err
}
英文:

I have a problem in which I have a M:M relationship between two tables, with a join table storing each of their ids as a foreign key to the primary key in their respective tables.

For example:

 CREATE TABLE todo (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
title TEXT NOT NULL,
slug TEXT NOT NULL,
description VARCHAR(100) NOT NULL,
user_id INT(10) UNSIGNED NOT NULL,
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (id)
);
CREATE TABLE category (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
name TEXT,
slug TEXT,
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (id)
);
CREATE TABLE todo_category (
todo_id INT(10) UNSIGNED NOT NULL,
category_id INT(10) UNSIGNED NOT NULL,
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`todo_id`, `category_id`),
CONSTRAINT `f_todo_category_todo` FOREIGN KEY (`todo_id`) REFERENCES `todo` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `f_todo_category_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

I can create a todo which has many categories. I can also select that todo, or all todos along with all of their respective categories -- and I can do both rather cleanly with only 1-2 queries.

When I try to UPDATE a todo, I can also update the categories using a REPLACE INTO query on the todo_category table. I know it will delete and create those rows again, but I think it is fine in this case because generally there won't be more than a hand full of categories. The problem with this approach, however, is that what if new categories weren't added, but were removed? This brings me to my question...

Is there a standard query for doing something like this (update the todo_category table to delete id's that aren't present, and add id's that are new -- in one query), or is this where I have to just use code logic and do multiple queries for my own case scenario, such as my example below?

How I'm currently attempting to do it is as follows (which I think is really bad because it requires many queries and many lines of code):

  1. UPDATE the todo
  2. SELECT the todo that we just updated in order to use its ID later
  3. SELECT all the categories corresponding to the ID we got from #2 (we create an array of already existing category ID's from these results)
  4. SELECT all categories with a matching todo_id from #2, as well as with matching category ID's from the ID's sent in the request (these would be the ID's sent by the form that is meant to update this todo and its categories)
  5. Do a diff on the category ID's from #3 and the category ID's from #4 to see what is new, what is no longer present (removed), and create two new arrays from those results
  6. Do an INSERT query to add the new categories in the todo_category table to reflect the new ID's
  7. Do a DELETE query to remove the categories that were no longer present in the request compared to the categories the todo currently has

As you can see, that is very granular and requires many queries as well as makes the Update method in the code on that Todo model very much larger than all of the other methods, and it makes me feel like there's a better approach.

EDIT

Here is my code, albeit incomplete (it's just for reference, though I don't think it matters much cause I explained what I'm doing above):

// Update makes changes to an existing item.
func Update(db Connection, title string, description string, slug string, idsFromRequest category.IDList, userID string) (sql.Result, error) {
// Update the todo
newSlug := helper.StringToSlug(title, true)
result, err := db.Exec(fmt.Sprintf(`
UPDATE %v
SET title = ?,
slug = ?,
description = ?
WHERE slug = ?
AND user_id = ?;
`, table), title, newSlug, description, slug, userID)
if err != nil {
return result, err
}
// Get the todo to reference its ID
t, _, err := BySlug(db, newSlug)
if err != nil {
return result, err
}
// Get the categories the todo currently has
cats, _, err := category.ByTodoID(db, t.ID)
if err != nil {
return result, err
}
// Get the ids from all of the categories
var idsAlreadyExisting []string
for _, cat := range cats {
idsAlreadyExisting = append(idsAlreadyExisting, cat.ID)
}
// Get the ids to be added (don't yet exist) and the ids to be deleted (not present in the request)
idsToBeAdded, idsToBeDeleted := diff(idsAlreadyExisting, idsFromRequest)
// Create the query for adding the category ids for the todo in todo_category
query := `INSERT INTO todo_category (todo_id, category_id)`
for i, id := range idsToBeAdded {
if i == 0 {
query += ` VALUES`
}
query += fmt.Sprintf(` (%s, %s)`, fmt.Sprint(t.ID), id)
if i == len(idsToBeAdded) - 1 {
query += ";"
} else {
query += ","
}
}
result, err = db.Exec(query)
// Delete query goes here...
return result, err
}

答案1

得分: 1

如果您在UPDATE语句中使用主键而不是使用sluguser_id,将会更容易一些。

todo-ID可以在表单提交时发送。由于所有的category-ID也都可以在表单提交时获取到,我可能会这样做:

  1. 使用主键而不是sluguser_idUPDATEtodo
  2. 在表categoryINSERT新的类别,这些类别在表单提交的category-ID数组中尚不存在。
  3. DELETEtodo_category中所有满足todo-ID等于提交的todo-ID的行。
  4. 在表todo_categoryINSERT使用表单提交的所有category-ID和todo-ID的行。

如果您需要清理并删除表categories中与任何todo-ID没有关联的类别,您可以最后执行一个查询来删除孤立的类别。

英文:

It would be much easier if you used a primary key in your UPDATE statement instead of using slug and user_id

The todo-ID could be sent on form submission. And since all category-ID:s also are available on form submission, I would probably do something like this:

  1. UPDATE the table todo using the primary key instead of slug and user_id
  2. INSERT new categories in table category that do not already exist compared to the array of category-ID:s submitted in the form
  3. DELETE ALL rows in table todo_category WHERE todo-ID = the submitted todo-ID
  4. INSERT rows in table todo_category with ALL the category-ID:s submitted from the form, together with the todo-ID

If you need to clean up and delete categories in table categories that do not have any relation to any todo-ID, you could finish of with a query to delete orphan categories.

huangapple
  • 本文由 发表于 2017年2月26日 06:47:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/42462503.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定