Question 90493 in PostgreSQL Question posted in
The official documentation can be found here.

Scan a Sql Query sqlx.Rows into a Nested Structure in golang – Postgresql

I have three following tables:

create table A (
  a_id     varchar(256) not null unique,
  a_name   varchar(256)
);

create table B (
  b_id     varchar(256) not null,
  b_a_id   varchar(256) not null,
  b_name   varchar(256),
  FOREIGN KEY (b_a_id) REFERENCES a (a_id)
);

create table C (
  c_id     varchar(256) not null,
  c_a_id   varchar(256) not null,
  c_name   varchar(256),
  FOREIGN KEY (c_a_id) REFERENCES a (a_id)
);

insert into A(a_id, a_name) values('1234', 'a_name_1');

insert into B(b_id, b_a_id, b_name) values('B1','1234', 'b_name_1');
insert into B(b_id, b_a_id, b_name) values('B2','1234', 'b_name_2');

insert into C(c_id, c_a_id, c_name) values('C1','1234', 'c_name_1');
insert into C(c_id, c_a_id, c_name) values('C2','1234', 'c_name_2');
insert into C(c_id, c_a_id, c_name) values('C3','1234', 'c_name_3');

I have the following Structs in golang:

type A struct {
     a_id   string    `db:"a_id"`
     a_name string    `db:"a_name"`
     b      *B        `db:"b"`
     c      *C        `db:"c"`
}

type B struct {
    b_id    string     `db:"b_id"`
    b_name  string     `db:"b_name"`
    b_a_id  string     `db:"b_a_id"`
}

type C struct {
    c_id    string     `db:"c_id"`
    c_name  string     `db:"c_name"`
    c_a_id  string     `db:"c_a_id"`
}

I want to scan the rows I get from executing the join query:
SELECT * from A INNER JOIN B ON a_id=b_a_id inner join C on c_a_id=a_id;

Into Struct A using rows.StructScan() in Golang but I am not able to do that. How Do I scan
a join query result into a nested struct and I don’t want to individually scan each column as there are a lot of columns as a result of the join query going forward.

Tags:

2

Answers


  1. 0

    After some investigation, I came up with a solution that should work also for you. First, let me present the working code, and then I’ll explain the relevant parts:

    package main

import (
    "fmt"

    "github.com/jmoiron/sqlx"
    _ "github.com/lib/pq"
)

type A struct {
    Id   string `db:"a_id"`
    Name string `db:"a_name"`
    B
    C
}

type B struct {
    Id   string `db:"b_id"`
    AId  string `db:"b_a_id"`
    Name string `db:"b_name"`
}

type C struct {
    Id   string `db:"c_id"`
    AId  string `db:"c_a_id"`
    Name string `db:"c_name"`
}

func main() {
    db, err := sqlx.Open("postgres", "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    a := []A{}
    rows, err := db.Queryx("SELECT * from A INNER JOIN B ON a_id=b_a_id inner join C on c_a_id=a_id;")
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    for rows.Next() {
        var record A
        if err := rows.StructScan(&record); err != nil {
            panic(err)
        }
        a = append(a, record)
    }

    if err := rows.Err(); err != nil {
        panic(err)
    }

    for _, v := range a {
        fmt.Println(v)
    }
}

    Structs definition

    Here, I fixed the structs’ definition by embedding B and C into the A struct. Furthermore, I also fixed the name as long as they didn’t collide with the others (e.g. it’s completely safe to have the Id field in all of the three structs).

    Fetching data

    The other relevant part is fetching the data from Postgres. Here you’ve to use the Queryx method and provide to it the SQL query you’ve correctly written.
    However, you should use the methods provided by the database/sql package when dealing with multiple rows (as in our case). Next and Err are self-explanatory so I won’t spend any time over them.
    StructScan is the method that does the trick. It puts the current query into a loop-scoped variable called record of type A (which is our parent struct).

    If you give a try to this code it should work also for you, if not let me know!

    Login or Signup to reply.
  2. 0

    Please have a look at this answer Stackoverflow

    Using a light weight library Carta can map SQL results to nested structs.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search