如何在 Knex.js 中动态创建 where 条件

Question

我需要根据收到的参数使用 where 子句创建一个查询，但我不知道如何在 where 子句中包含一些参数。在下面的代码中，四个第一个条件（客户ID，公司ID，发票ID和打开）运行良好，但我不知道如何包含其他条件（关闭，打开发件人和打开到）。

const getByParams = (req, res) => {
    const conditions = {}
    if (req.query.customerId) conditions['wo.customerId'] = req.query.customerId
    if (req.query.companyId) conditions['wo.companyId'] = req.query.companyId
    if (req.query.invoiceId) conditions['wo.invoiceId'] = req.query.invoiceId
    if (req.query.open) conditions['wo.finishedAt'] = null
    const onlyClosed = req.query.closed ? 'wo.finishedAt != null' : ''
    const openedFrom = req.query.openedFrom ? `wo.openingAt >= ${req.query.openedFrom}` : ''
    const openedTo = req.query.openedTo ? `wo.openingAt <= ${req.query.openedTo}` : ''
    app.db('workOrders as wo')
        .select('wo.*',
            'wos.serviceId',
            'wos.cost',
            'srv.name as serviceName')
        .leftJoin('workOrderServices as wos', 'wo.id', 'wos.workOrderId')
        .leftJoin('services as srv', 'wos.serviceId', 'srv.id')
        .whereNull('wo.canceledAt')
        .where(conditions)
        .then(wo => {
            const json = JSON.stringify(wo)
            const newJson = convertJson(json)
            res.status(200).send(newJson)
        })
        .catch(err => res.status(500).send(err))
}

Answer 1

指的是 https://knexjs.org/#Builder-where

试试这个：

.where((builder) => {
    if (req.query.customerId)
        builder.where('wo.customerId', req.query.customerId);
    if (req.query.companyId)
        builder.where('wo.companyId', req.query.companyId);
    if (req.query.invoiceId)
        builder.where('wo.invoiceId', req.query.invoiceId);
    if (req.query.open)
        builder.whereNull('wo.finishedAt');
    // so on..
});

而不是

.where(conditions)

Answer 2

如果所有过滤器都是 AND 'equals' 语句，另一种方法是

.where({
    ...(req.query.customerId && {'wo.customerId': req.query.customerId})
    ...(req.query.companyId && {'wo.companyId': req.query.companyId})
    ...(req.query.invoiceId && {'wo.invoiceId': req.query.invoiceId})
});