Options I have web app with a search bar. The search bar accepts customer_id's and runs a query to a redshift cluster with the customer_id as a filter. I need to be able to input multiple customer id's comma separated and then run the query filtered by those customer id's. Currently i can only use 1 customer id at a time. Here is what i currently use. Any help or guidance is appreciated.
func riiapp(w http.ResponseWriter, r *http.Request) {
oname := r.PostFormValue("orderid")
rows, err := db.Query("SELECT rma_id, order_id, customer_id, bin_id, owner, asin, lpn, warehouse_id FROM crt.returns_in_inv WHERE (warehouse_id IN ($1) OR lower(warehouse_id) IN ($1)) OR lpn IN ($1) OR asin IN ($1) OR rma_id IN ($1) OR customer_id IN ($1) OR ORDER_ID IN ($1);", oname)
if err != nil {
http.Error(w, http.StatusText(500), 500)
return
}
defer rows.Close()
bks := make([]Book, 0)
for rows.Next() {
bk := Book{}
err := rows.Scan(&bk.Rmaid, &bk.Orderid, &bk.Customerid, &bk.Binid, &bk.Owner, &bk.Asin, &bk.Lpn, &bk.Warehouseid) // order matters
if err != nil {
http.Error(w, http.StatusText(500), 500)
return
}
bks = append(bks, bk)
}
if err = rows.Err(); err != nil {
http.Error(w, http.StatusText(500), 500)
return
}
tpl.ExecuteTemplate(w, "riiapp.gohtml", bks)
fmt.Println(oname)
}
<html>
<div class="container" align="center">
<h6>Search By: Rma_Id, Order Id, Customer ID, Asin, LPN OR Warehouse ID (May Pull Many Records)</h6>
<form action="/riiapp" method="POST">
<input type="text" name="orderid" id="ord" placeholder="SEARCH">
<input type="submit">
</form>
<table id="testTable">
<thead valign="top">
<tr>
<th>Rma_id</th>
<th>Order_id</th>
<th>Customer_id</th>
<th>Bin_id</th>
<th>Owner</th>
<th>Asin</th>
<th>Lpn</th>
<th>Warehouse_id</th>
</tr>
</thead>
<tbody>{{range .}}
<tr>
<td>{{.Rmaid}}</td>
<td>{{.Orderid}}</td>
<td>{{.Customerid}}</td>
<td>{{.Binid}}</td>
<td>{{.Owner}}</td>
<td>{{.Asin}}</td>
<td>{{.Lpn}}</td>
<td>{{.Warehouseid}}</td>
</tr>
{{end}}
</tbody>
</table>
</div>
</html>
You have at least two options, building a string of placeholders and interpolating them into the query before passing the parameters, or instead of using IN you switch to using ANY together with lib/pq.Int64Array.
Building your own placeholders:
orderid := "44,33,55,66" // get input
ids := strings.Split(orderid, ",")
if len(ids) < 1 {
return // exit if no input
}
placeholders := ""
for i := range ids {
placeholders += ",$" + strconv.Itoa(i+1)
}
placeholders = placeholders[1:] // remove leading comma
query := `SELECT
rma_id,
order_id,
customer_id,
bin_id,
owner,
asin,
lpn,
warehouse_id
FROM crt.returns_in_inv
WHERE (warehouse_id IN (%[1]s) OR lower(warehouse_id) IN (%[1]s))
OR lpn IN (%[1]s)
OR asin IN (%[1]s)
OR rma_id IN (%[1]s)
OR customer_id IN (%[1]s)
OR ORDER_ID IN (%[1]s);`
query = fmt.Sprintf(query, placeholders) // inject placeholders $1,$2,$3,$4 into query template
rows, err := db.Query(query, ids...) // execute query
// ...
Using ANY:
orderid := "44,33,55,66" // get input
ids := strings.Split(orderid, ",")
if len(ids) < 1 {
return // exit if no input
}
params := make(pq.Int64Array, len(ids))
for i, s := range ids {
id, err := strconv.ParseInt(s, 10, 64)
if err != nil {
return // fail
}
params[i] = id
}
query := `SELECT
rma_id,
order_id,
customer_id,
bin_id,
owner,
asin,
lpn,
warehouse_id
FROM crt.returns_in_inv
WHERE (warehouse_id = ANY ($1) OR lower(warehouse_id) = ANY ($1))
OR lpn = ANY ($1)
OR asin = ANY ($1)
OR rma_id = ANY ($1)
OR customer_id = ANY ($1)
OR ORDER_ID IN = ANY ($1)`
rows, err := db.Query(query, params)
// ...
//This is the code that i got to work. Thank you so much @mkopriva !!!
//You are freaking awesome!! I did have to add in 1 thing to it.
func riiapp(w http.ResponseWriter, r *http.Request) {
oname := r.PostFormValue("orderid")
ids := strings.Split(oname, ",")
if len(ids) < 1 {
return // exit if no input
}
placeholders := ""
for i := range ids {
placeholders += ",$" + strconv.Itoa(i+1)
}
placeholders = placeholders[1:] // remove leading comma
t := ids
s := make([]interface{}, len(t))
for i, v := range t {
s[i] = v
}
query := `SELECT
rma_id,
order_id,
customer_id,
bin_id,
owner,
asin,
lpn,
warehouse_id
FROM z_support.returns_in_inv
WHERE (warehouse_id IN (%[1]s) OR lower(warehouse_id) IN (%[1]s))
OR lpn IN (%[1]s)
OR asin IN (%[1]s)
OR rma_id IN (%[1]s)
OR customer_id IN (%[1]s)
OR ORDER_ID IN (%[1]s);`
query = fmt.Sprintf(query, placeholders) // inject placeholders $1,$2,$3,$4 into
query template
rows, err := db.Query(query, s...) // execute query
if err != nil {
log.Println(err)
return
}
defer rows.Close()
bks := make([]Book, 0)
for rows.Next() {
bk := Book{}
err := rows.Scan(&bk.Rmaid, &bk.Orderid, &bk.Customerid, &bk.Binid, &bk.Owner,
&bk.Asin, &bk.Lpn, &bk.Warehouseid) // order matters
if err != nil {
http.Error(w, http.StatusText(500), 500)
return
}
bks = append(bks, bk)
}
if err = rows.Err(); err != nil {
http.Error(w, http.StatusText(500), 500)
return
}
tpl.ExecuteTemplate(w, "riiapp.gohtml", bks)
}