英文:
Retrieve sum() result from psql using raw query with Gorm
问题
我的原始查询是：
SELECT
at."category" AS "category",
at."month" AS "month",
sum(at.price_aft_discount) as "sum",
sum(at.qty_ordered) as "sum2"
FROM
all_trans at
GROUP BY
at."category",
at."month"
ORDER BY
at."category" ASC,
at."month" ASC
在DBeaver中执行上述查询时，sum列会出现警告：sum: numeric(131089,0) (只读：没有对应的表列)。对于sum2也会出现相同的警告。
我使用Gorm在Go中查询并保存结果的代码如下：
type ATQueryResult struct {
category string `gorm:"column:category"`
month string `gorm:"column:month"`
sum float32 `gorm:"column:sum"`
sum2 float32 `gorm:"column:sum2"`
}
queryString := ... // 与上述查询相同
var result []ATQueryResult
db.Table(model.TableAllTrans).Raw(queryString).Find(&result)
fmt.Println(result[3])
但问题是，所有的result[i].sum和result[i].sum2都是0。当我在DBeaver上实时查询时，可以看到实际的非零和数值，尽管会出现上述警告。但是当我尝试将查询结果扫描到result中时，所有的sum值都是0。
英文:
My raw query is
SELECT
at."category" AS "category",
at."month" AS "month",
sum(at.price_aft_discount) as "sum",
sum(at.qty_ordered) as "sum2"
FROM
all_trans at
GROUP BY
at."category",
at."month"
ORDER BY
at."category" ASC,
at."month" asc
When executing above query in DBeaver, the sum columns give warning: sum: numeric(131089,0) (Read-only: No corresponding table column). Same warning is given for sum2
My code to query save the result in Go using Gorm is:
type ATQueryResult struct {
category string `gorm:"column:category"`
month string `gorm:"column:month"`
sum float32 `gorm:"column:sum"`
sum2 float32 `gorm:"column:sum2"`
}
queryString := ... // same as above
var result []ATQueryResult
db.Table(model.TableAllTrans).Raw(queryString).Find(&result)
fmt.Println(result[3])
But the issue is, all result[i].sum and result[i].sum2 is 0-zero. I can see the actual sum number (which is all non-zero) when querying live on DBeaver, albeit with the above warning. But when I tried to scan the query result on to result, all the sum values are 0.
答案1
得分: 2
字段必须被导出，以便于gorm（或者它在底层使用的任何东西）能够填充它们。尝试使用以下代码：
type ATQueryResult struct {
Category string `gorm:"column:category"`
Month string `gorm:"column:month"`
Sum float32 `gorm:"column:sum"`
Sum2 float32 `gorm:"column:sum2"`
}
英文:
The fields must be exported for gorm (or whatever it leverages under the hood) to be able to populate them. Try using:
type ATQueryResult struct {
Category string `gorm:"column:category"`
Month string `gorm:"column:month"`
Sum float32 `gorm:"column:sum"`
Sum2 float32 `gorm:"column:sum2"`
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论