如何用Gorm执行跨表原生查询？结果为空问题排查

问题描述 #

使用PostgreSQL数据库，通过Gorm执行原生SQL查询多表字段，已定义对应Result结构体，SQL在psql终端执行正常，但Gorm调用后返回空集合且无报错。

相关代码 #

type Result struct {
    ID        string     `gorm:"column:id"`           
    CreatedAt time.Time  `gorm:"column:created_at"`   
    UpdatedAt time.Time  `gorm:"column:updated_at"`   
    DeletedAt *time.Time `gorm:"column:a_deleted_at"` 
    Category  string     `gorm:"column:category_code"`                    

    LastReleaseID          string    `gorm:"column:r_id"`
    LastReleaseIconID      uuid.UUID `gorm:"column:i_id"`          
    LastReleaseName        string    `gorm:"column:r_name"`
    LastReleaseVersion     string    `gorm:"column:r_version"`
    LastReleaseSubVersion  string    `gorm:"column:r_subversion"`
    LastReleaseStatus      string    `gorm:"column:r_status"`
    
    Developer string  `gorm:"column:b_dev"` 
}

func DoRequestToDB(db *gorm.DB, sqlArgs ...any) error {
    sql := `SELECT
        a."id",
        a."created_at",
        a."updated_at",
        a."deleted_at" AS a_deleted_at,
        a."category_code"
        ,
        r.id AS r_id,
        r.icon_id AS i_id,
        r.name AS r_name,
        r.version AS r_version,
        r.subversion AS r_subVersion
        r.status AS r_status
        ,
        b.developer AS b_dev
    FROM "applications" as a
    JOIN (
        SELECT release_filtered.*
        FROM releases AS release_filtered
        JOIN (
            SELECT
                application_id,
                max(created_at) as max_created_at
            FROM releases
            WHERE deleted_at is null
            AND status in (?)
            GROUP BY "application_id"
        ) AS release_condition
        ON release_filtered.application_id = release_condition.application_id
        AND release_filtered.created_at = release_condition.max_created_at
        and release_filtered.deleted_at is null
    ) AS r
    ON r.application_id = a.id
    JOIN builds AS b
    ON r.build_id = b.id
    WHERE a.deleted_at IS NULL`

    var result = make([]Result, 0)
    err := db.Raw(sqlQuery, sqlArgs...).Find(&result).Error
    if err != nil {
        return nil, err
    }

    return result, nil
}

错误点及修复方案 #

1. SQL语句语法错误 #

SQL中r.subversion AS r_subVersion一行末尾缺少逗号，导致语法错误：

r.version AS r_version,
r.subversion AS r_subVersion  -- 此处缺少逗号
r.status AS r_status

修复：添加逗号，修正为：

r.version AS r_version,
r.subversion AS r_subVersion,
r.status AS r_status

2. 函数返回值类型不匹配 #

函数声明返回error，但实际返回result, nil（切片和错误），这会导致编译错误。
修复：修改函数签名为返回切片和错误：

func DoRequestToDB(db *gorm.DB, sqlArgs ...any) ([]Result, error)

3. Gorm调用时变量名错误 #

代码中定义的SQL变量名为sql，但db.Raw中使用了未定义的sqlQuery，导致执行空SQL或编译错误。
修复：替换为正确的变量名：

err := db.Raw(sql, sqlArgs...).Scan(&result).Error

4. Gorm查询方法使用错误 #

执行原生SQL后，应使用Scan方法将结果映射到结构体，而非Find（Find适用于GORM的链式查询）。
修复：将Find改为Scan：

err := db.Raw(sql, sqlArgs...).Scan(&result).Error

5. 字段映射大小写不匹配 #

PostgreSQL对标识符大小写敏感（未加双引号时会转为小写），SQL中r.subversion AS r_subVersion的别名为驼峰式，但结构体标签gorm:"column:r_subversion"是全小写，导致映射失败。
修复：统一别名和标签的大小写，例如将SQL中的别名改为小写：

r.subversion AS r_subversion,
r.status AS r_status

或者修改结构体标签为驼峰式（需确保SQL别名一致）：

LastReleaseSubVersion  string    `gorm:"column:r_subVersion"`

6. 参数传递验证 #

确认调用DoRequestToDB时传入了正确的status参数，例如：

results, err := DoRequestToDB(db, "published", "draft")

如果参数为空或不符合条件，会导致查询结果为空。

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