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

Question

我需要根据收到的参数使用where子句创建一个查询，但我不知道如何在where子句中包含一些参数。在下面的代码中，前四个条件(customerId、companyId、invoiceId 和 open)运行良好，但我不知道如何包含其他条件(close、openedFrom 和openedTo)。

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)