Search code examples
mysqlexpressknex.jsbookshelf.js

Knex.js Select Average and Round

I am switching an application from PHP/MYSQL to Express and am using knex to connect to the MYSQL database. In one of my queries I use a statement like such (I have shortened it for brevity.) 

SELECT ROUND(AVG(Q1),2) AS Q1 FROM reviews WHERE id=? AND active='1'

I am able to use ROUND if I use knex.raw but I am wondering if there is a way to write this using query builder. Using query builder makes dealing with the output on the view side so much easier than trying to navigate the objects returned from the raw query.

Here is what I have so far in knex.

let id = req.params.id;

knex('reviews')
//Can you wrap a ROUND around the average?  Or do a Round at all? 
.avg('Q1 as Q1')
.where('id', '=', id)

Thanks so much!

Solution

  • You can use raw inside select. In this case:

    knex('reviews')
  .select(knex.raw('ROUND(AVG(Q1),2) AS Q1'))

    Check the docs here for more examples and good practices when dealing with raw statements.