CODEWITHSUNDEEP
mysqlnode.js
VipiN Negi
VipiN Negi

Reputation: 3108

MySql Form query for filters or Ignore WHERE clause for column value in NodeJS

I'm trying to fetch a user list where gender, region, and status are the mandatory filters in my app. Filtering works fine when gender is either 0 or 1. The problem is when I need both. Scenario is same for all 3 filters.

API: https://website.com/api/getUsers?gender=0&region=1&status=1 to get filter results.

APPROACH 1: I want to pass something for those api params for eg. gender=#$%, so that it ignores its WHERE clause when passing it on queryInputs below.

module.exports.getMatches = ({offset, gender, status, region}, callback) => {
var dbQuery = 'SELECT * from `users` WHERE `gender` = ? AND `status` = ? AND `region` = ? LIMIT ? OFFSET ?';
var queryInputs = [parseInt(gender), parseInt(status), parseInt(region), 10, parseInt(offset)];

db.query(dbQuery, queryInputs, function(err, result) {
    //code goes here
});
}

APPROACH 2: I'm passing empty params to validate to fetch all data for particular filters for eg. https://website.com/api/getUsers?gender=&region=1&status=

I'm trying to avoid WHERE clause if I pass empty params in api. This almost worked for me if it weren't for last AND before LIMIT in the query.

module.exports.getMatches = ({offset, gender, status, region}, callback) => {
var dbQuery = 'SELECT * from `users`';
if( gender != '' || status!= '' || region != '') dbQuery += ' WHERE'
if(gender != ''){
    dbQuery += ' `gender` = '+ db.escape(parseInt(gender))+' AND';
}
if(status != ''){
    dbQuery += ' `status` = '+ db.escape(parseInt(status))+' AND';
}
if(region != ''){
    dbQuery += ' `region` = '+ db.escape(parseInt(region))+' AND';
}
dbQuery += ' LIMIT 10 OFFSET ' + db.escape(parseInt(offset));

db.query(dbQuery, queryInputs, function(err, result) {
    //code goes here
});
}

Among these two Approches solution for anyone works for me.

Upvotes: 1

Views: 1699

Answers (2)

Captain
Captain

Reputation: 430

Here is an edit for your second approach. This should allow you to put "AND" before your query additions so you avoid your stated error.

module.exports.getMatches = ({offset, gender, status, region}, callback) => {
var dbQuery = 'SELECT * from `users` WHERE 1=1 ';
//if( gender != '' || status!= '' || region != '') dbQuery += ' WHERE'
if(gender != ''){
    dbQuery += 'AND `gender` = '+ db.escape(parseInt(gender));
}
if(status != ''){
    dbQuery += 'AND `status` = '+ db.escape(parseInt(status));
}
if(region != ''){
    dbQuery += 'AND `region` = '+ db.escape(parseInt(region));
}
dbQuery += ' LIMIT 10 OFFSET ' + db.escape(parseInt(offset));

db.query(dbQuery, queryInputs, function(err, result) {
    //code goes here
});
}

Upvotes: 3

Harsh Srivastava
Harsh Srivastava

Reputation: 390

as per best practices, if you want to fetch all the users, you may follow the approach given below:

1: Create a new API (/allUsers) with pagination obviously and create a separate SQL statement for this API, where you don't need to add a filter clause.

OR

2: Add a validation check in existing API, if there is no param then don't apply filters otherwise apply as usual.

Upvotes: 0

Related Questions