Node.js API with express & mysql - Apply search parameter only if set

Question

My current query looks like this:

router.get('/search?', function(req, res, next) {
    var title = req.query.title;
    var active = req.query.active;
    var sql = "SELECT * from users WHERE title = ? AND active = ?"; 
    connection.query(sql, [title, active], function (error, results, fields) {
        res.json({"status": 200, "error": null, "response": results});
    });
});

It works it I enter:

localhost:4001/api/v1/users/search?title=mr&active=1

But it doesn't work if one is not set:

localhost:4001/api/v1/users/search?title=mr

{"status":200,"error":null,"response":[]}

How can I make the query and return the desired string regardless if one or the other search parameter is set or not? So like:

localhost:4001/api/v1/users/search?title=mr
localhost:4001/api/v1/users/search?active=1 
localhost:4001/api/v1/users/search?title=mr&active=1
localhost:4001/api/v1/users/search?active=1&title=mr

Pierre Mallet · Accepted Answer · 2018-02-21 14:16:50Z

2

you can try to build your query dynamically according to the params set in the request:

router.get('/search?', function(req, res, next) {
    var sql = "SELECT * from users ";
    const existingParams = ["title", "active"].filter(field => req.query[field]);

    if (existingParams.length) {
        sql += " WHERE ";
        sql += existingParams.map(field => `${field} = ?`).join(" AND ");
    }

    connection.query(
        sql,
        existingParams.map(field => req.query[field]),
        function (error, results, fields) {
            res.json({"status": 200, "error": null, "response": results});
        }
    );
});

this way if you want to handle more params you can just add them the "existingParams" statement

edited Feb 21, 2018 at 14:16

answered Feb 21, 2018 at 9:03

Pierre Mallet

7,2412 gold badges22 silver badges31 bronze badges

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

4 Comments

Rahul Sharma Over a year ago

You have missed last condition var sql = "SELECT * from users"; const existingParams = ["title", "active"].filter(field => req.query[field]); if (existingParams.length !== 0) sql += ' WHERE ';

Philipp M Over a year ago

Unfortunately its not working properly ... I provided the results above.

Philipp M Over a year ago

Rahul is right. if (existingParams.length !== 0) sql += ' WHERE '; was missing ... but also existingParams should be in the .query() ... instead I need to defined them depending if available. The above is working. Not sure if that is the best way to do so though

Pierre Mallet Over a year ago

yeah sorry for the different mistakes, i edited the answer

Collectives™ on Stack Overflow

Node.js API with express & mysql - Apply search parameter only if set

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related