为什么 GORM 的 db.raw 在 sql 中用 mysql as 别名查询会赋空值

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

The Go Programming Language

› http://golang.org/

› Go Playground

Go Projects

› Revel Web Framework

示例 1：结构体和 select 都是用的 as 别名，直接 get 赋值给结构体的是空值，

r.GET("/fp", utils.AuthMiddleware, func(c *gin.Context) {
username := c.MustGet("username").(string)

var products []struct {
Age string `json:"a"`
TaobaoID string `json:"b"`
MinPrice float64 `json:"c"`
TPrice int `json:"d"`
}

if err := db.Raw(`select products.age as a,products.min_price as b,products.taobao_id as c from products join users where users.username = ?`, username).Scan(&products).Error; err != nil {
c.JSON( http.StatusInternalServerError, gin.H{"error": "Could not retrieve user products"})
return
}

c.JSON( http.StatusOK, products)
})

[root@ctos0 routes]# curl -X GET http://localhost:8080/fp -H "Authorization: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJleHAiOjE3MzI2ODg1OTQsImlzcyI6ImFhYSJ9.CRiAl3SG509O2XkB-a_aXtnNcgFORZsbP_7M-580HBg"
[GIN] 2024/11/26 - 09:05:55 | 200 | 716.591µs | ::1 | GET "/fp"
[{"a":"","b":"","c":0,"d":0},{"a":"","b":"","c":0,"d":0},{"a":"","b":"","c":0,"d":0},{"a":"","b":"","c":0,"d":0},{"a":"","b":"","c":0,"d":0},{"a":"","b":"","c":0,"d":0}]

示例 2：直接使用列名赋值给结构体，sql 语句不用 as 别名，这样可以正常赋值显示数据

r.GET("/fp", utils.AuthMiddleware, func(c *gin.Context) {
username := c.MustGet("username").(string)

var products []struct {
Age string `json:"p.age"`
TaobaoID string `json:"p.taobao_id"`
MinPrice float64 `json:"p.min_price"`
TPrice int `json:"pr.t_price"`
}

if err := db.Raw(`
SELECT p.age, p.min_price, p.taobao_id, pr.t_price
FROM user_products up
JOIN products p ON up.product_id = p.id
JOIN prices pr ON up.t_price = pr.t_price
JOIN users u ON up.username = u.username
WHERE u.username = ?`, username).Scan(&products).Error; err != nil {
c.JSON( http.StatusInternalServerError, gin.H{"error": "Could not retrieve user products"})
return
}

c.JSON( http.StatusOK, products)
})

[root@ctos0 routes]# curl -X GET http://localhost:8080/fp -H "Authorization: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJleHAiOjE3MzI2ODg1OTQsImlzcyI6ImFhYSJ9.CRiAl3SG509O2XkB-a_aXtnNcgFORZsbP_7M-580HBg"
[GIN] 2024/11/26 - 09:10:44 | 200 | 821.97µs | ::1 | GET "/fp"
[{"p.age":"333","p.taobao_id":"033","p.min_price":129,"pr.t_price":79},{"p.age":"222","p.taobao_id":"022","p.min_price":129,"pr.t_price":79}]

3 条回复

rrfeng

3 小时 5 分钟前 via Android

拿 json tag 硬凑 gorm 啊…
gorm 有自己的 tag

saleacy

2 小时 21 分钟前

@rrfeng 没怎么用过😅

saleacy

2 小时 11 分钟前

@rrfeng 很强！

r.GET("/fp", utils.AuthMiddleware, func(c *gin.Context) {
username := c.MustGet("username").(string)

var products []struct {
Age string `gorm:"column:a"`
TaobaoID string `gorm:"column:b"`
MinPrice float64 `gorm:"column:c"`
TPrice int `gorm:"column:d"`
}

if err := db.Raw(`select products.age as a,products.min_price as b,products.taobao_id as c from products join users where users.username = ?`, username).Scan(&products).Error; err != nil {
c.JSON( http.StatusInternalServerError, gin.H{"error": "Could not retrieve user products"})
return
}

c.JSON( http.StatusOK, products)
})

[root@ctos0 routes]# curl -X GET http://localhost:8080/fp -H "Authorization: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJleHAiOjE3MzI2ODg1OTQsImlzcyI6ImFhYSJ9.CRiAl3SG509O2XkB-a_aXtnNcgFORZsbP_7M-580HBg"
[GIN] 2024/11/26 - 11:26:53 | 200 | 821.97µs | ::1 | GET "/fp"
[{"p.age":"333","p.taobao_id":"033","p.min_price":129,"pr.t_price":79},{"p.age":"222","p.taobao_id":"022","p.min_price":129,"pr.t_price":79}]