将多对多连接的右侧转换为数组的 Golang sqlx 代码部分。

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

Golang sqlx Convert right side of join of many to many into array

问题

我有两个表，Unit和UnitImage，UnitImage有一个外键指向Unit。

我执行了以下SQL查询：

  1. SELECT un.*, array_agg(ROW(ui.is_main, ui.image, ui.id)) AS unit_images
  2. FROM unit un
  3. INNER JOIN unitimage ui ON ui.unit_id = un.id
  4. GROUP BY un.id;

作为响应，我得到了所有Unit字段和连接字段unit_images，格式如下：

  1. {"(f,photos/units/unit_37/90_big.jpg,108)","(f,photos/units/unit_37/91_big.jpg,109)","(f,photos/units/unit_37/92_big.jpg,110)","(f,photos/units/unit_37/93_big.jpg,111)"}

Golang结构体如下：

  1. type Unit struct {
  2. 	ID                     *int         `json:"id" db:"id"`
  3. 	Name                   *string      `json:"name" db:"name"`
  4.     ... 很多其他字段
  5. 	UnitImages             []UnitImages `json:"unit_images" db:"unit_images"`
  6. }
  8. type UnitImages struct {
  9. 	ID *int    `json:"id" db:"id"`
  10. 	Image  *string `json:"image" db:"image"`
  11. 	IsMain *bool   `json:"is_main" db:"is_main"`
  12. }

sqlx代码如下：

  1. query := fmt.Sprintf("SELECT un.*, array_agg(ROW(ui.id, ui.image, ui.is_main)) as unit_images FROM %s un INNER JOIN %s ui ON ui.unit_id = un.id GROUP BY un.id",
  2. unitsTable, unitImagesTable)
  3. err := r.db.Select(&units, query)

我得到了一个错误信息："sql: Scan error on column index 45, name \"unit_images\": unsupported Scan, storing driver.Value type []uint8 into type *[]*UnitImages"

我是一个Golang的新手，我想知道如何解决这个问题。也许我选择了错误的方法。

我想知道解决这个问题的正确方法。

英文:

I have two tables
Unit and UnitImage with foreign key to Unit.

I do this sql query

  1. SELECT un.*, array_agg(ROW(ui.is_main, ui.image, ui.id)) AS unit_images
  2. FROM unit un
  3. INNER JOIN unitimage ui ON ui.unit_id = un.id
  4. GROUP BY un.id;

In response i got all unit fields and joined field unit_images like this

  1. {"(f,photos/units/unit_37/90_big.jpg,108)","(f,photos/units/unit_37/91_big.jpg,109)","(f,photos/units/unit_37/92_big.jpg,110)","(f,photos/units/unit_37/93_big.jpg,111)"}

Golang Structs

  1. type Unit struct {
  2. 	ID                     *int         `json:"id" db:"id"`
  3. 	Name                   *string      `json:"name" db:"name"`
  4.     ... A lot of fields
  5. 	UnitImages             []UnitImages `json:"unit_images" db:"unit_images"`
  6. }
  8. type UnitImages struct {
  9. 	ID *int `json:"id" db:"id"`
  10. 	Image  *string `json:"image" db:"image"`
  11. 	IsMain *bool   `json:"is_main" db:"is_main"`
  12. }

sqlx code

  1. query := fmt.Sprintf("SELECT un.*, array_agg(ROW(ui.id, ui.image, ui.is_main)) as unit_images FROM %s un INNER JOIN %s ui ON ui.unit_id = un.id GROUP BY un.id",
  2. unitsTable, unitImagesTable)
  3. err := r.db.Select(&units, query)

I got an error "sql: Scan error on column index 45, name \"unit_images\": unsupported Scan, storing driver.Value type []uint8 into type *[]*UnitImages"

I'm a newbie in golang, i'd like to get any tips how to resolve this issue. Maybe i choose the wrong way.

I want to know the right way of resolving this issue.

答案1

得分: 0

看起来返回的 SQL 结果如下：

  1. un.id | un.name | un.description | unit_images
  2. ------+---------+----------------+---------------------------------------------------------------------
  3. 1     | Unit A  | Description A  | [(true, 'image1.jpg', 1), (false, 'image2.jpg', 2), (false, 'image3.jpg', 3)]
  4. 2     | Unit B  | Description B  | [(true, 'image4.jpg', 4), (true, 'image5.jpg', 5), (false, 'image6.jpg', 6)]
  5. 3     | Unit C  | Description C  | [(true, 'image7.jpg', 7), (false, 'image8.jpg', 8), (false, 'image9.jpg', 9)]

所以

  1. `UnitImages []UnitImages `json:"unit_images" db:"unit_images"`

是正确的想法，你有一个 UnitImages 的数组。但是：

  1.     ID *int `json:"id" db:"id"`
  2.     Image  *string `json:"image" db:"image"`
  3.     IsMain *bool   `json:"is_main" db:"is_main"`

请注意，没有 SQL 列对应 id、image、is_main，所以 Go 无法进行映射。

简单的解决方法是将 UnitImages 改为 []any，然后自己对数组中的内容进行转换，例如：

  1. for _, item := range thing.UnitImages {
  2.   isMain := item[0].(bool)
  3.   image := item[1].(string)
  4.   id := item[2].(int64)
  6.   //TODO 使用这些变量做一些操作
  7. }
  9. 或者你可以使用 `pg.StringArray` 类型。
  11. <details>
  12. <summary>英文:</summary>
  14. Looks like the sql results comming back would be:

un.id | un.name | un.description | unit_images
------+---------+----------------+---------------------------------------------------------------------
1 | Unit A | Description A | [(true, 'image1.jpg', 1), (false, 'image2.jpg', 2), (false, 'image3.jpg', 3)]
2 | Unit B | Description B | [(true, 'image4.jpg', 4), (true, 'image5.jpg', 5), (false, 'image6.jpg', 6)]
3 | Unit C | Description C | [(true, 'image7.jpg', 7), (false, 'image8.jpg', 8), (false, 'image9.jpg', 9)]

  2. So

UnitImages []UnitImages json:"unit_images" db:"unit_images"`

  2. Is the right idea, you have an array of UnitImages. BUT:
  1. ID *int `json:&quot;id&quot; db:&quot;id&quot;`
  2. Image  *string `json:&quot;image&quot; db:&quot;image&quot;`
  3. IsMain *bool   `json:&quot;is_main&quot; db:&quot;is_main&quot;`
  2. Notice there is no sql columns for `id`, `image`, `is_main` so go has no way to map them.
  4. Easy fix to change UnitImages to []any and then cast the stuff in that array yourself like:

for _, item := range thing.UnitImages {
isMain := item[0].(bool)
image := item[1].(string)
id := item[2].(int64)

//TODO do something with these vars
}

Or you could use pg.StringArray type.

  2. </details>

huangapple
  • 本文由 发表于 2023年7月10日 19:54:55
  • 转载请务必保留本文链接：https://go.coder-hub.com/76653484.html
  • aggregate
  • go
  • postgresql
  • sqlx
匿名

发表评论

匿名网友

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

确定