Skip to main content
Code Review

Return to Question

Post Merged (destination) from codereview.stackexchange.com/questions/56421/…
edited tags; edited tags
Link
200_success
200_success
  • 144.2k
  • 22
  • 188
  • 473
deleted 32 characters in body
Source Link
Jamal
Jamal
  • 34.9k
  • 13
  • 133
  • 237

I have a node.js/express.js based REST application. In one GET service I am querying data based on different set of request parameters. I am looking for a better way to implement it. Following is my sample code

function handleGet(connection,req,res) {

var sql = 'SELECT id, name, age, rent, marks FROM student WHERE class = 10';

if (req.param('minAge') && req.param('maxAge')) {
    sql = sql + ' AND age BETWEEN ' + connection.escape(req.param('minAge')) + ' AND ' + connection.escape(req.param('maxAge'));
}

if (req.param('minRent') && req.param('maxRent')) {
    sql =sql + ' AND rent BETWEEN ' + connection.escape(req.param('minRent')) 
        + ' AND ' + connection.escape(req.param('maxRent'));
};

if (req.param('marks')) {
    sql = sql + ' AND marks >= ' + connection.escape(req.param('marks'));
};

};



var limit = ('undefined' === typeof req.params.limit) ? 20: req.params.limit;

connection.query(sql + ' ORDER BY id DESC LIMIT ' + limit, function handleSql(err, rows){
        if (err){ logAndRespond(err,res); return; }
        if (rows.length === 0){ res.send(204); return; }
        res.json(200, {'students':rows});
        connection.release();
});
}

Data can be queried by passing difference combination request parameters. Can anyone please suggest me any better  (reusable, abstract, robust) way of doing it? I was wondering if I can create a module for building query. Something like setting all param values in the module and let the module validate and construct the query.

I have a node.js/express.js based REST application. In one GET service I am querying data based on different set of request parameters. I am looking for a better way to implement it. Following is my sample code

function handleGet(connection,req,res) {

var sql = 'SELECT id, name, age, rent, marks FROM student WHERE class = 10';

if (req.param('minAge') && req.param('maxAge')) {
    sql = sql + ' AND age BETWEEN ' + connection.escape(req.param('minAge')) + ' AND ' + connection.escape(req.param('maxAge'));
}

if (req.param('minRent') && req.param('maxRent')) {
    sql =sql + ' AND rent BETWEEN ' + connection.escape(req.param('minRent')) 
        + ' AND ' + connection.escape(req.param('maxRent'));
};

if (req.param('marks')) {
    sql = sql + ' AND marks >= ' + connection.escape(req.param('marks'));
};

};



var limit = ('undefined' === typeof req.params.limit) ? 20: req.params.limit;

connection.query(sql + ' ORDER BY id DESC LIMIT ' + limit, function handleSql(err, rows){
        if (err){ logAndRespond(err,res); return; }
        if (rows.length === 0){ res.send(204); return; }
        res.json(200, {'students':rows});
        connection.release();
});
}

Data can be queried by passing difference combination request parameters. Can anyone please suggest me any better(reusable, abstract, robust) way of doing it? I was wondering if I can create a module for building query. Something like setting all param values in the module and let the module validate and construct the query.

I have a node.js/express.js based REST application. In one GET service I am querying data based on different set of request parameters. I am looking for a better way to implement it.

function handleGet(connection,req,res) {

var sql = 'SELECT id, name, age, rent, marks FROM student WHERE class = 10';

if (req.param('minAge') && req.param('maxAge')) {
    sql = sql + ' AND age BETWEEN ' + connection.escape(req.param('minAge')) + ' AND ' + connection.escape(req.param('maxAge'));
}

if (req.param('minRent') && req.param('maxRent')) {
    sql =sql + ' AND rent BETWEEN ' + connection.escape(req.param('minRent')) 
        + ' AND ' + connection.escape(req.param('maxRent'));
};

if (req.param('marks')) {
    sql = sql + ' AND marks >= ' + connection.escape(req.param('marks'));
};

};



var limit = ('undefined' === typeof req.params.limit) ? 20: req.params.limit;

connection.query(sql + ' ORDER BY id DESC LIMIT ' + limit, function handleSql(err, rows){
        if (err){ logAndRespond(err,res); return; }
        if (rows.length === 0){ res.send(204); return; }
        res.json(200, {'students':rows});
        connection.release();
});
}

Data can be queried by passing difference combination request parameters. Can anyone please suggest any better  (reusable, abstract, robust) way of doing it? I was wondering if I can create a module for building query. Something like setting all param values in the module and let the module validate and construct the query.

Post Migrated Here from programmers.stackexchange.com (revisions)
Source Link
amique
amique
  • 141
  • 1
  • 2

Building SQL from multiple combinations of query parameters

I have a node.js/express.js based REST application. In one GET service I am querying data based on different set of request parameters. I am looking for a better way to implement it. Following is my sample code

function handleGet(connection,req,res) {

var sql = 'SELECT id, name, age, rent, marks FROM student WHERE class = 10';

if (req.param('minAge') && req.param('maxAge')) {
    sql = sql + ' AND age BETWEEN ' + connection.escape(req.param('minAge')) + ' AND ' + connection.escape(req.param('maxAge'));
}

if (req.param('minRent') && req.param('maxRent')) {
    sql =sql + ' AND rent BETWEEN ' + connection.escape(req.param('minRent')) 
        + ' AND ' + connection.escape(req.param('maxRent'));
};

if (req.param('marks')) {
    sql = sql + ' AND marks >= ' + connection.escape(req.param('marks'));
};

};



var limit = ('undefined' === typeof req.params.limit) ? 20: req.params.limit;

connection.query(sql + ' ORDER BY id DESC LIMIT ' + limit, function handleSql(err, rows){
        if (err){ logAndRespond(err,res); return; }
        if (rows.length === 0){ res.send(204); return; }
        res.json(200, {'students':rows});
        connection.release();
});
}

Data can be queried by passing difference combination request parameters. Can anyone please suggest me any better(reusable, abstract, robust) way of doing it? I was wondering if I can create a module for building query. Something like setting all param values in the module and let the module validate and construct the query.