英文:
Golang db.Query with sql join
问题
我想查询一个问题及其所有答案。以下两个函数运行良好。问题是我认为应该使用一个函数和一个查询来完成这个任务（为了简洁起见，我删除了错误检查）。
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
}
我想以这种方式（或类似方式）使用联接查询：
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)
// 这里还有更多的代码
return question, answers, nil
}
英文:
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
}
答案1
得分: 11
一般来说，它应该是这样的：
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
}
请注意，我故意将以下内容替换为了上述代码中的内容：
<br>将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也被省略了。
英文:
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:
<br>SELECT * to SELECT q.id, q.body, a.id, a.question_id, a.body
<br>with purpose to avoid errors like:
<br>panic: sql: expected 6 destination arguments in Scan, not 5
<br>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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论