简化带双重引用的FilmGroup模型：实现数据库映射与展示模型复用

嗨，我来帮你梳理下如何简化FilmGroup模型，同时兼顾数据库映射和展示需求。其实核心思路是利用结构体标签区分数据库字段和关联对象，或者借助轻量ORM思想优化映射逻辑，以下是具体方案：

方案一：通过结构体标签隐藏冗余字段，复用单个结构体 #

你可以保留FilmOrder引用，同时通过结构体标签明确区分数据库字段和关联对象，无需同时维护FilmOrderID和FilmOrder两个冗余字段。

修改后的FilmGroup模型：

type FilmGroup struct {
    ID        int        `db:"id"`
    Films     []Film     `db:"-"` // 标记为非数据库字段，因为是关联查询结果
    FilmOrder *FilmOrder `db:"-"` // 标记为非数据库字段，用于展示关联数据
    FilmOrderID *int     `db:"film_order_id"` // 仅保留数据库外键字段
    Status    string     `db:"status"`
    Comments  *string    `db:"comments"`
}

关键调整说明：

• 用db:"-"标记Films和FilmOrder字段，告诉数据库驱动这两个字段不需要映射到表列（它们是通过关联查询得到的展示数据）。
• 只保留FilmOrderID作为数据库外键的映射字段，用于数据库的增删改操作；而FilmOrder字段仅用于查询后赋值，给前端展示用。

优化后的AddFilmGroup方法（无需改动太多）：

func AddFilmGroup(db *sql.DB, filmGroup models.FilmGroup) error {
    insertGroupQuery := `
        INSERT INTO FilmGroup (status, comments, film_order_id)
        VALUES (?, ?, ?);`
    result, err := db.Exec(insertGroupQuery, filmGroup.Status, filmGroup.Comments, filmGroup.FilmOrderID)
    if err != nil {
        return fmt.Errorf("failed to insert FilmGroup: %w", err)
    }

    groupID, err := result.LastInsertId()
    if err != nil {
        return fmt.Errorf("failed to retrieve FilmGroup ID: %w", err)
    }

    for _, film := range filmGroup.Films {
        insertFilmGroupFilmQuery := `
            INSERT INTO FilmGroup_Film (film_group_id, film_id)
            VALUES (?, ?);`
        _, err := db.Exec(insertFilmGroupFilmQuery, groupID, film.ID)
        if err != nil {
            return fmt.Errorf("failed to associate Film %d with FilmGroup %d: %w", film.ID, groupID, err)
        }
    }

    log.Printf("FilmGroup '%d' added successfully.\n", groupID)
    return nil
}

优化后的GetFilmGroupWithDetails方法：

func GetFilmGroupWithDetails(db *sql.DB, groupID int) (models.FilmGroup, error) {
    var filmGroup models.FilmGroup

    query := `
        SELECT id, status, comments, film_order_id
        FROM FilmGroup
        WHERE id = ?;`
    err := db.QueryRow(query, groupID).Scan(&filmGroup.ID, &filmGroup.Status, &filmGroup.Comments, &filmGroup.FilmOrderID)
    if err != nil {
        if err == sql.ErrNoRows {
            return filmGroup, fmt.Errorf("FilmGroup with ID %d not found", groupID)
        }
        return filmGroup, fmt.Errorf("failed to fetch FilmGroup: %w", err)
    }

    // 关联查询Films的逻辑保持不变
    filmQuery := `
        SELECT f.id, f.film_name, f.film_format, f.film_processing_type, f.scans_folder, f.roll_label, f.comments, f.date_shot, f.scan_completed, f.cut_and_sleeved
        FROM Film f
        JOIN FilmGroup_Film fgf ON f.id = fgf.film_id
        WHERE fgf.film_group_id = ?;`
    rows, err := db.Query(filmQuery, groupID)
    if err != nil {
        return filmGroup, fmt.Errorf("failed to fetch films for FilmGroup %d: %w", groupID, err)
    }
    defer rows.Close()

    var films []models.Film
    for rows.Next() {
        var film models.Film
        var filmFormat, filmProcessingType, scansFolder, rollLabel, comments, dateShot sql.NullString
        var scanCompleted, cutAndSleeved sql.NullBool

        if err := rows.Scan(&film.ID, &film.FilmName, &filmFormat, &filmProcessingType, &scansFolder, &rollLabel, &comments, &dateShot, &scanCompleted, &cutAndSleeved); err != nil {
            return filmGroup, fmt.Errorf("failed to scan film row: %w", err)
        }

        // 处理nullable字段（你的原有逻辑）
        if filmFormat.Valid { film.FilmFormat = &filmFormat.String }
        if filmProcessingType.Valid { film.FilmProcessingType = &filmProcessingType.String }
        if scansFolder.Valid { film.ScansFolder = &scansFolder.String }
        if rollLabel.Valid { film.RollLabel = &rollLabel.String }
        if comments.Valid { film.Comments = &comments.String }
        if dateShot.Valid { film.DateShot = &dateShot.String }
        if scanCompleted.Valid { film.ScanCompleted = &scanCompleted.Bool }
        if cutAndSleeved.Valid { film.CutAndSleeved = &cutAndSleeved.Bool }

        films = append(films, film)
    }
    if err := rows.Err(); err != nil {
        return filmGroup, fmt.Errorf("error iterating film rows: %w", err)
    }
    filmGroup.Films = films

    // 根据FilmOrderID查询并赋值FilmOrder
    if filmGroup.FilmOrderID != nil {
        var filmOrder models.FilmOrder
        filmOrderQuery := `
            SELECT id, film_lab_name, to_lab_tracking, from_lab_tracking, date_film_received_by_lab, date_film_received_by_me, date_ordered, date_mailed_to_lab, scans_requested, scans_received, date_scans_received_by_me
            FROM FilmOrder
            WHERE id = ?;`
        err = db.QueryRow(filmOrderQuery, *filmGroup.FilmOrderID).Scan(&filmOrder.ID, &filmOrder.FilmLabName, &filmOrder.ToLabTracking, &filmOrder.FromLabTracking, &filmOrder.DateFilmReceivedByLab, &filmOrder.DateFilmReceivedByMe, &filmOrder.DateOrdered, &filmOrder.DateMailedToLab, &filmOrder.ScansRequested, &filmOrder.ScansReceived, &filmOrder.DateScansReceivedByMe)
        if err != nil {
            if err == sql.ErrNoRows {
                return filmGroup, fmt.Errorf("FilmOrder not found for FilmGroup %d", groupID)
            }
            return filmGroup, fmt.Errorf("failed to fetch FilmOrder: %w", err)
        }
        filmGroup.FilmOrder = &filmOrder
    }

    return filmGroup, nil
}

方案二：引入轻量ORM库（如GORM）自动处理关联 #

如果不想手动处理关联映射，推荐使用GORM这类成熟的Go ORM库，它能自动处理一对一、一对多关联，并且支持结构体直接映射数据库和展示：

用GORM定义的模型：

import "gorm.io/gorm"

type Film struct {
    gorm.Model
    FilmName           string  `gorm:"column:film_name;not null"`
    FilmFormat         *string `gorm:"column:film_format"`
    FilmProcessingType *string `gorm:"column:film_processing_type"`
    ScansFolder        *string `gorm:"column:scans_folder"`
    RollLabel          *string `gorm:"column:roll_label"`
    Comments           *string `gorm:"column:comments"`
    DateShot           *string `gorm:"column:date_shot"`
    ScanCompleted      *bool   `gorm:"column:scan_completed"`
    CutAndSleeved      *bool   `gorm:"column:cut_and_sleeved"`
}

type FilmOrder struct {
    gorm.Model
    FilmLabName           string     `gorm:"column:film_lab_name;not null"`
    ToLabTracking         *string    `gorm:"column:to_lab_tracking"`
    FromLabTracking       *string    `gorm:"column:from_lab_tracking"`
    DateFilmReceivedByLab *time.Time `gorm:"column:date_film_received_by_lab"`
    DateFilmReceivedByMe  *time.Time `gorm:"column:date_film_received_by_me"`
    DateOrdered           *time.Time `gorm:"column:date_ordered"`
    DateMailedToLab       *time.Time `gorm:"column:date_mailed_to_lab"`
    ScansRequested        *bool      `gorm:"column:scans_requested"`
    ScansReceived         *bool      `gorm:"column:scans_received"`
    DateScansReceivedByMe *time.Time `gorm:"column:date_scans_received_by_me"`
}

type FilmGroup struct {
    gorm.Model
    Status    string     `gorm:"column:status"`
    Comments  *string    `gorm:"column:comments"`
    Films     []Film     `gorm:"many2many:FilmGroup_Film;"` // 自动关联多对多中间表
    FilmOrder *FilmOrder `gorm:"foreignKey:FilmOrderID"`   // 自动处理一对一关联
    FilmOrderID *int     `gorm:"column:film_order_id"`      // 外键字段，可选保留
}

优势：

• GORM会自动处理插入时的外键赋值、查询时的关联预加载（比如用db.Preload("FilmOrder").Preload("Films").First(&filmGroup, groupID)一次性查询所有关联数据）。
• 同一个结构体既可以用于数据库操作，也可以直接返回给前端展示，无需额外转换。

额外注意事项 #

1. 你的数据库表FilmGroup的外键引用写错了：REFERENCES FilmOrders (id)应该改为REFERENCES FilmOrder (id)（表名是FilmOrder，不是FilmOrders），这个错误会导致外键约束失效，记得修正。
2. 如果坚持不用ORM，也可以封装一个工具函数，专门处理从数据库字段到关联对象的映射，减少重复代码。

备注：内容来源于stack exchange，提问作者FlameDra