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.