Golang Gorm如何在整数切片中使用`where in`

Question

From http://jinzhu.me/gorm/advanced.html#sql-builder, I should be able to update multiple rows using WHERE IN with a single (?) and passing a slice to the single ? as opposed to WHERE IN (?,?,?,?).

Example from jinzhu.me as follows: db.Exec("UPDATE orders SET shipped_at=? WHERE id IN (?)", time.Now, []int64{11,22,33}). Here is an example of gorm's tests showing it working. https://github.com/jinzhu/gorm/blob/021d7b33143de37b743d1cf660974e9c8d3f80ea/main_test.go#L449

This doesnt work for me however:

var availableIds []int
for _, p := range v.Products {
    availableIds = append(availableIds, p.Id)
}

log.Println(availableIds)

db.Exec("UPDATE product_supplier SET deleted_at=? WHERE supplier_id = ? AND sku NOT IN (?)", time.Now(), 3, availableIds)

Output:

2016/04/19 07:48:44 [336 338 337 306 329 94 79 43 57 313 108 122 126 127 124 125 123 221 93 330 335 333 312]

(sql: expected 2 arguments, got 25)

When I try hardcoding, I get the same issue:

db.Exec("UPDATE product_supplier SET deleted_at=? WHERE supplier_id = ? AND sku NOT IN (?)", time.Now(), 3, []int{313, 108})

Output:

(sql: expected 2 arguments, got 4)

Solution:

The code is not actually bugged at all. Was me being silly - I had an extra parameter in my actual code than I should have. I just didn't translate it for stack overflow correctly. My bad.

Answer 1

The natural of prepare functionality prevents you from passing a slice as an argument.

The implementation of db.Exec in Go is to first prepare the query (including the variable placeholders) and then send the arguments.

If you wonder why prepare prevents you from passing a slice, read this answer.

As a workaround, the same amount of placeholders as the size of your slice need to be concatenated in your program, which should be generating a query like this:

... WHERE supplier_id = ? AND sku NOT IN (?, ?, ?, ?)

Example code:

Ids := []int{1, 2, 3}
query := "... WHERE supplier_id = ? AND sku NOT IN (" + genVar(len(Ids)) + ")"
db.Exec(query, 3, Ids)

UPDATE:

It turns Gorm's implementation of db.Exec method is not using prepare functionality from DBMS, but it concatenate strings in the driver.

My diagnosis now is that there might be something wrong in the dependencies.

Are you importing Gorm like it shows in http://jinzhu.me/gorm/ ?