Golang db.Query with sql join

Question

I would like to query for a question and all of its answers. The following two functions work just fine. The problem is I think this should be done in one function, with one query. (I removed error checking for brevity).

func QuestionById(id string) (*Question, error) {
    question := new(Question)
    _ = db.QueryRow("select * from question where question.id = ?", id).Scan(
        &question.Id,
        &question.LessonId,
        &question.Body,
        &question.Type,
    )

    return question, nil
}

func AnswersByQuestionId(id string) ([]*Answer, error) {
    rows, _ := db.Query("select * from answer where question_id = ?", id)
    defer rows.Close()

    answers := make([]*Answer, 0)

    for rows.Next() {
        answer := new(Answer)

        _ = rows.Scan(&answer.Id, &answer.Body, &answer.QuestionId, &answer.Correct)

        answers = append(answers, answer)
    }
    _ = rows.Err()

    return answers, nil
}

I would like to use a join query in this way (or something similar):

func QuestionByIdAndAnswers(id string) (*Question, []*Answer error) {
    rows, _ := db.Query("select * from question join answer on question.id = answer.question_id where question.id = ?", id)

    // more stuff here

    return question, answers, nil
}

There was a similar question about this not long ago. See this answer which describes how to populate a struct using a primary key as a discriminator. — Martin Campbell
– Martin Campbell, Commented Aug 11, 2017 at 14:50

cn0047 · Accepted Answer · 2019-09-02 15:27:08Z

14

In general it must be something like this:

func QuestionByIdAndAnswers(id string) (*Question, []*Answer, error) {
  query := `
    SELECT q.id, q.body, a.id, a.question_id, a.body
    FROM question AS q
    JOIN answer AS a ON q.id = a.question_id
    WHERE q.id = ?
  `
  rows, err := db.Query(query, id)
  checkErr(err)

  question := &Question{}
  for rows.Next() {
    answer := &Answer{}
    err = rows.Scan(
      &question.ID,
      &question.Body,
      &answer.ID,
      &answer.QuestionID,
      &answer.Body,
    )
    checkErr(err)
    question.Answers = append(question.Answers, answer)
  }

  return question, question.Answers, nil
}

Please pay attention, I intentionally replaced:
SELECT * to SELECT q.id, q.body, a.id, a.question_id, a.body
with purpose to avoid errors like:
panic: sql: expected 6 destination arguments in Scan, not 5
which may occur when some columns added or deleted from table, so this query more robust.

And this is just basic implementation, you can extend it with more fields...

PS: Function checkErr also omitted for brevity.

answered Sep 2, 2019 at 15:27

cn0047

17.2k7 gold badges61 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Muhammed Ozdogan Over a year ago

Let's say the question table is really big and has over 20 columns whereas the answer table has only 3 or 5 columns. When you join two tables you will retrieve the same question information which allocates unnecessary memory both on golang side and the DB side, because every row includes duplicate question information only answer information differs for each row. Maybe sometimes running two queries make sense right? What is your personal opinion about repeating the same data for each row?

Collectives™ on Stack Overflow

Golang db.Query with sql join

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related