我在 PostgreSQL 数据库中有 3 个表。

问题表:

| id (int) | text (text)                          |
|----------|--------------------------------------|
| 1        | What is your favorite color?         |
| 2        | What is your favorite football club? |

OPTIONS表:

| id (int) | text (text) |
|----------|-------------|
| 1        | red         |
| 2        | blue        |
| 3        | grey        |
| 4        | green       |
| 5        | brown       |

QUESTIONS_OPTIONS 表:

| question_id (int) | option_id (int) |
|-------------------|-----------------|
| 1                 | 1               |
| 1                 | 2               |
| 1                 | 3               |
| 1                 | 4               |
| 1                 | 5               |

在 Golang 应用程序中，我创建了这样的模型:

type Option struct {
    ID   int    `json:"option_id"`
    Text string `json:"option_text"`
}

type Question struct {
    ID int `json:"question_id"`
    Text string `json:"question_text"`
    Options []Option `json:"options"`
}

在 Controller 中我有这样的代码:

var GetQuestions = func(responseWriter http.ResponseWriter, request *http.Request) {
    rows, _ := database.DBSQL.Query("SELECT * FROM questions;")
    defer rows.Close()

    var questions []Question

    for rows.Next() {
        var question Question
        var options []Option

        queries, _ := database.DBSQL.Query(`select options.id as option_id, options.text as option_text from questions_options inner join questions on questions_options.question_id = ` + &question.ID + ` inner join options on questions_options.option_id = options.id`)
        queries.Close()

        for queries.Next() {
            var option Option
            if err := queries.Scan(&option.ID, &option.Text); err != nil {
                log.Println(err)
            }
            options = append(options, option)
        }

        if err := rows.Scan(&question.ID, &question.Text, options); err != nil {  // service raise error in this line: sql: expected 2 destination arguments in Scan, not 3
            log.Println(err)
        }

        questions = append(questions, question)
    }
    utils.Response(responseWriter, http.StatusOK, questions)
}

当我尝试发出 GET 请求以接受所有带有所有选项服务的问题时，这样的错误结果:

[
    {
        "question_id": 0,
        "question_text": "",
        "options": null
    },
    {
        "question_id": 0,
        "question_text": "",
        "options": null
    }
]

我哪里弄错了？

最佳答案

您应该将 queries.Close() 移动到循环的末尾，如下所示:

var GetQuestions = func(responseWriter http.ResponseWriter, request *http.Request) {
    rows, _ := database.DBSQL.Query("SELECT * FROM questions;")
    defer rows.Close()

    var questions []Question

    for rows.Next() {
        var question Question

        if err := rows.Scan(&question.ID, &question.Text); err != nil {
            log.Println(err)
            continue
        }

        queries, _ := database.DBSQL.Query(`select options.id as option_id, options.text as option_text from questions_options inner join questions on questions_options.question_id = $1 inner join options on questions_options.option_id = options.id`, question.ID)

        for queries.Next() {
            var option Option
            if err := queries.Scan(&option.ID, &option.Text); err != nil {
                log.Println(err)
            }
            question.Options = append(question.Options, option)
        }
        queries.Close()

        questions = append(questions, question)
    }
    utils.Response(responseWriter, http.StatusOK, questions)
}

关于sql - 如何返回嵌套的 JSON？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/54997768/