Building a MySQL query from optional filters in Express JS

Question

Sorry if it's a dumb question but I'm new to express and MySQL and I couldn't find a solution anywhere.

I need to create a MySQL query based on some user input filters from an HTML form. The parameters are something like this:

let sDate = req.body.startDate;
let eDate = req.body.endDate;
let param1 = req.body.param1;
let param2 = req.body.param2;
let param3 = req.body.param3;
let param4 = req.body.param4;

A normal query that I will use if all params are not null will be

const query = `
SELECT * FROM table
WHERE
date BETWEEN ? AND ?
AND col1 = ?
AND col2 = ?
AND col3 = ?
AND col4 = ?;`

db.query(query, [startDate, endDate, param1, param2, param3, param4], (e, rows)=>{});

But every single parameter can be null, so there are a lot of possible filter combinations.

Is there a way to query the db with one single query? I could handle it in many if-else statements but it feels wrong.

Thanks for your help in advance!

With respect, you did not tell us enough about your question for us to help. Please edit it, or ask another.Your SQL will query a table: please show it to us. And please explain how your four param values express the criteria for searching. The interface between the procedural (javascript) realm and the declarative (SQL) realm requires a lot of precision. — O. Jones
– O. Jones, Commented Mar 22, 2021 at 0:11
@O.Jones sorry about that! I've edited with a query that I would use in a scenario that all parameters are provided. What I'm kinda looking for is if there's a way to create a query that does not take into consideration null values in a WHERE statement. — Alessandro Favero
– Alessandro Favero, Commented Mar 22, 2021 at 0:54

O. Jones · Accepted Answer · 2021-03-22 10:19:06Z

1

For your col1 = ? pattern you can use (? IS NULL OR ? = col1).

For your date matching you can do this.

   WHERE date <= IF( ? IS NOT NULL THEN ? ELSE '2999-12-31')
     AND date >= IF( ? IS NOT NULL THEN ? ELSE '1000-01-01')

But notice that parameters appear twice. So your query is

const sql = `
SELECT *
  FROM tbl
 WHERE date <= IF(? IS NOT NULL, ?, '2999-12-31')
   AND date >= IF(? IS NOT NULL, ?, '1000-01-01')
   AND (? IS NULL OR col1 = ?)
   AND (? IS NULL OR col2 = ?)
   AND (? IS NULL OR col3 = ?)
   AND (? IS NULL OR col4 = ?);`

And you'll invoke it like this.

const params = [startDate, startDate, 
                endDate, endDate,
                param1, param1, 
                param2, param3, 
                param2, param3, 
                param4, param4]

db.query(query, params, (e, rows)=>{});

But beware: this kind of query is a notorious edge case for the query-optimization software in the database server. It can't tell what you want well enough to use table statistics to choose the right index. Still, be sure to put an index on

(date, col1, col2, col3, col4)

for this query. You're likely to have a date range.

This may be slow, especially if that table is really large. You may be better off building up a custom query from strings each time you handle the request. The query optimizer understands stuff like this better than stuff with that ? IS NULL OR clause in there.

WHERE date <= ?
  AND date >= ?
  AND col2 = ?
  AND col3 = ?

edited Mar 22, 2021 at 10:19

answered Mar 22, 2021 at 1:35

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alessandro Favero Over a year ago

I've used IF(? IS NOT NULL, ?, '1000-01-01')

O. Jones Over a year ago

Ah, I see, thanks for pointing out my mistake. Edited.

mariko · Accepted Answer · 2023-01-05 23:49:32Z

0

You can use a LIKE clause with a combination of the COALESCE() function and the NULLIF() function to prevent a NULL value from being passed to the LIKE clause

const query = "SELECT * FROM accounts WHERE name LIKE COALESCE(NULLIF(?, ''), '%')";
const values = [name];

answered Jan 5, 2023 at 23:49

mariko

2265 silver badges12 bronze badges

Collectives™ on Stack Overflow

Building a MySQL query from optional filters in Express JS

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related