查询构建器

VEF Framework 提供了强大的查询构建器，基于 GORM 封装，支持链式调用。

基本查询

获取单条记录

import "github.com/ilxqx/vef-framework-go/orm"

// Find by ID
var user models.User
err := orm.DB().First(&user, "id = ?", userId).Error

// Find by condition
err := orm.DB().Where("username = ?", "john").First(&user).Error

// Find with multiple conditions
err := orm.DB().Where("is_active = ? AND department_id = ?", true, deptId).First(&user).Error

获取多条记录

var users []models.User

// Find all
err := orm.DB().Find(&users).Error

// Find with condition
err := orm.DB().Where("is_active = ?", true).Find(&users).Error

// Find with limit
err := orm.DB().Where("department_id = ?", deptId).Limit(10).Find(&users).Error

分页查询

var users []models.User
var total int64

page := 1
pageSize := 10
offset := (page - 1) * pageSize

// Count total
orm.DB().Model(&models.User{}).Where("is_active = ?", true).Count(&total)

// Get page data
orm.DB().Where("is_active = ?", true).
    Offset(offset).
    Limit(pageSize).
    Find(&users)

条件查询

Where 条件

// Equal
orm.DB().Where("username = ?", "john")

// Not equal
orm.DB().Where("status <> ?", "deleted")

// IN
orm.DB().Where("id IN ?", []string{"id1", "id2", "id3"})

// LIKE
orm.DB().Where("username LIKE ?", "%john%")

// BETWEEN
orm.DB().Where("created_at BETWEEN ? AND ?", startTime, endTime)

// NULL check
orm.DB().Where("deleted_at IS NULL")

多条件组合

// AND conditions
orm.DB().Where("is_active = ?", true).
    Where("department_id = ?", deptId).
    Where("created_at > ?", startTime)

// OR conditions
orm.DB().Where("role = ?", "admin").
    Or("role = ?", "manager")

// Grouped conditions
orm.DB().Where("is_active = ?", true).
    Where(orm.DB().Where("role = ?", "admin").Or("role = ?", "manager"))

选择字段

指定返回字段

var users []models.User

// Select specific columns
orm.DB().Select("id", "username", "email").Find(&users)

// Select with alias
orm.DB().Select("id", "username as name").Find(&users)

// Select with expression
orm.DB().Select("id", "username", "CONCAT(first_name, ' ', last_name) as full_name").Find(&users)

排除字段

// Omit sensitive fields
orm.DB().Omit("password", "salt").Find(&users)

排序

单字段排序

// Ascending
orm.DB().Order("created_at ASC").Find(&users)

// Descending
orm.DB().Order("created_at DESC").Find(&users)

多字段排序

orm.DB().Order("is_active DESC").Order("created_at DESC").Find(&users)

// Or in single call
orm.DB().Order("is_active DESC, created_at DESC").Find(&users)

关联查询

预加载关联

var users []models.User

// Preload single association
orm.DB().Preload("Department").Find(&users)

// Preload multiple associations
orm.DB().Preload("Department").Preload("Roles").Find(&users)

// Nested preload
orm.DB().Preload("Department.Company").Find(&users)

条件预加载

// Preload with condition
orm.DB().Preload("Roles", "is_active = ?", true).Find(&users)

// Preload with custom query
orm.DB().Preload("Roles", func(db *gorm.DB) *gorm.DB {
    return db.Where("is_active = ?", true).Order("name ASC")
}).Find(&users)

Join 查询

var results []struct {
    UserId       string
    Username     string
    DepartmentName string
}

orm.DB().Table("users").
    Select("users.id as user_id, users.username, departments.name as department_name").
    Joins("LEFT JOIN departments ON departments.id = users.department_id").
    Where("users.is_active = ?", true).
    Scan(&results)

聚合查询

计数

var count int64
orm.DB().Model(&models.User{}).Where("is_active = ?", true).Count(&count)

求和

var total float64
orm.DB().Model(&models.Order{}).
    Where("status = ?", "completed").
    Select("SUM(amount)").
    Scan(&total)

分组统计

type DepartmentStats struct {
    DepartmentId string
    UserCount    int64
}

var stats []DepartmentStats
orm.DB().Model(&models.User{}).
    Select("department_id, COUNT(*) as user_count").
    Group("department_id").
    Having("COUNT(*) > ?", 5).
    Scan(&stats)

子查询

// Subquery in WHERE
subQuery := orm.DB().Model(&models.Order{}).
    Select("user_id").
    Where("status = ?", "completed").
    Group("user_id").
    Having("SUM(amount) > ?", 1000)

var users []models.User
orm.DB().Where("id IN (?)", subQuery).Find(&users)

原始 SQL

执行原始查询

var users []models.User
orm.DB().Raw("SELECT * FROM users WHERE is_active = ? AND created_at > ?", true, startTime).Scan(&users)

执行原始 SQL

orm.DB().Exec("UPDATE users SET login_count = login_count + 1 WHERE id = ?", userId)

使用 Query 构建器

VEF Framework 提供了类型安全的 Query 构建器：

import "github.com/ilxqx/vef-framework-go/orm"

// Create typed query
q := orm.NewQuery[models.User]()

// Build query with chain methods
users, err := q.
    Where("is_active", true).
    Where("department_id", deptId).
    OrderBy("created_at DESC").
    Limit(10).
    Find()

// With preload
users, err := q.
    Preload("Department", "Roles").
    Where("is_active", true).
    Find()

// Pagination
users, total, err := q.
    Where("is_active", true).
    Paginate(page, pageSize)

下一步

• 学习 条件构建器 构建复杂条件
• 了解 搜索标签 简化查询参数
• 查看 事务处理 确保数据一致性