I’ve cases that return a nested json. sample
type Post struct{
ID int json:"id"
Title string json:"title"
Author Author json:"author"
Comment []Comment json:"comment"
}
type Author struct {
ID int json:"id"
Name string json:"name"
}
type Comment struct {
ID int json:"id"
Text string json:"text"
}
and the return should be
{
"total_result" : 50,
"current_page" : 1,
"posts" : [
{
"title" : "title 1",
"author" : [
"name" : "author 1",
],
"comment" : [
{"text" : "commentary 1.1"}, {"text" : "commentary 1.2"}, ....
]
},
{
"title" : "title 2",
"author" : [
"name" : "author 2",
],
"comment" : [
{"text" : "commentary 2.1"}, {"text" : "commentary 2.2"}, ....
]
}
]
}
my question is, is this a good idea to execute a query like this :
query := "query that gets the post data with limit and offset of course"
rows, err := db.QueryContext(ctx, query)
//handle error
var record []Post
for rows.Next(){
var post Post
if err := rows.Scan(&post....) {..}
record = append(record, post)
}
then, I do for-loop to get the author
for i := 0; i < len(record); i++ {
var author Author
query = "select id,name from authors where id=$1"
rows, err := db.QueryContext(ctx, query, record[i].Id)
//handel error
//scan data to author
record[i].Author.Id = author.Id
record[i].Author.Name = author.Name
}
same process to get comments if the post has comments.
but, I’m just thinking about how can I do with just 1 process to get the Author and the Comments. it’s like while I scan the first query (select posts query) and then I do looping inside of
for rows.Next()
post scan process, but I got fail because the first query should be rows.Close(). but when I do that, the Scan process can’t process because the connection is over.
Thanks in advance everyone