SQL 构建器

动态构建 SQL 是常见需求。GORM 提供了灵活的构建方式，支持条件拼接、动态查询等场景。

链式构建

GORM 的链式 API 本身就是 SQL 构建器：

query := db.Model(&User{})

if name != "" {
    query = query.Where("name LIKE ?", "%"+name+"%")
}
if status != "" {
    query = query.Where("status = ?", status)
}
if minAge > 0 {
    query = query.Where("age >= ?", minAge)
}

var users []User
query.Find(&users)

条件构建器

封装条件构建逻辑：

type QueryBuilder struct {
    db *gorm.DB
}

func NewQueryBuilder(db *gorm.DB) *QueryBuilder {
    return &QueryBuilder{db: db}
}

func (qb *QueryBuilder) Where(condition string, args ...interface{}) *QueryBuilder {
    qb.db = qb.db.Where(condition, args...)
    return qb
}

func (qb *QueryBuilder) If(condition bool, fn func(*QueryBuilder) *QueryBuilder) *QueryBuilder {
    if condition {
        return fn(qb)
    }
    return qb
}

func (qb *QueryBuilder) Find(dest interface{}) error {
    return qb.db.Find(dest).Error
}

// 使用
var users []User
NewQueryBuilder(db.Model(&User{})).
    If(name != "", func(qb *QueryBuilder) *QueryBuilder {
        return qb.Where("name LIKE ?", "%"+name+"%")
    }).
    If(status != "", func(qb *QueryBuilder) *QueryBuilder {
        return qb.Where("status = ?", status)
    }).
    Find(&users)

动态 ORDER BY

func BuildOrderBy(sortBy string, order string) string {
    allowedFields := map[string]bool{
        "id":         true,
        "name":       true,
        "created_at": true,
        "updated_at": true,
    }
    
    allowedOrders := map[string]bool{
        "asc":  true,
        "desc": true,
    }
    
    if !allowedFields[sortBy] {
        sortBy = "id"
    }
    if !allowedOrders[order] {
        order = "desc"
    }
    
    return sortBy + " " + order
}

db.Order(BuildOrderBy(params.SortBy, params.Order)).Find(&users)

动态 SELECT

func SelectFields(fields []string) interface{} {
    if len(fields) == 0 {
        return "*"
    }
    return fields
}

db.Select(SelectFields(params.Fields)).Find(&users)

动态 JOIN

query := db.Model(&User{})

if withOrders {
    query = query.Joins("LEFT JOIN orders ON orders.user_id = users.id")
}
if withProfile {
    query = query.Joins("LEFT JOIN profiles ON profiles.user_id = users.id")
}

query.Find(&results)

子查询构建

subQuery := db.Model(&Order{}).
    Select("user_id, SUM(amount) as total").
    Group("user_id").
    Having("total > ?", 1000)

db.Table("(?) as o", subQuery).
    Select("users.*, o.total").
    Joins("JOIN users ON users.id = o.user_id").
    Find(&results)

SQL 片段复用

var activeUserScope = func(db *gorm.DB) *gorm.DB {
    return db.Where("status = ?", "active").Where("deleted_at IS NULL")
}

var adultUserScope = func(db *gorm.DB) *gorm.DB {
    return db.Where("age >= ?", 18)
}

db.Scopes(activeUserScope, adultUserScope).Find(&users)

复杂查询构建

搜索条件构建

func BuildSearchQuery(db *gorm.DB, params SearchParams) *gorm.DB {
    query := db.Model(&User{})

    if params.Keyword != "" {
        query = query.Where("name LIKE ? OR email LIKE ?",
            "%"+params.Keyword+"%",
            "%"+params.Keyword+"%")
    }

    if len(params.Statuses) > 0 {
        query = query.Where("status IN ?", params.Statuses)
    }

    if params.MinAge > 0 {
        query = query.Where("age >= ?", params.MinAge)
    }
    if params.MaxAge > 0 {
        query = query.Where("age <= ?", params.MaxAge)
    }

    if !params.StartTime.IsZero() {
        query = query.Where("created_at >= ?", params.StartTime)
    }
    if !params.EndTime.IsZero() {
        query = query.Where("created_at <= ?", params.EndTime)
    }

    if params.OrderBy != "" {
        query = query.Order(params.OrderBy + " " + params.Order)
    }

    return query
}

统计查询构建

func BuildStatsQuery(db *gorm.DB, groupBy string, filters map[string]interface{}) *gorm.DB {
    query := db.Model(&Order{})

    for key, value := range filters {
        if value != nil && value != "" {
            query = query.Where(key+" = ?", value)
        }
    }

    return query.Select(groupBy + ", COUNT(*) as count, SUM(amount) as total").
        Group(groupBy).
        Order("total DESC")
}

使用 Clauses

GORM 的 Clauses 提供更底层的构建能力：

import "gorm.io/gorm/clause"

db.Clauses(clause.OrderBy{
    Columns: []clause.OrderByColumn{
        {Column: clause.Column{Name: "age"}, Desc: true},
        {Column: clause.Column{Name: "name"}},
    },
}).Find(&users)

db.Clauses(clause.Locking{Strength: "UPDATE"}).Find(&users)

db.Clauses(clause.Limit{
    Limit:  10,
    Offset: 20,
}).Find(&users)

小结

SQL 构建器让动态查询变得清晰。合理封装构建逻辑，提高代码复用性和可读性。