如何使用Gorm重写获取关联模型的递归SQL查询？

问题分析 #

你当前的代码只是简单拼接两个子查询，没有正确构建递归CTE的结构。Gorm提供了专门的CTE支持，也可以直接执行已验证的原生SQL。另外需要先修正Model结构体的关联字段定义，否则GORM无法正确映射数据库字段。

步骤1：修正Model结构体定义 #

你的原生SQL中用到了parent_model_id，但原结构体未显式声明该字段；同时ParentModel字段需要用指针类型避免循环引用，补充后结构体如下：

import "github.com/google/uuid"

type Model struct {
    ID           uuid.UUID `gorm:"primaryKey"`
    Name         string
    ParentModel  *Model    `gorm:"foreignKey:ParentModelID"` // 指针类型避免循环引用
    ParentModelID uuid.UUID // 对应数据库的parent_model_id字段
    CaseID       uuid.UUID
    CaseVersion  string // 原生SQL中提到的case_version字段，按需补充
}

步骤2：用Gorm CTE API实现递归查询 #

Gorm支持通过With方法构建递归CTE，代码示例：

// 假设db是已初始化的Gorm DB实例
var models []Model
targetCaseID := uuid.MustParse("1") // 替换为实际要查询的CaseID

// 定义递归CTE：第一个参数指定"recursive model"，后续是初始查询和递归查询部分
cte := db.With("recursive model",
    // 初始查询：获取指定CaseID的所有Model
    db.Select("id, name, parent_model_id, case_id, case_version").Model(&Model{}).Where("case_id = ?", targetCaseID),
    // 递归查询：关联CTE的model表，获取所有子Model
    db.Select("m.id, m.name, m.parent_model_id, m.case_id, m.case_version").Model(&Model{}).Table("models m").
        Joins("inner join model on model.id = m.parent_model_id"),
)

// 从CTE表中查询所有结果
if err := cte.Table("model").Find(&models).Error; err != nil {
    // 处理查询错误，如日志记录或返回错误
}

步骤3：直接执行原生SQL（更简便的方式） #

如果你已经有验证过的原生SQL，直接用Gorm的Raw方法执行会更直接，无需学习CTE API的细节：

var models []Model
targetCaseID := uuid.MustParse("1")

err := db.Raw(`
with recursive model(id, name, case_version, parent_model_id, case_id) as (
  select models.* from models  
  where models.case_id = ?
union all 
  select models.* from models 
  inner join model on model.id = models.parent_model_id
)
select * from model
`, targetCaseID).Scan(&models).Error

if err != nil {
    // 处理错误逻辑
}

注意事项 #

• 确保数据库支持递归CTE（如PostgreSQL、MySQL 8.0+等）
• 如果表结构有其他字段，需同步在结构体和查询语句中补充

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