dongzaotiao2863 2018-10-22 21:46
浏览 19

如何将多个值传递到模板中以运行查询

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>
  • 写回答

2条回答 默认 最新

  • dpppic5186 2018-10-23 06:33
    关注

    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)
// ...
    评论

报告相同问题？

悬赏问题

  • ¥15 如何获取烟草零售终端数据
  • ¥15 数学建模招标中位数问题
  • ¥15 phython路径名过长报错 不知道什么问题
  • ¥15 深度学习中模型转换该怎么实现
  • ¥15 HLs设计手写数字识别程序编译通不过
  • ¥15 Stata外部命令安装问题求帮助！
  • ¥15 从键盘随机输入A-H中的一串字符串，用七段数码管方法进行绘制。提交代码及运行截图。
  • ¥15 TYPCE母转母，插入认方向
  • ¥15 如何用python向钉钉机器人发送可以放大的图片？
  • ¥15 matlab（相关搜索：紧聚焦）