Go-Gorm多对多关联下Secret关联Environment的查询问题

问题背景 #

现有以下Go GORM模型：

type User struct {
    ID        uuid.UUID `gorm:"type:uuid;default:uuid_generate_v4();primary_key" json:"id"`
    // 其他字段
}

type Environment struct {
    ID        uuid.UUID `gorm:"type:uuid;default:uuid_generate_v4();primary_key" json:"id"`
    UserId    uuid.UUID `gorm:"type:uuid" json:"userId"`
    User      User      `gorm:"foreignKey:UserId;references:ID;constraint:OnUpdate:CASCADE,OnDelete:CASCADE;" json:"-"`
    // 其他字段
}

type Secret struct {
    ID           uuid.UUID     `gorm:"type:uuid;default:uuid_generate_v4();primary_key" json:"id"`
    UserId       uuid.UUID     `gorm:"type:uuid" json:"userId"`
    User         User          `gorm:"foreignKey:UserId;references:ID;constraint:OnUpdate:CASCADE,OnDelete:CASCADE;" json:"-"`
    Environments []Environment `gorm:"many2many:environment_secrets;constraint:OnUpdate:CASCADE,OnDelete:CASCADE;" json:"environments"`
    // 其他字段
}

Secret与Environment通过中间表environment_secrets建立多对多关联，表结构如下：

secret_id | environment_id
--------------------------
uuid      | uuid

需求：查询属于指定用户（匹配userId）且关联了特定Environment ID的Secret，同时返回每个Secret关联的所有Environment数据。

原查询代码报错：

var secrets []models.Secret
if err := db.Preload("Environments").Find(&secrets, "user_id=? AND ? @> environments.id", userSessionId, environmentId).Error; err != nil {
  return c.Status(fiber.StatusOK).JSON(
    fiber.Map{"error": err.Error()},
  )
}
// 错误信息：ERROR: missing FROM-clause entry for table "environments" (SQLSTATE 42P01)

错误原因 #

原代码的Find条件中直接引用environments.id，但主查询仅针对secrets表，未关联中间表和environments表，导致SQL解析时找不到environments表的引用。

解决方案 #

方法一：GORM关联查询+预加载 #

通过Joins关联中间表和environments表过滤目标Secret，再用Preload加载所有关联的Environment数据：

var secrets []models.Secret
err := db.Preload("Environments").
    Joins("JOIN environment_secrets es ON es.secret_id = secrets.id").
    Joins("JOIN environments e ON e.id = es.environment_id").
    Where("secrets.user_id = ? AND e.id = ?", userSessionId, environmentId).
    Find(&secrets).Error

if err != nil {
    return c.Status(fiber.StatusOK).JSON(fiber.Map{"error": err.Error()})
}
// 返回secrets，每个Secret的Environments字段包含所有关联的环境数据

方法二：原生SQL查询 #

直接编写原生SQL，通过关联查询过滤Secret，并聚合所有关联的Environment数据：

type SecretWithEnvs struct {
    models.Secret
    Environments []models.Environment `json:"environments"`
}

var secrets []SecretWithEnvs
sqlQuery := `
SELECT 
    s.*,
    COALESCE(json_agg(e) FILTER (WHERE e.id IS NOT NULL), '[]') AS environments
FROM secrets s
JOIN environment_secrets es ON s.id = es.secret_id
JOIN environments e ON es.environment_id = e.id
WHERE s.user_id = $1 AND e.id = $2
GROUP BY s.id
`

err := db.Raw(sqlQuery, userSessionId, environmentId).Scan(&secrets).Error
if err != nil {
    return c.Status(fiber.StatusOK).JSON(fiber.Map{"error": err.Error()})
}
// 返回secrets，每个对象包含完整的Secret信息和所有关联的Environment数据

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