bteixeira/coppers2

coppers2's Issues

db hint

about code:

db.one(`
        INSERT INTO Spendings (
            id_user,
            amount,
            date,
            description
        ) VALUES (
            1,
            $1,
            $2,
            $3
        ) RETURNING ID;
    `, [amount, date, description]).then(function (data) {
    db.none(
        tags.map(function (tag) {
            return `
                    INSERT INTO Spendings_Tags (
                        id_spending,
                        tag)
                    VALUES (
                        ${data.id},
                        '${tag}'
                    );`;
        }).join('')
        , []).then(function () {
        res.send({id: data.id});
    });
});

The right way to concatenate inserts is not one insert after another, but use one insert with sets of values concatenated, like this:

db.one(`
        INSERT INTO Spendings (
            id_user,
            amount,
            date,
            description
        ) VALUES (
            1,
            $1,
            $2,
            $3
        ) RETURNING ID;
    `, [amount, date, description]).then(function (data) {
    db.none('INSERT INTO Spendings_Tags (id_spending, tag) VALUES' +
            tags.map(function (tag) {
                return `(
                        ${data.id},
                        '${tag}'
                    )`;
            }).join(','))
        .then(function () {
            res.send({id: data.id});
        });
});

See this post for a comparative example: Performance Boost.

proper filter generation

instead of this:

    var q = 'SELECT * FROM Spendings WHERE id_user = $1 ';
    var params = [req.session.uid];
    for (var p in req.query) {
        if (req.query.hasOwnProperty(p)) {
            params.push(req.query[p]);
            q += 'AND ';
            if (p === 'amount-min') {
                q += 'amount >= $' + params.length + ' ';
            } else if (p === 'amount-max') {
                q += 'amount <= $' + params.length + ' ';
            } else if (p === 'date-min') {
                q += 'date >= $' + params.length + ' ';
                params[params.length - 1] = new Date(params[params.length - 1]);
            } else if (p === 'date-max') {
                q += 'date <= $' + params.length + ' ';
                params[params.length - 1] = new Date(params[params.length - 1]);
            }
        }
    }

consider using this approach:

var params = {
    uid: req.session.uid
}, conditions = ['id_user = ${uid}'];
for (var p in req.query) {
    if (req.query.hasOwnProperty(p)) {
        switch (p) {
            case 'amount-min':
                params.minAmount = req.query[p];
                conditions.push('amount >= ${minAmount}');
                break;
            case 'amount-max':
                params.maxAmount = req.query[p];
                conditions.push('amount <= ${maxAmount}');
                break;
            case 'date-min':
                params.minDate = new Date(req.query[p]);
                conditions.push('date >= ${minDate}');
                break;
            case 'date-max':
                params.maxDate = new Date(req.query[p]);
                conditions.push('date <= ${maxDate}');
                break;
            default:
                break;
        }
    }
}
var q = 'SELECT * FROM Spendings WHERE ' + conditions.join(' AND ');