使用Gorm通过原始查询从psql检索sum()结果

huangapple
go评论141阅读模式
英文:

Retrieve sum() result from psql using raw query with Gorm

问题

我的原始查询是：

  1. SELECT 
  2.   at."category" AS "category", 
  3.   at."month" AS "month", 
  4.   sum(at.price_aft_discount) as "sum", 
  5.   sum(at.qty_ordered) as "sum2" 
  6. FROM 
  7.   all_trans at 
  8. GROUP BY 
  9.   at."category", 
  10.   at."month" 
  11. ORDER BY 
  12.   at."category" ASC, 
  13.   at."month" ASC

在DBeaver中执行上述查询时，sum列会出现警告：sum: numeric(131089,0) (只读：没有对应的表列)。对于sum2也会出现相同的警告。

我使用Gorm在Go中查询并保存结果的代码如下：

  1. type ATQueryResult struct {
  2.    category string  `gorm:"column:category"`
  3.    month    string  `gorm:"column:month"`
  4.    sum      float32 `gorm:"column:sum"`
  5.    sum2     float32 `gorm:"column:sum2"`
  6. }
  8. queryString := ... // 与上述查询相同
  9. var result []ATQueryResult
  11. db.Table(model.TableAllTrans).Raw(queryString).Find(&result)
  13. fmt.Println(result[3])

但问题是，所有的result[i].sum和result[i].sum2都是0。当我在DBeaver上实时查询时，可以看到实际的非零和数值，尽管会出现上述警告。但是当我尝试将查询结果扫描到result中时，所有的sum值都是0。

英文:

My raw query is

  1. SELECT 
  2.   at."category" AS "category", 
  3.   at."month" AS "month", 
  4.   sum(at.price_aft_discount) as "sum", 
  5.   sum(at.qty_ordered) as "sum2" 
  6. FROM 
  7.   all_trans at 
  8. GROUP BY 
  9.   at."category", 
  10.   at."month" 
  11. ORDER BY 
  12.   at."category" ASC, 
  13.   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:

  1. type ATQueryResult struct {
  2.    category string  `gorm:"column:category"`
  3.    month    string  `gorm:"column:month"`
  4.    sum      float32 `gorm:"column:sum"`
  5.    sum2     float32 `gorm:"column:sum2"`
  6. }
  8. queryString := ... // same as above
  9. var result []ATQueryResult
  11. db.Table(model.TableAllTrans).Raw(queryString).Find(&result)
  13. 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（或者它在底层使用的任何东西）能够填充它们。尝试使用以下代码：

  1. type ATQueryResult struct {
  2.     Category string  `gorm:"column:category"`
  3.     Month    string  `gorm:"column:month"`
  4.     Sum      float32 `gorm:"column:sum"`
  5.     Sum2     float32 `gorm:"column:sum2"`
  6. }
英文:

The fields must be exported for gorm (or whatever it leverages under the hood) to be able to populate them. Try using:

  1. type ATQueryResult struct {
  2. 	Category string  `gorm:"column:category"`
  3. 	Month    string  `gorm:"column:month"`
  4. 	Sum      float32 `gorm:"column:sum"`
  5. 	Sum2     float32 `gorm:"column:sum2"`
  6. }

huangapple
  • 本文由 发表于 2022年10月27日 16:31:00
  • 转载请务必保留本文链接：https://go.coder-hub.com/74219010.html
  • dbeaver
  • go
  • go-gorm
  • psql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定