postgresqlgoaggregatesqlx

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

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

I do 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 unit fields and joined 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)"}

Golang Structs

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"`
}

sqlx code

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 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.

Solution

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

    So

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

    Is the right idea, you have an array of UnitImages. BUT:

        ID *int `json:"id" db:"id"`
    Image  *string `json:"image" db:"image"`
    IsMain *bool   `json:"is_main" db:"is_main"`

    Notice there is no sql columns for id, image, is_main so go has no way to map them.

    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.