golang slice in mysql query with where in clause

Question

I am running the below query, but only get the first id value:-

select * from `table` where table`.`id` in ('1', '2', '3', '4', '5', '6', '7', '9', '11', '13', '14', '15', '17') and `table`.`deleted_at` is null

I have done the following:-

var aID = make([]string, 0)
var in India // india is struct

for rows.Next() {
    cook := rows.Scan(&in.ID)

    aID = append(aID, strconv.Itoa(in.ID))
}

asID = strings.Join(aID, ",")

anotherRow,err := db.Query("SELECT * from table2 where id in (?)", asID)
if err != nil { fmt.Printf("Error: ", err) }
// ... Other line follows up with "for anotherRow.Next() and fetching"

While fetching data, it only returns value of "1" and ignores all other ID passed to it, which are '2', '3', '4', '5', '6', '7', '9', '11', '13', '14', '15', '17'.

How can I pass it correctly?

I am using go-sql-driver/mysql.

FAQ :

1. aID does contain all those numbers as string and

2. table has all the rows available with provided above id.

3. table is from where id is fetched and appended to aID and another record with id stored in aID are fetched with in statement from table2.

Thanks

Answer 1

You can do something like this:

args := make([]interface{}, len(asID))
for i, id := range asID {
    args[i] = id
}
stmt := `SELECT * from table2 where id in (?` + strings.Repeat(",?", len(args)-1) + `)`
anotherRow, err := db.Query(stmt, args...)

Just note you will want to put in a guard if asID can ever have len == 0.

If you have any other arguments to pass in, you'll have to add them to the args slice.

Also to note, you should explicitly name the columns you want so you can guarantee you are scanning in the correct columns to the correct fields.

Answer 2

Try

q,args,err := sqlx.In("SELECT * FROM table2 WHERE id IN(?);", asID) //creates the query string and arguments
rows, err := db.Query(q,args...)

You could also use the Masterminds/squirrel package:

import sq "github.com/Masterminds/squirrel"

...

users := sq.Select("*").From("table2")
active := users.Where(sq.Eq{"id":[]string{"1","2","3"}})
sql, args, err := active.ToSql()

Which will do the in clause automatically when using sq.Eq struct with a slice.

Answer 3

The most elegant solution for queries to make array/slice work directly with sql queries. This also sql injection proof as you are not using string concatenation rather using sql prepared statement

idAry := []string{"1", "2", "3"}
q := "SELECT * FROM table WHERE id = any($1);"
rows, err := db.Exec(q, pq.Array(authors))

Answer 4

maybe something like this.

func GetPlaceholders(values ...string) (placeholders string, parameters []interface{}) {
    n := len(values)
    p := make([]string, n)
    parameters = make([]interface{}, n)
    for i := 0; i < n; i++ {
        p[i] = "?"
        parameters[i] = values[i]
    }
    placeholders = strings.Join(p, ",")
    return placeholders, parameters
}

and calling the function like this

placeholders, params := GetPlaceholders("1", "2", "3")

rows, err := db.Query(`select language, textkey, text 
        from language where textkey in (`+placeholders+`)
        order by language, textkey`, params...)

Answer 5

Since you're dealing with ids from your own database and if you are certain there is no way someone could inject malicious "ids" into that code, don't use the placeholder ? and just use the fmt package.

fmt.Sprintf("SELECT * from table2 where id in (%s)", asID)

this will result in SELECT * from table2 where id in (1,2,3,4...) as opposed to SELECT * from table2 where id in ('1,2,3,4...')

Answer 6

example:

idAry := []string{"1", "2", "3"}
ids := strings.Join(idAry, "','")
sqlRaw := fmt.Sprintf(`SELECT * FROM table WHERE id IN ('%s')`, ids)
rows, err := db.Query(sqlRaw)

It works fine