Golang中sqlx库的嵌套结构解析

在Golang中使用sqlx库解析嵌套结构，可以通过结构体标签和sqlx的扩展功能实现。以下是一个主从表结构的示例：

假设有订单（主表）和订单项（从表）的关系：

import (
    "github.com/jmoiron/sqlx"
    "fmt"
)

// 订单项结构
type OrderItem struct {
    ID       int    `db:"id"`
    OrderID  int    `db:"order_id"`
    Product  string `db:"product"`
    Quantity int    `db:"quantity"`
}

// 订单结构（包含订单项切片）
type Order struct {
    ID      int         `db:"id"`
    Number  string      `db:"order_number"`
    Items   []OrderItem // 嵌套结构
}

// 查询订单及其订单项
func GetOrderWithItems(db *sqlx.DB, orderID int) (*Order, error) {
    // 使用sqlx的NamedQuery处理嵌套查询
    query := `
        SELECT 
            o.id, o.order_number,
            oi.id, oi.order_id, oi.product, oi.quantity
        FROM orders o
        LEFT JOIN order_items oi ON o.id = oi.order_id
        WHERE o.id = :order_id
    `
    
    // 创建参数映射
    params := map[string]interface{}{
        "order_id": orderID,
    }
    
    // 执行查询
    rows, err := db.NamedQuery(query, params)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    // 使用结构体扫描
    var order *Order
    orderMap := make(map[int]*Order)
    
    for rows.Next() {
        var (
            oID      int
            oNumber  string
            oiID     sql.NullInt64
            oiOrderID sql.NullInt64
            oiProduct sql.NullString
            oiQuantity sql.NullInt64
        )
        
        // 扫描行数据
        err := rows.Scan(&oID, &oNumber, &oiID, &oiOrderID, &oiProduct, &oiQuantity)
        if err != nil {
            return nil, err
        }
        
        // 如果订单尚未在map中，创建新订单
        if _, exists := orderMap[oID]; !exists {
            order = &Order{
                ID:     oID,
                Number: oNumber,
                Items:  []OrderItem{},
            }
            orderMap[oID] = order
        } else {
            order = orderMap[oID]
        }
        
        // 如果有订单项数据，添加到订单中
        if oiID.Valid {
            item := OrderItem{
                ID:       int(oiID.Int64),
                OrderID:  int(oiOrderID.Int64),
                Product:  oiProduct.String,
                Quantity: int(oiQuantity.Int64),
            }
            order.Items = append(order.Items, item)
        }
    }
    
    if order == nil {
        return nil, fmt.Errorf("order not found")
    }
    
    return order, nil
}

// 使用Select直接映射到嵌套结构（需要自定义扫描器）
func GetOrderWithItemsSelect(db *sqlx.DB, orderID int) ([]Order, error) {
    type OrderRow struct {
        OrderID      int    `db:"order_id"`
        OrderNumber  string `db:"order_number"`
        ItemID       *int   `db:"item_id"`
        Product      *string `db:"product"`
        Quantity     *int   `db:"quantity"`
    }
    
    var rows []OrderRow
    query := `
        SELECT 
            o.id as order_id,
            o.order_number,
            oi.id as item_id,
            oi.product,
            oi.quantity
        FROM orders o
        LEFT JOIN order_items oi ON o.id = oi.order_id
        WHERE o.id = ?
        ORDER BY o.id
    `
    
    err := db.Select(&rows, query, orderID)
    if err != nil {
        return nil, err
    }
    
    // 手动分组数据
    ordersMap := make(map[int]*Order)
    for _, row := range rows {
        if _, exists := ordersMap[row.OrderID]; !exists {
            ordersMap[row.OrderID] = &Order{
                ID:     row.OrderID,
                Number: row.OrderNumber,
                Items:  []OrderItem{},
            }
        }
        
        if row.ItemID != nil {
            item := OrderItem{
                ID:       *row.ItemID,
                OrderID:  row.OrderID,
                Product:  *row.Product,
                Quantity: *row.Quantity,
            }
            ordersMap[row.OrderID].Items = append(ordersMap[row.OrderID].Items, item)
        }
    }
    
    // 转换为切片
    orders := make([]Order, 0, len(ordersMap))
    for _, order := range ordersMap {
        orders = append(orders, *order)
    }
    
    return orders, nil
}

对于更复杂的嵌套结构，可以使用sqlx的StructScan结合自定义扫描：

// 使用sqlx.In和Rebind处理多记录查询
func GetOrdersWithItems(db *sqlx.DB, orderIDs []int) ([]Order, error) {
    query, args, err := sqlx.In(`
        SELECT 
            o.id, o.order_number,
            oi.id, oi.order_id, oi.product, oi.quantity
        FROM orders o
        LEFT JOIN order_items oi ON o.id = oi.order_id
        WHERE o.id IN (?)
        ORDER BY o.id, oi.id
    `, orderIDs)
    
    if err != nil {
        return nil, err
    }
    
    query = db.Rebind(query)
    rows, err := db.Queryx(query, args...)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    ordersMap := make(map[int]*Order)
    
    for rows.Next() {
        var (
            order Order
            item  OrderItem
        )
        
        // 使用结构体扫描
        err := rows.Scan(
            &order.ID,
            &order.Number,
            &item.ID,
            &item.OrderID,
            &item.Product,
            &item.Quantity,
        )
        if err != nil {
            return nil, err
        }
        
        if _, exists := ordersMap[order.ID]; !exists {
            ordersMap[order.ID] = &Order{
                ID:     order.ID,
                Number: order.Number,
                Items:  []OrderItem{},
            }
        }
        
        if item.ID != 0 {
            ordersMap[order.ID].Items = append(ordersMap[order.ID].Items, item)
        }
    }
    
    orders := make([]Order, 0, len(ordersMap))
    for _, order := range ordersMap {
        orders = append(orders, *order)
    }
    
    return orders, nil
}

这些示例展示了使用sqlx处理嵌套结构的常见模式，包括JOIN查询、手动分组映射和批量查询处理。