golang slice in mysql query with where in clause

user_2819754 0
Viewed 20769

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

go
6 Answers

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))
user_44692690

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

user_42594470

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...)
user_74763800
Related