Go语言查询列引用不起作用

I have a query in GO that returns a different response from mysql.

here is the query:

SELECT DISTINCT 
        questions.id as question_id, 
        questions.question, 
        questions.priority, 
        questions.type
    FROM questions 
    LEFT JOIN profile ON profile.user_id = ? 
    LEFT JOIN group ON group.user_id = profile.user_id  
    WHERE questions.status = 1 
    AND group.status = 1 
    AND questions.id NOT IN (SELECT DISTINCT question_id FROM answers WHERE user_id = profiles.user_id) 

When I run this on mysql terminal it returns nothing as expected. But when I try to run this on GO lang it has a return which is not supposed to be returned since it has already been filtered in the NOT IN clause which are all the answered questions. When I tried to change the profiles.user_id to a specific value it returns the expected output.

I think the use of column parameters in not working in GO. It will be a quick fix if I change the users.profile to a specific variable but there are other queries that needs to use that feature in order to achieve my expected output.

I tried using stmt.Prepared statement and the db.Query() with same results

Go code:

query :=    "    SELECT DISTINCT " +
        "        questions.id as question_id, " +
        "        questions.question, " +
        "        questions.priority, " +
        "        questions.type " +
        "    FROM questions " +
        "    LEFT JOIN profile ON profile.user_id = 1627 " +
        "    LEFT JOIN group ON group.user_id = profile.user_id  " +
        "    WHERE questions.status = 1 " +
        "    AND group.status = 1 " +
        "    AND questions.id NOT IN (SELECT DISTINCT question_id FROM answers WHERE user_id = profiles.user_id); "

stmt, err := db.Prepare(query)
if err != nil {
    checkErr(err) // proper error handling instead of panic in your app
}
defer stmt.Close() // Close the statement when we leave main() / the program terminates

userId := json.userId

args := []interface{}{}
args = append(args, userId)

start := time.Now()
rows, err := stmt.Query(args...)
elapsed := time.Since(start)

if err != nil {
    checkErr(err) // proper error handling instead of panic in your app
}

// Fetch rows
for rows.Next() {
    // get RawBytes from data
    err = rows.Scan(&question.QuestionId, &question.Question, &question.Priority, &question.Type)
    questions = append(questions, question)
    if err != nil {
        checkErr(err) // proper error handling instead of panic in your app
    }
}
defer rows.Close()
defer db.Close()

Is there any workaround for it to work ?

Thanks for your response

You can use question mark '?' in the query in places where you expect a parameter:

age := 27
rows, err := db.Query("SELECT name FROM users WHERE age=?", age)
if err != nil {
        log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
        var name string
        if err := rows.Scan(&name); err != nil {
                log.Fatal(err)
        }
        fmt.Printf("%s is %d
", name, age)
}
if err := rows.Err(); err != nil {
        log.Fatal(err)
}

Also, you can use back tick ` for a multi-line string in your code (rather than concatenation with +). It would make your code easier to read.

query :=    `SELECT DISTINCT
    questions.id as question_id, 
    questions.question, 
    questions.priority, 
    questions.type 
FROM questions 
LEFT JOIN profile ON profile.user_id = 1627 
LEFT JOIN group ON group.user_id = profile.user_id  
WHERE questions.status = 1 
AND group.status = 1 
AND questions.id NOT IN (SELECT DISTINCT question_id FROM answers WHERE user_id = profiles.user_id); `