MySQL conditional filters handling

user_6407088 0
Viewed 86

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 ?

express node.js javascript
5 Answers

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
    }
}
user_30845770 
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.

user_42813890

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.

user_54463790

The code you used above to build the query string is not possible to cover all possible cases.

Example: What if isFilter==false and one of the 3 variables orderId, receiptId and driver has the value !== undefined? => your query will be wrong because the WHERE statement is missing.

Another case, if one of the variables orderId or receiptId satisfied the condition and the variable driver is not valid. => Your query will be wrong because of the extra of 'OR' at the end.

Those are the 2 cases that are most likely to cause problems for the above code. Here is my suggestion to solve your problem:

queryString="select * from orders"

if(isFilter){
  queryString+=' where'
  if(orderId){
    queryString+=' ..... or'
  }
  if(receiptId){
    queryString+=' ..... or'
  }
  if(driver){
    queryString+=' .....'
  }
}
queryString=queryString.trim()
if(queryString.endsWith('or')){
    queryString=queryString.substring(0,queryString.length-2)
}
if(queryString.endsWith('where')){
    queryString=queryString.substring(0,queryString.length-5)
}
user_161491920 
    select * from orders ${isFilter ? 'where 1=0' : ''} 
        ${orderId !== undefined && orderId ? `OR order_id = '${orderId}'  ` : '' }
        ${receiptId !== undefined && receiptId? `OR receipt_id = '${receiptId}' `: '' }
        ${driver !== undefined && driver ? `OR driver_id = '${Number(driver)}'` : '' }

Instead if its AND you can do 1=1 instead of 1=0.

user_65635670
Related