如何拼接字符串数组以适配SQL NOT IN查询？（Go/Buffalo场景）

Got it, let's work through this problem step by step. Your current code has two key issues keeping you from getting the right SQL query, plus a potential edge case to watch out for. Here's how to fix it:

1. Stop using c.Params for request body data #

First off, you’re trying to pull currentList from c.Params("currentList"), but that’s for URL path parameters (like /sentences/:currentList). Your currentList lives in the JSON request body, which you’ve already parsed into the req variable. So you need to use req.CurrentList instead of c.Params here.

2. Let GORM handle the array automatically (no manual string splicing needed!) #

Good news: GORM natively supports slice parameters for IN/NOT IN clauses. You don’t need to manually join the array into a string—doing that would risk SQL injection and syntax errors anyway. Just pass the slice directly to the Where method, and GORM will expand it into the correct SQL syntax automatically.

3. Handle the empty array edge case #

If currentList is empty, the generated SQL would have NOT IN (), which is invalid syntax. Add a check for this before running the query to avoid database errors.

Fixed Code Example #

Here’s your updated Create function with all these fixes:

import "errors"
import "net/http"

func (usr *SentenceResource) Create(c buffalo.Context) error {
    req := sentenceCreateReq{}
    // Don't forget to handle parsing errors! Your original code skipped this
    if err := parseReqBody(c.Request(), &req); err != nil {
        return c.Error(http.StatusBadRequest, err)
    }

    ...
    sentence := models.Sentence{}
    tx := usr.Tx // Make sure you have a valid transaction/DB instance here

    // Guard against empty currentList to avoid invalid SQL
    if len(req.CurrentList) == 0 {
        return c.Error(http.StatusBadRequest, errors.New("currentList cannot be empty"))
    }

    // Correct way to use the array in NOT IN
    err := tx.Limit(1).Where("word NOT IN (?)", req.CurrentList).First(&sentence)
    if err != nil {
        // Handle query errors (e.g., no record found)
        return err
    }
    ...
}

What the Generated SQL Looks Like #

When you pass req.CurrentList = ["hey", "cool"], GORM will generate this valid SQL:

SELECT * FROM sentences WHERE word NOT IN ('hey', 'cool') LIMIT 1;

This approach is safe (uses parameterized queries to prevent SQL injection) and clean—no messy string manipulation required.

内容的提问来源于stack exchange，提问作者bigpotato