0

I'm aiming to get a list of related child records to returned for a set of parent records.

Using SQL the following works

SELECT id FROM children WHERE parent_id IN ( 
    19036,19037
)

Using golang I'm getting the following errors.

When using a comma separated string

import (
    "database/sql"
    "github.com/lib/pq"
)
// db is a valid XODB struct generated from https://github.com/xo/xo

var ids []string{"19036","19037"}

const sqlstr = `SELECT ` +
    `id ` +  
    `FROM children ` +
    `WHERE parent_id in ($1) `

q, err := db.Query(sqlstr, strings.Join(ids, ","))
if err != nil {
    return nil, err
}

results in pq: invalid input syntax for integer: "19036,19037"

When using a slice of ints

var ids []int{19036,19037}

const sqlstr = `SELECT ` +
    `id ` +  
    `FROM children ` +
    `WHERE parent_id in ($1) `

q, err := db.Query(sqlstr, ids)
if err != nil {
    return nil, err
}

results in sql: converting argument $1 type: unsupported type []int, a slice of int.

Creating a sql string using fmt.Sprintf works

var ids []string{"19036","19037"}

var sqlstr = `SELECT ` +
    `id ` +  
    `FROM children ` +
    `WHERE parent_id in ($1) `

sqlstr = fmt.Sprintf(sqlstr, strings.Join(ids, ","))
q, err := db.Query(sqlstr)
if err != nil {
    return nil, err
}

but I'm looking for a way to pass in either a slice of int or a comma delimited string into a database/sql 'in' query. How would you do that?

Keith John Hutchison
  • 4,955
  • 11
  • 46
  • 64
  • Have you looked at https://godoc.org/github.com/lib/pq#Int64Array? – mkopriva Sep 25 '18 at 09:53
  • 1
    ... my bad, the array types won't work with `IN` but you could use them with `ANY`, like so `WHERE parent_id = ANY ($1)`. (https://github.com/lib/pq/blob/90697d60dd844d5ef6ff15135d0203f65d2f53b8/array.go#L22) – mkopriva Sep 25 '18 at 09:56

0 Answers0