javascript - MySQL条件过滤器处理

Question

我正在使用 node.js 并尝试基于多个过滤器进行查询，如果它们是真的

select * from orders ${isFilter ? 'where' : ''} 
    ${orderId !== undefined && orderId ? `order_id = '${orderId}' or ` : '' }
    ${receiptId !== undefined && receiptId? `receipt_id = '${receiptId}' or `: '' }
    ${driver !== undefined && driver ? `driver_id = '${Number(driver)}'` : '' }

这在没有过滤器或所有过滤器都为真但OR缺少一个或多个过滤器时会导致问题的情况下工作正常。处理这个问题的最佳方法是什么？

标签： javascriptnode.jsexpress

Answer 1

这是动态组合查询的伪代码

var clause = 'where';
var query = 'select * from orders';
if (isFilter) { // this statement could be removed
    if (orderId !== undefined && orderId) {
        query += clause + ' order_id = `${orderId}`';
        clause = 'or';
    }
    if (receiptId !== undefined && receiptId) {
        query += clause + ' receiptId = `${receiptId}`';
        clause = 'or';
    }
    if (driver !== undefined && driver) {
        query += clause + ' driver = `${driver}`';
        clause = 'or'; // this is not really needed, but it could be useful for further filters in future
    }
}