'NodeJS Mysql Query with multiple fields in where clause

I'm working with MySQL for a while and also built an API in NodeJS including mySQL for work

Basically i need a query like "SELECT * FROM table WHERE fieldA=varA AND/OR fieldB=valB"

I'm using "mysql.format(sql, args)" to format my query, so i'm using ? and ?? in my Queries.

I would like to write a basic query, that i could use and feed all the needed fields and values

I tried the following ways

-> "SELECT * FROM table WHERE ?" with "{fieldA: varA, fieldB: varB}" as replacement for ?

that leads to "SELECT * FROM table WHERE fieldA='varA', fieldB='varB'"

-> "SELECT * FROM table WHERE ?? = ?" with "['fieldA', 'fieldB']" and "['varA', 'varB']" as replacements

what leads to "SELECT * FROM table WHERE fieldA, fieldB = 'varA', 'varB'"

For now i "only" need 2 different fields, so i could add fixed "fieldA=? AND/OR fieldB=?" and fill only the values. But i would like a dynamic way and give all the fields i could need in it and also if i use AND or OR in combining.

I didn't find anything like this in the documentation, maybe somebody here had stumbled upon before. Or might it be the only solution to dynamically add some "AND/OR ?? = ?" to the query and fill the arguments array with fieldName, values one after the other?

mysqlnode.js


Solution 1:[1]

Consider theses are the fields that you need to build your dynamic query string.

var fields = [{fieldName:"A",value:"one",operator:"AND"},{fieldName:"B",value:"two",operator:""}];

Now try building your query string with that array. For now I have considered only two fields but you can add as per your needs.

var fields = [{fieldName:"A",value:"one",operator:"AND"},{fieldName:"B",value:"two",operator:""}];
var query = "SELECT * FROM table WHERE ";

fields.forEach((f)=>{
    query=query+`${f.fieldName} = ${f.value} `
  if(f.operator!="") query=query+f.operator+" "
})
console.log(query)

Solution 2:[2]

I have built a method for this now, with hints from Ameers answer. It will generate a query like

SELECT * FROM ?? WHERE ??=? AND/OR ??=? AND/OR ??=?

and then will add the fieldName and values to the arguments array for mysql.format

var fieldValues = [
  { field: 'command', value: command.command, operator: 'OR' },
  { field: 'alias', value: command.command, operator: 'OR' }
];

exists(fieldValues) {
  let query = 'SELECT * FROM ??';
  let args = ['tablename'];
  for (let i = 0; i < fieldValues.length; i++) {
    let operator = i == 0 ? 'WHERE' : fieldValues[i].operator;
    query += ` ${operator} ?? = ?`;
    args.push(fieldValues[i].field);
    args.push(fieldValues[i].value);
  }

  return mysql.query(query, args);
}

mysql.query() here is my wrapper around mysql.connection.query and using promises

Maybe there is a better builtin method, but i didn't find one yet and this way i can still use the builtin mysql-formatter function.

Solution 3:[3]

Consider, that you have fields in DB like first_name, last_name, personal_email, work_email and office_name. and you want to get all result matching with search fields. i.e. let search = req.query.search. It will become easy with ORM like Objection.js So the query will be like this using objection.js

if (search) {
    table_name.where('column_name.last_name', 'like', `%${search}%`) 
        .orWhere('column_name.first_name', 'like', `%${search}%`) 
        .orWhere('column_name.personal_email', 'like', `%${search}%`) 
        .orWhere('column_name.work_email', 'like', `%${search}%`) 
        .orWhere('column_name.office_name', 'like', `%${search}%`)
}

Note: This code is in nodejs and you can also use table_name you can also add model_name.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ameerudheen.K
Solution 2 MRistau
Solution 3 Tyler2P

Related Questions