如何使用gocql动态添加查询参数？

enyaitl3 于 2021-06-09 发布在 Cassandra

关注(0)|答案(1)|浏览(483)

基于过滤器输入（供应商标识、客户标识、订单状态标识）编写查询的最佳方式是什么。。。或所有的非空字段（同时）在一个函数中（golang、gocql、cassandra）？我不想在任何情况下都编写新函数，比如下面的函数，它只按customerid过滤：

func (repo *orderRepo) FindByCustomerId(req *pb.FindRequest) (*pb.OrdersResponse, error) {
    offset := req.GetOffset()
    limit := req.GetLimit()
    customerID := req.GetCustomerId()
    //vendorID := req.GetVendorId()
    //orderStatusID := req.GetOrderStatusId()
    var (
        orders []*pb.Order
        count  int32
    )
    if err := repo.session.Query(`SELECT count(1) FROM orders WHERE customer_id = ?`, customerID).
        Scan(&count); err != nil {
        return nil, err
    }
    if offset < count {
        query := repo.session.Query(`SELECT
            id,
            customer_id,
            customer_name,
            vendor_id,
            toJSON(items),
            paid_at,
            created_at,
            order_status_id
        FROM orders WHERE customer_id = ?`, customerID)
        defer query.Release()
        var ps []byte
        iter := query.PageState(ps).PageSize(int(offset % limit)).Iter()
        ps = iter.PageState()
        page := offset / limit
        var i int32
        for i = 0; i < page; i++ {
            iter = query.PageState(ps).PageSize(int(limit)).Iter()
            ps = iter.PageState()
        }
        iter = query.PageState(ps).PageSize(int(limit)).Iter()
        rows := iter.Scanner()
        for rows.Next() {
            var (
                order                pb.Order
                createdAt            time.Time
                paidAt               time.Time
                itemsJSON            []byte
            )
            if err := rows.Scan(
                &order.Id,
                &order.CustomerId,
                &order.CustomerName,
                &order.VendorId,
                &itemsJSON,
                &paidAt,
                &createdAt,
                &order.OrderStatusId,
            ); err != nil {
                return nil, err
            }
            if err := json.Unmarshal(itemsJSON, &order.Items); err != nil {
                return nil, err
            }
            order.PaidAt = paidAt.String()
            order.CreatedAt = createdAt.String()
            orders = append(orders, &order)
        }
        if err := iter.Close(); err != nil {
            return nil, err
        }
    }
    return &pb.OrdersResponse{
        Orders: orders,
        Count:  count,
    }, nil
}

我需要向上面的查询动态添加参数

cassandra go gocql

来源：https://stackoverflow.com/questions/63742243/how-can-i-add-query-parameters-dynamically-using-gocql

1条答案

按热度按时间

kmynzznz1#

以下是我对自己在3天内提出的问题的解答：）我认为这会帮助其他人）

func (repo *orderRepo) Find(req *pb.FindRequest) (*pb.OrdersResponse, error) {
    offset := req.GetOffset()
    limit := req.GetLimit()
    vendorID := req.GetVendorId()
    customerID := req.GetCustomerId()
    orderStatusID := req.GetOrderStatusId()
    var (
        orders []*pb.Order
        count  int32
    )
    var filter string
    var values []interface{}
    if len(vendorID) > 0 {
        if len(filter) > 0 {
            filter = filter + " AND vendor_id = ?"
        } else {
            filter = " WHERE vendor_id = ?"
        }
        values = append(values, vendorID)
    }
    if len(customerID) > 0 {
        if len(filter) > 0 {
            filter = filter + " AND customer_id = ?"
        } else {
            filter = " WHERE customer_id = ?"
        }
        values = append(values, customerID)
    }
    if len(orderStatusID) > 0 {
        if len(filter) > 0 {
            filter = filter + " AND order_status_id = ?"
        } else {
            filter = " WHERE order_status_id = ?"
        }
        values = append(values, orderStatusID)
    }
    fmt.Println(filter)
    if err := repo.session.Query(`SELECT count(1) FROM orders`+filter, values...).
        Scan(&count); err != nil {
        return nil, err
    }
    if offset < count {
        query := repo.session.Query(`SELECT
        id,
        customer_id,
        customer_name,
        vendor_id,
        toJSON(items),
        paid_at,
        created_at,
        order_status_id
        FROM orders`+filter, values...)
        defer query.Release()
        var ps []byte
        iter := query.PageState(ps).PageSize(int(offset % limit)).Iter()
        ps = iter.PageState()
        page := offset / limit
        var i int32
        for i = 0; i < page; i++ {
            iter = query.PageState(ps).PageSize(int(limit)).Iter()
            ps = iter.PageState()
        }
        iter = query.PageState(ps).PageSize(int(limit)).Iter()
        rows := iter.Scanner()
        for rows.Next() {
            var (
                order                pb.Order
                createdAt            time.Time
                paidAt               time.Time
                itemsJSON            []byte
            )
            if err := rows.Scan(
                &order.Id,
                &order.CustomerId,
                &order.CustomerName,
                &order.VendorId,
                &itemsJSON,
                &paidAt,
                &createdAt,
                &order.OrderStatusId,
            ); err != nil {
                return nil, err
            }
            if err := json.Unmarshal(itemsJSON, &order.Items); err != nil {
                return nil, err
            }
            order.PaidAt = paidAt.String()
            order.CreatedAt = createdAt.String()
            orders = append(orders, &order)
        }
        if err := iter.Close(); err != nil {
            return nil, err
        }
    }
    return &pb.OrdersResponse{
        Orders: orders,
        Count:  count,
    }, nil
}

展开查看全部

赞(0）回复(0）举报 2021-06-09

我来回答

如何使用gocql动态添加查询参数？

1条答案

相关问题

热门标签

最新问答