首页 > Golang > Go问答

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

来源：stackoverflow

时间：2024-03-12 17:09:22 172浏览收藏

推广推荐

支持 PC / 移动端，安全直达

从现在开始，努力学习吧！本文《传递多个值到模板以执行查询》主要讲解了等等相关知识点，我会在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)
}

<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">
&lt;input type=&quot;text&quot; name=&quot;orderid&quot; id=&quot;ord&quot; placeholder=&quot;SEARCH&quot;&gt;
&lt;input type=&quot;submit&quot;&gt;
</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>

解决方案

您至少有两个选择，构建一串占位符并在传递参数之前将它们插值到查询中，或者不使用 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学习网公众号了解相关技术文章。

声明：本文转载于：stackoverflow 如有侵犯，请联系study_golang@163.com删除