Sqlx: Named parameters don't work with IN statement

[pt-arvind]

arg := map[string]interface{}{
    "published": true,
    "authors": []{8, 19, 32, 44},
}
query, args, err := sqlx.Named("SELECT * FROM articles WHERE published=:published AND author_id IN (:authors)", arg)
query, args, err := sqlx.In(query, args...)
query = db.Rebind(query)
db.Query(query, args...)

this snippet is from http://jmoiron.github.io/sqlx/#namedParams and it's not valid go syntax. When I tried to use something similar, except with a string array instead of int array, I receive: sql: converting argument $3 type: unsupported type []string, a slice of string

here's what my code looks like:

    customLineItemIDs := []string{"7edd5b7e-fd73-11e8-9d4f-dca90492a8b7"}
    tx, err := db.Beginx()
    if err != nil {
        panic("fatal error creating transaction")
    }

    argMap := map[string]interface{}{
        "category_name":        "test category name",
        "custom_line_item_ids": customLineItemIDs,
    }

    query := fmt.Sprintf(`
        UPDATE producttable
        SET productname = :category_name, productcode = :category_name
        WHERE recordid IN (:custom_line_item_ids)
        `)
    query1, args, err := sqlx.Named(query, argMap)
    if err != nil {
        fmt.Printf("%+v\n", err)
        panic("fatal error with doing invoice and billing update")
    }

        query2 := tx.Rebind(query1)
    _, err = tx.Exec(query2, args...)

This looks to me like a bit of a bug with the library. Perhaps I'm an idiot and not using it properly but I scoured the web pretty good and I didn't see anything for this use case.

[xdefrag]

@pt-arvind I think answer lies in documentation http://jmoiron.github.io/sqlx

When this gets prepared as a statement on the backend, the bindvar ? will only correspond to a single argument, but what is often desired is for that to be a variable number of arguments depending on the length of some slice.

So you must wrap your query and args with sqlx.In:

var levels = []int{4, 6, 7}
query, args, err := sqlx.In("SELECT * FROM users WHERE level IN (?);", levels)

// sqlx.In returns queries with the `?` bindvar, we can rebind it for our backend
query = db.Rebind(query)
rows, err := db.Query(query, args...)

And with NamedQueries:

arg := map[string]interface{}{
    "published": true,
    "authors": []{8, 19, 32, 44},
}
query, args, err := sqlx.Named("SELECT * FROM articles WHERE published=:published AND author_id IN (:authors)", arg)
query, args, err := sqlx.In(query, args...)
query = db.Rebind(query)
db.Query(query, args...)

But it confuses me at first too.

[pt-arvind]

@xdefrag hey, thanks for the reply. In the example I showed above (now edited to include go syntax highlighting) I actually quote that exact documentation example and show why it does not work.

Basically, I followed the instructions in the docs and they do not work. I won't deny that I didn't dig too deeply into the source for this; I was under deadline pressure so I just wrote a helper function to assemble my own IN statement (and lose the prepared statement protections but it doesn't matter for my specific use case because the items in the IN statement are generated by software not humans).

I've used the IN statement and particularly sqlx.In before in various pieces of code, but I haven't done it in a situation like this where there are IN parameters in addition to others.

[chalda]

Hi just ran into this as well and am still struggling creating a named query that also unrolls a slice into the args. Ive been trying to make sense of the docs but this bit here

query, args, err := sqlx.Named("SELECT * FROM articles WHERE published=:published AND author_id IN (:authors)", arg)
query, args, err := sqlx.In(query, args...)

is just plain not valid gocode so i dont know what to make of it.

[Christian-Sesta]

A less fiddly alternative to this issue could be some variant of this, that has the same result.

arg := map[string]interface{}{
    "published": true,
    "authors":  pq.Int64Array([]int64{8, 19, 32, 44}),
}

query := "SELECT * FROM articles WHERE published= :published AND WHERE author_id = ANY(:authors)"
sqlResult, err := sxlx.NamedExec(query, args)