一种方法是使用raw((。我相信这或多或少符合你想要得到的查询:
我正在使用PostgresQL,并且我能够运行以下语句:
SELECT id, code, supplier, item, price, price * stockonhand AS stockvalue, stockonhand - stockprocessed AS stockleft FROM products;
这在PostgresQL中工作,并返回我选择的所有列和新计算的列";股票价值;以及";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
现在我的问题是,如何转换以下代码,使其包含使用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
};
我到处找了,但找不到任何有助于我的情况的东西。
感谢
knex.select('id', 'supplier', 'code', 'item', 'price', knex.raw('"price" * "stockonhand" as "stockvalue"'), knex.raw('"stockonhand" - "stockprocessed" as "stockleft"')).table('products')
结果如下:
select "id", "supplier", "code", "item", "price", "price" * "stockonhand" as "stockvalue", "stockonhand" - "stockprocessed" as "stockleft" from "products"
你可以在这里修改postgres的确切表达式:QueryLab