How to use pgx.CollectRows for nested struct with repeated row values

84 Views Asked by At

I have three tables called groups, accounts, and group_account_membership cross table with many to many relationship.

SELECT groups.id, groups.name, group_account_membership.id from group join group_account_membership on group.id = group_account_membership.account_id

ID Name group_account_membership_id accountname
1 group1 123 abcd
1 group1 456 efhd

My struct looks like:

type Group struct {
    ID      uuid.UUID
    Name    string
    Accounts []*Accounts
    
}
type Account struct{
      ID     uuid.UUID
      name   uuid.UUID
}

query:= SELECT groups.id, groups.name, group_account_membership.id from group join group_account_membership on group.id = group_account_membership.account_id where id=$1
rows, _ := a.conn.Query(ctx, query, 1)

group, err := pgx.CollectRows(selectQuery, pgx.RowToAddrOfStructByName[Group])

However, when I retrieve the data in Golang using the pgx.CollectRows() function, I want to structure it in a way that the id and name columns are collected only once for group, while group_account_membership_id and accountname are inserted as arrays values in the same group.

I can do this by two queries but i only want to query the db once.

0

There are 0 best solutions below