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

对于一个Golang开发者来说，牢固扎实的基础是十分重要的，编程网就来带大家一点点的掌握基础知识点。今天本篇文章带大家了解《如何使用 gocql 动态添加查询参数？》，主要介绍了，希望对大家的知识积累有所帮助，快点收藏起来吧，否则需要时就找不到了！

在一个函数（golang、gocql、cassandra）中基于过滤器输入（vendor_id、customer_id、order_status_id ...或同时所有非空字段）编写查询的最佳方法是什么？我不想在每种情况下都编写新函数，例如以下仅按 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
}

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

解决方案

这是我在三天内提出的问题的解决方案:)我认为这会对其他人有所帮助)

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
}

终于介绍完啦！小伙伴们，这篇关于《如何使用 gocql 动态添加查询参数？》的介绍应该让你收获多多了吧！欢迎大家收藏或分享给更多需要学习的朋友吧~编程网公众号也会发布Golang相关知识，快来关注吧！