MySQL：有没有一种方法可以更新多对多联接表而又不会变得很细致？

Question

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
}

Answer 1

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.