Query relational database and map the data into structure with nested slice of another structure type

Question

What are good approaches to query data from SQL Database and map it into a structure which contains a slice of another structure type?

In my case, I am using Postgres SQL, pgx to query data, and scany to map the row results into objects.

I have a posts table and comments table. The relationship is that one post can have many comments.

These are my models representing the database tables

package models

type Post struct {
    Id int64
    // more fields

    Comments []Comment
}

type Comment struct {
    Id     int64
    PostId int64
    // more fields

    Post Post
}

This is my PostRepository object

package repository

import (
    "context"
    "fmt"

    "github.com/georgysavva/scany/pgxscan"
    "github.com/jackc/pgx/v4/pgxpool"
    "kosev.com/databases/models"
)

type PostRepository struct {
    dbPool *pgxpool.Pool
}

func NewPostRepository(dbPool *pgxpool.Pool) *PostRepository {
    return &PostRepository{
        dbPool: dbPool,
    }
}

func (pr *PostRepository) GetAll() []models.Post {
    query := `SELECT * FROM posts
              LEFT OUTER JOIN "comments"
              ON "comments".post_id  = posts.id;`

    var posts []models.Post

    rows, _ := pr.dbPool.Query(context.Background(), query)

    err := pgxscan.ScanAll(&posts, rows)

    if err != nil {
        fmt.Println("Error: ", err.Error())
    }

    return posts
}

What I am trying to achieve is to get all posts and in each post to have a slice of the corresponding comments for this post

Now I know that I can

query all posts
loop through the posts and build a query that will get me the comments for each post
add the appropriate comments into the slice of the appropriate post

I am wondering if there is the better solution, one which will only require me to execute the query and some Scan() method that is smart enough to add the comments inside the posts.

score 0 · Answer 1 · answered Oct 04 '22 at 09:39

I tried using the json capacity of postgres, it could be OK for simple database but I'm not sure about the perfs for more complexes queries

docker-compose

version: '3'
services:

  db:
    image: postgres:10.16
    depends_on:
      - adminer  
    environment:
      POSTGRES_HOST_AUTH_METHOD: trust
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
      POSTGRES_DB: test
    volumes:
      - ./volume:/var/lib/mysql
      - ./sql/:/docker-entrypoint-initdb.d/
    ports:
      - 5432:5432

  adminer:
    image: adminer:latest
    environment:
      ADMINER_DEFAULT_SERVER: localhost
    ports:
      - 8080:8080

folder sql to init db

    CREATE TABLE IF NOT EXISTS posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(20)
);

CREATE TABLE IF NOT EXISTS comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER,
  txt TEXT
);

INSERT INTO posts (title)
VALUES ('first'),('second');

INSERT INTO comments (post_id, txt)
VALUES (1,'cool'), (1,'notcool'), (2,'third');

on adminer (localhost:8080) you can try this query

    SELECT p.id, p.title, json_agg(c) as comments
    FROM posts p LEFT JOIN comments c ON c.post_id = p.id
    GROUP BY p.id;

you will get the nested comments in json format

1   first   [{"id":1,"post_id":1,"txt":"cool"}, {"id":2,"post_id":1,"txt":"notcool"}]
2   second  [{"id":3,"post_id":2,"txt":"third"}]

now it's possible to scan it's nested struct fields

package main

import (
    "context"
    "fmt"
    "os"

    "github.com/jackc/pgx/v5"
)

type Post struct {
    Id       int64
    Title    string
    Comments []Comment
}

type Comment struct {
    Id  int64
    Txt string
}

func main() {
    urlExample := "postgres://user:password@localhost:5432/test"
    conn, err := pgx.Connect(context.Background(), urlExample)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
        os.Exit(1)
    }
    defer conn.Close(context.Background())

    rows, err := conn.Query(context.Background(),
        `SELECT p.id, p.title,  
    json_agg(c) as comments
        FROM posts p
        LEFT JOIN comments c ON c.post_id = p.id
        GROUP BY p.id;`)

    defer rows.Close()
    for rows.Next() {
        var post Post
        err := rows.Scan(&post.Id, &post.Title, &post.Comments)
        if err != nil {
            panic(fmt.Sprintf("%v", err))
        }
        fmt.Println(post)
    }
}

ref : example page for query postgres postgres building json example

Query relational database and map the data into structure with nested slice of another structure type

1 Answers1