skip to Main Content

I have cases with nested results in JSON. here’s the sample result,

{
  "total_result" : 25,
  "questions" : [

    {
      "id" : 1,
      "text" : "the question of user 1 here",
      "user" : {
        "id" : 5,
        "name" : "user 5",
      },
      "answers" : [
        {
          "id" : 5,
          "text" : "first answer to user 1 question",
          "user" : {
            "id" : 10,
            "name" : "user 10",
           }
        },
        {
          "id" : 6,
          "text" : "second answer to user 1 question",
          "user" : {
            "id" : 11,
            "name" : "user 11",
           }
        },
        {
          "id" : 10,
          "text" : "third answer to user 1 question",
          "user" : {
            "id" : 12,
            "name" : "user 12",
           }
        }
      ]
    },

    {
      "id" : 2,
      "text" : "the question by user 2 here",
      "user" : {
        "id" : 6,
        "name" : "user 6",
      },
      "answers" : [
        {
          "id" : 5,
          "text" : "first answer to user 2 question",
          "user" : {
            "id" : 30,
            "name" : "user 30",
           }
        },
        {
          "id" : 6,
          "text" : "second answer to user 2 question",
          "user" : {
            "id" : 20,
            "name" : "user 20",
           }
        },
        {
          "id" : 10,
          "text" : "third answer to user 2 question",
          "user" : {
            "id" : 1,
            "name" : "user 1",
           }
        }
      ]
    },

  ]
}

my struct goes here,

type Question struct {
  Id int 
  Text string ...
  User User ...
  Answer []Answer ...
}
type User struct {
  Id int ...
  Name string ...
}
type Answer struct {
  Id int ...
  Text string ...
  User User ...
}

here’s the query to get questions and user.Detail

query := "select text, user_id from questions limit 10 offset 10"
rows, err, := db.QueryCtx(ctx, query)
//handel error

var questions []Question
var userIds []string
for rows.Next() {
  var q Question
  var userId string
  //scan to `question` and `userId`
  questions = append(questions, q)
  userIds = append(questions, userId)
}

here’s the query to get the user to the question

query = "select name from users where id = any($1)"
userRows, err := db.QueryCtx(ctx, query, pq.Array(userIds))
//handle error
var users []User
//scan and store to users

here’s the query to get answers to the question

query = "select answers.id, answers.text, u.id, u.name from answers join questions as q on q.id=answers.question_id join users as u on u.id=answers.user_id where answers.questions_id=$1"
for i := 0; i < len(questions); i++{
  rowAnswer, err := db.QueryCtx(ctx, query, questions[i].Id)
  //handle error
  var answers []Answer
  for rowAnswer.Next(){
    var answer Answer
    //scan to answer
    append = (answers, answer)
  }
  questions[i].User.Id = users[i].Id
  questions[i].User.Name = users[i].Name
  questions[i].Answer = answers
}

users table

id name
1 name

questions table

id text user_id
1 text 1

answers table

id text question_id user_id
1 text 1 1

the result is good, nothing wrong with the code and the result. but, I’m thinking about the n+query case, because I do looping to get the answers. my question is, is it reasonable to do that, or is any good advice for my code?

2

Answers


  1. In get questions, you need var questionIds and mapQuestionIdx

    • add select id
    • questionIds is for get question id, so you can query where in
    • mapQuestionIdx is for save question id and index in slice. Note question_id is key and index is value.

    so it look like this

    query := "select id, text, user_id from questions limit 10 offset 10"
    rows, err, := db.QueryCtx(ctx, query)
    //handel error
    
    var questions []Question
    var userIds []string
    
    questionIds := make([]int, 0, 10) // because limit is 10
    mapQuestionIdx := make(map[int]int)
    idx := 0
    
    for rows.Next() {
      var q Question
      var userId string
      //scan to `question` and `userId`
      questions = append(questions, q)
      userIds = append(questions, userId)
    
      questionIds = append(questionIds, q.ID)
      mapQuestionIdx[q.ID] = idx
      idx++
    }
    

    On query to get answers to the question

    • add select question_id
    // add select question_id
    query = "select q.id question_id, answers.id, answers.text, u.id, u.name from answers join questions as q on q.id=answers.question_id join users as u on u.id=answers.user_id where answers.questions_id in ($1)"
    
      rowAnswer, err := db.QueryCtx(ctx, query, questionIds) // questionIds from above
      //handle error
      for rowAnswer.Next(){
        var answer Answer
        var question_id int
        //scan to answer and question_id
    
        i := mapQuestionIdx[question_id]
        
        questions[i].User.Id = users[i].Id
        questions[i].User.Name = users[i].Name
    
        if questions[i].Answer == nil {
          questions[i].Answer = make([]answer, 0)
        }
        questions[i].Answer = append(questions[i].Answer, answer)
      }
    
    Login or Signup to reply.
  2. You can make use of a combination of struct tag and encoding/json to un-marshal your result properly.

    Example for your use case:

    Redeclare your type as

    type Question struct {
      Id int `json:"id"`
      Text string `json:"text"`
      User User `json:"user"`
      Answer []Answer `json:"answer"`
    
    // Fill the rest yourself
    ...
    }
    

    To unmarshal simply:

    
     someDummyQuestionsResult := []byte{
        {
         // some example
        }
     }
    
     var questions []Question
     err := json.Unmarshal(someDummyQuestionsResult , &questions)
    
     fmt.Println(questions) 
    
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search