Select a sum in Knex.js without using .raw

user_1443026 0
Viewed 8405

I am trying to rewrite some MySQL queries in Knex.js, and I feel like I'm running into .raw at every turn, which feels counter to the reason I want to use Knex in the first place.

Is it possible to write the following query without using .raw?

SELECT
   product,
   SUM(revenue)
FROM orders

Using raw, it works to write:

knex()
   .select(
      'product',
      knex.raw('SUM(revenue)')
   )
   .from('orders')

but the idea of using Knex was to avoid using MySQL query strings, so I'm hoping there's another way. Or does everyone just use .raw everywhere, and I'm misunderstanding something? Very possible, I'm new to this.

knex.js mysql
2 Answers

You can use the sum method.

sum — .sum(column|columns|raw) Retrieve the sum of the values of a given column or array of columns (note that some drivers do not support multiple columns). Also accepts raw expressions.

knex('users').sum('products')
Outputs:
select sum("products") from "users"

Probably be something like this:

knex()
   .select('product')
   .sum('revenue')
   .from('orders')

You should adjust to your specific case. You might need to use something like groupBy('product') to get total revenue per product.

You should really go over knex's documentation, it's pretty good and straight forward and you definitely should not be using raw all the time.

user_30085060

You can even specify the returning sum column name like this:

knex(tableName)
 .select('product')
 .sum({ total: 'revenue' })
 .groupBy('product');
user_80799720
Related