Get count result with knex.js / bookshelf.js

Question

I'm trying to perform a simple count with knex (since it seems to not be supported by bookshelf yet). The following code is working:

bookshelf.knex('hosts').count('id').then(function(total) {
  res.send({
    meta: {
      total: total[0]['count(`id`)']
    }
  });
});

It just seems odd to me that I have to do total[0]['count('id')'] to get the actual result. Am I doing things right here?

Thanks!

Answer 1

All the results from knex.js are arrays. A query could be successful and simply return 0 results.

Also, you can alias the column directly in the column name (or count() call). Like this:

  bookshelf.knex('hosts').count('id as CNT').then(function(total) {
    res.send({
      meta: {
        total: total[0].CNT
      }
    });
  });

Still need to get the first element, but you can reference the column as a normal JSON property.

Answer 2

While knex does return results as arrays, it also has a method for returning the first result, which will be an object--not an array. It's pretty simple to get straight to the count without having to rely on [0] or anything to access your count within an array. For your example, a cleaner solution could be:

bookshelf
  .knex("hosts")
  .count("id")
  .first()
  .then(function(total) {
    res.send({
      meta: {
        total: total.count
      }
    });
  });

Answer 3

code for node js

let result = await knex.count("id").from('events').first();
if (result) {
console.log(result.count);
}