如何用GORM更新多记录？电影分类关联数据更新异常求助

问题现象 #

我需要更新movie_genre表中电影对应的分类数据，每部电影对应多个genre_id，因此表中存在多条movie_id相同但genre_id不同的记录。使用GORM操作时遇到以下问题：

• 仅选择1个genre_id时，旧分类被修改，但新分类会新增一条重复记录（除created_at外其他字段值一致）；
• 选择2个genre_id时，只有第一个genre_id生效，第二个未被处理。

相关信息 #

数据库ERD #

前端传入的JSON Payload #

{
  "title":"Autaaa sint nihil quis ",
  "release_date":"2001-01-01",
  "runtime":"22",
  "mpaa_rating":"G",
  "rating":"4",
  "description":"Eius enim distinctio1",
  "id":"82",
  "genre_id":["3","8"]
}

Handler代码 #

func (app *Application) UpdateMovie(ctx *gin.Context) {

    var payload web.MoviePayloadResponse
    if err := ctx.BindJSON(&payload); err != nil {
        log.Println(err)
        return
    }

    id, _ := strconv.Atoi(payload.ID)
    movie, err := app.models.Repository.GetMovieById(id)
    if err != nil {
        helper.NotFound(*ctx, err)
        return
    }

    movie.ID, _ = strconv.Atoi(payload.ID)
    movie.Title = payload.Title
    movie.Description = payload.Description
    movie.ReleaseDate, _ = time.Parse("2006-01-02", payload.ReleaseDate)
    movie.Year = movie.ReleaseDate.Year()
    movie.Runtime, _ = strconv.Atoi(payload.Runtime)
    movie.Rating, _ = strconv.Atoi(payload.Rating)
    movie.MPAARating = payload.MPAARating
    movie.UpdatedAt = time.Now()
    
    err = app.models.Repository.UpdateMovie(movie, id)
    if err != nil {
        panic(err)
    }


    movieGenre, _ := app.models.Repository.GetMovieGenresById(id)
    
    for _, mg := range *movieGenre {
        mg.MovieID = movie.ID
        mg.UpdatedAt = time.Now()
        for _, v := range payload.GenreID {
            mg.GenreID, _ = strconv.Atoi(v)
            err := app.models.Repository.UpdateMovieGenre(&mg, id)
            if err != nil {
                panic(err)
            }
        }
    }

    ctx.JSON(http.StatusOK, &gin.H{
        "ok": "response",
    })

    return
}

Repository代码 #

func (MovieRepositoryImpl *MovieRepositoryImpl) UpdateMovie(movie *Movie, id int) error {
    err := MovieRepositoryImpl.DB.Where("id = ?", id).Save(&movie).Error
    if err != nil {
        return err
    }
    return nil
}

func (MovieRepositoryImpl *MovieRepositoryImpl) UpdateMovieGenre(movieGenre *MovieGenre, id int) error {
    err := MovieRepositoryImpl.DB.Where("movie_id = ?", id).Save(&movieGenre).Error
    if err != nil {
        return err
    }
    return nil
}

func (MovieRepositoryImpl *MovieRepositoryImpl) GetMovieGenresById(id int) (*[]MovieGenre, error) {
    var movie []MovieGenre
    err := MovieRepositoryImpl.DB.Where("movie_id = ?", id).Find(&movie).Error
    if err != nil {
        return nil, err
    }
    
    return &movie, nil
}

Model定义 #

type Movie struct {
    ID          int          `json:"id"`
    Title       string       `json:"title"`
    Description string       `json:"description"`
    Year        int          `json:"year"`
    ReleaseDate time.Time    `json:"release_date"`
    Runtime     int          `json:"runtime"`
    Rating      int          `json:"rating"`
    MPAARating  string       `json:"mpaa_rating"`
    CreatedAt   time.Time    `json:"created_at"`
    UpdatedAt   time.Time    `json:"updated_at"`
    Genres      []Genre      `json:"genres" gorm:"many2many:movie_genres"`
    MovieGenre  []MovieGenre `json:"movie_genres" gorm:"many2many:movie_genres"`
}

type Genre struct {
    ID        int       `json:"id"`
    GenreName string    `json:"name"`
    Movies    []Movie   `json:"movies" gorm:"many2many:movie_genres"`
    CreatedAt time.Time `json:"-"`
    UpdatedAt time.Time `json:"-"`
}

type MovieGenre struct {
    ID        int       `json:"id"`
    MovieID   int       `json:"movie_id"`
    Movie     Movie     `gorm:"foreignKey:MovieID"`
    GenreID   int       `json:"genre_id"`
    Genre     Genre     `gorm:"foreignKey:GenreID"`
    CreatedAt time.Time `json:"-"`
    UpdatedAt time.Time `json:"-"`
}

type MoviePayloadResponse struct {
    ID          string   `json:"id"`
    Title       string   `json:"title"`
    Description string   `json:"description"`
    Year        string   `json:"year"`
    ReleaseDate string   `json:"release_date"`
    Runtime     string   `json:"runtime"`
    Rating      string   `json:"rating"`
    MPAARating  string   `json:"mpaa_rating"`
    GenreID     []string `json:"genre_id"`
    CreatedAt   string   `json:"created_at"`
    UpdatedAt   string   `json:"updated_at"`
}

期望效果 #

仅更新与payload中genre_id不同的记录，相同记录不修改；若payload中的genre_id数量多于原有记录，自动新增超出部分的记录。

内容的提问来源于stack exchange，提问作者leo