传递多个值到模板以执行查询

从现在开始，努力学习吧！本文《传递多个值到模板以执行查询》主要讲解了等等相关知识点，我会在golang学习网中持续更新相关的系列文章，欢迎大家关注并积极留言建议。下面就先一起来看一下本篇正文内容吧，希望能帮到你！

选项 我有带有搜索栏的网络应用程序。搜索栏接受 customer_id 并使用 customer_id 作为过滤器对 redshift 集群运行查询。我需要能够输入多个客户 id 的逗号分隔，然后运行按这些客户 id 过滤的查询。目前我一次只能使用 1 个客户 id。这是我目前使用的。感谢任何帮助或指导。

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



Search By: Rma_Id, Order Id, Customer ID, Asin, LPN OR Warehouse ID (May Pull Many Records)








{{range .}}
    
        {{end}}
  

  

    
         
        
Rma_id
        
Order_id
        
Customer_id
        
Bin_id
        
Owner
        
Asin
        
Lpn
        
Warehouse_id
    
 
 

        
{{.Rmaid}}
        
{{.Orderid}}
        
{{.Customerid}}
        
{{.Binid}}
        
{{.Owner}}
        
{{.Asin}}
        
{{.Lpn}}
        
{{.Warehouseid}}
    

 

解决方案

您至少有两个选择，构建一串占位符并在传递参数之前将它们插值到查询中，或者不使用 in，而是改用 any 与 lib/pq.Int64Array 一起使用。

构建您自己的占位符：

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
// ...

使用 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)
// ...

文中关于的知识介绍，希望对你的学习有所帮助！若是受益匪浅，那就动动鼠标收藏这篇《传递多个值到模板以执行查询》文章吧，也可关注golang学习网公众号了解相关技术文章。