I am using node.js and trying to query based on multiple filters if only they are true
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)}'` : '' }
this works fine where there is no filter or when all the filters are true but the OR causes an issue when one filter or more are missing. what would be the best way to handle this ?
This is a pseudo code to dynamically compose the query
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
}
}
let whereClause = [];
if(orderId) whereClause.push(`order_id = '${orderId}'`);
if(receiptId) whereClause.push(`receipt_id = '${receiptId}'`);
if(driver) whereClause.push(`driver_id = ${parseInt(driver)}`);
let whereQuery = whereClause.join(' OR ');
let sql = `select * from orders where true ${whereClause.length ? whereQuery : ''}`;
try using join. so that you can add many properties even not in order. Also if you're expecting it to be undefined, then you may remove that as it resulted false on condition statement.
I think it would be reasonable if you can log the query. we can be sure about the query that's being sent. This would be the first step to troubleshoot and may be we can use the way you are trying using ternary operator with few changes.