Golang sqlx convert right side of many to many join to array

php Editor Xigua will introduce to you in this article how Golang sqlx converts the right side of a many-to-many connection into an array. Golang is a powerful programming language, and sqlx is a popular Golang database tool. When using sqlx for many-to-many connections, we often need to convert the results on the right side into an array for easier processing and operation. This article will introduce in detail how to use sqlx and some examples to demonstrate this process. I hope it will be helpful to everyone.

Question content

I have two tables unit and unitimage with unit foreign key.

I execute this sql query

select un.*, array_agg(row(ui.is_main, ui.image, ui.id)) as unit_images
from unit un
inner join unitimage ui on ui.unit_id = un.id
group by un.id;

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

{"(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)"}

go language structure

type unit struct {
    id                     *int         `json:"id" db:"id"`
    name                   *string      `json:"name" db:"name"`
    ... a lot of fields
    unitimages             []unitimages `json:"unit_images" db:"unit_images"`
}

type unitimages struct {
    id *int `json:"id" db:"id"`
    image  *string `json:"image" db:"image"`
    ismain *bool   `json:"is_main" db:"is_main"`
}

sqlxcode

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",
unitsTable, unitImagesTable)
err := r.db.Select(&units, query)

I got the error"sql: Scan error on column index 45, name \"unit_images\": Scan not supported, storing driver.value of type []uint8 to type *[]*unitimages"

I am new to golang and I would like any tips on how to solve this problem. Maybe I chose the wrong way.

I would like to know the correct way to solve this problem.

Solution

It seems that the returned sql result is:

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)]

so

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

This is the correct idea, you have a unitimages array. but:

id *int `json:"id" db:"id"`
    image  *string `json:"image" db:"image"`
    ismain *bool   `json:"is_main" db:"is_main"`

Please note that id, image, is_main do not have sql columns, so go cannot map them.

Easy fix change unitimages to []any and then cast the contents into that array yourself like this:

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.

The above is the detailed content of Golang sqlx convert right side of many to many join to array. For more information, please follow other related articles on the PHP Chinese website!