Sttcwzrd Sttcwzrd - 3 months ago 26
MySQL Question

Golang MySQL querying undefined amount of args using IN operator

I'm trying to use a MySQL query using the IN operator with undefined amount of arguments into my Golang project.

I'm using the package

github.com/go-sql-driver/mysql
and tried to build my solution on this Stackoverflow answer : How to execute an IN lookup in SQL using Golang?

I've read some similar posts giving me some advices about the way to go, but I'm stuck on the execution part of the query, because it does not allow the direct use of a slice as argument.

//converting my form args []string into []int
var args []int
for _, v := range r.Form["type"] {
t, _ := strconv.Atoi(v)
args = append(args, t)
}

sql := "SELECT id, name FROM resources WHERE id IN (SELECT resource_id FROM resources_types WHERE type_id IN (?" + strings.Repeat(",?", len(args)-1) + "))"
fmt.Println("Query : ", sql)
stmt, _ := db.Prepare(sql)
rows, err := stmt.Query(args)
defer stmt.Close()


Golang returns me an error at execution :


Query : SELECT id, name FROM resources WHERE id IN (SELECT resource_id
FROM resources_types WHERE type_id IN (?,?))
"sql: statement expects 2 inputs; got 1"


It works when I try with

rows, err := stmt.Query(args[0], args[1])


But as I need an undefined number of arguments, it isn't a solution. Is it at least possible to get it working with MySQL ?

Answer

Stmt.Query() has a variadic parameter:

func (s *Stmt) Query(args ...interface{}) (*Rows, error)

This means you can use the ellipsis ... to pass a slice value as the value of the variadic parameter, but that slice must be of type []interface{}, e.g.:

var args []interface{}
for _, v := range r.Form["type"] {
    t, _ := strconv.Atoi(v)
    args = append(args, t)
}

// ...

rows, err := stmt.Query(args...)

As an alternative, you could pre-build the SQL query and execute without passing query arguments, for an example see Go and IN clause in Postgres.