使用Golang的db.Query进行SQL连接查询

Question

使用Golang的db.Query进行SQL连接查询

8

我想查询一个 问题 和它的所有 答案。以下两个函数都可以正常工作。但问题是，我认为应该只用一个查询来完成这个任务（出于简洁起见，我删除了错误检查）。

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
}

我想以以下方式（或类似方式）使用连接查询：

SELECT * FROM table1 JOIN table2 ON table1.column = table2.column;

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
}

- twharmon

不久之前有一个类似的问题。请参见此答案，其中描述了如何使用主键作为鉴别器填充结构体。 - Martin Campbell

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- cn007b · Accepted Answer

通常情况下，它必须是这样的：

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
}

请注意，我有意将：
SELECT *替换为SELECT q.id，q.body，a.id，a.question_id，a.body
的目的是避免出现类似于以下错误:
panic: sql: expected 6 destination arguments in Scan, not 5
这种错误可能会在表中添加或删除某些列时发生，因此此查询更加健壮可靠。

而这只是基本实现，您可以使用更多字段进行扩展...

PS：为简洁起见，函数checkErr也省略了。