Struggling to write a SELECT query with Knex in Node js that does a calculation

Question

I am using PostgresQL and I am able to run the following statement:

SELECT id, code, supplier, item, price, price * stockonhand AS stockvalue, stockonhand - stockprocessed AS stockleft FROM products;

This works in PostgresQL and returns all the columns that I selected and the new calculated columns "stockvalue" and "stockleft".

  id  | code  | supplier |  item   | price | stockvalue | stockleft 
------+-------+----------+---------+-------+------------+-----------
 1002 | 15202 | EVADAM   | CHZIP-X | 48.24 | 39074.4000 |    670.00
 1001 | 15201 | EVADAM   | ZIP-X   | 42.38 | 50856.0000 |   1050.00
    2 | 15204 | EVADAM   | LCC-X   | 33.45 | 40140.0000 |   1200.00
    4 | 15203 | EVADAM   | LCC-X   | 33.45 | 40140.0000 |   1200.00
    5 | 15205 | EVADAM   | LOC-X   | 36.45 | 36450.0000 |   1000.00

Now my question is how do I convert the following code so that it includes the above calculations using Knex?

const handleGetProducts = (req, res, db) => {

    db.select('id', 'supplier', 'code', 'item', 'description', 'price', 'stockonhand', 'stockprocessed').table('products')
        .then(products => {
            if (products.length) {
                res.json(products)
                console.log(products)
            } else {
                res.status(400).json('not found')
            }
        })
        .catch(err => res.status(400).json('error getting products'))
}

module.exports = {
    handleProducts: handleGetProducts
};

I have searched everywhere but I can't find anything that helps my situation.

Thanks

Answer 1

One way is to use raw(). I believe this more or less matches the query you're trying to get:

knex.select('id', 'supplier', 'code', 'item', 'price', knex.raw('"price" * "stockonhand" as "stockvalue"'), knex.raw('"stockonhand" - "stockprocessed" as "stockleft"')).table('products')

Here's the result:

select "id", "supplier", "code", "item", "price", "price" * "stockonhand" as "stockvalue", "stockonhand" - "stockprocessed" as "stockleft" from "products"

You can tinker with this exact expression for postgres here: QueryLab