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!

score 30 · Accepted Answer · answered Feb 26 '14 at 16:13

30

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.

answered Feb 26 '14 at 16:13

clay

5,917
2
23
21

1

Perfect, thanks for the clarification! The alias seems like a good way to make my code more readable. – Pedro Mar 02 '14 at 18:46
1

Thanks, why is knexjs so odd? Seems basic to return a direct number. Is that too much to ask? – King Friday Nov 09 '17 at 23:41
2

(Late reply? : ) Knex is built on SQL, and queries return rows of results. So in a SQL query with `count("col")` would return one row, typically with a column named "count" (which can be aliased). Since Knex is built around SQL, a query always returns an array (the rows) of objects, with properties to match the columns. A special case could be made for `.count()` or others to return a single value, but really they are just aggregators and you could have a few in the same query (or even `.groupBy()` different columns in which case you'll have as many rows as groups, each with their own count). – clay Oct 09 '18 at 16:14
@clay what if i want to count where column1 = 'some value', how to write that ? – sasha romanov Jul 22 '19 at 18:09
With anything "special" you use `knex.raw`, like `knex.raw('sum(case when "column1" = \'someValue\' then 1 else 0 end) as "total"')`. – clay Jul 22 '19 at 18:13
If using Knex already, may as well first use Knex's `where`, `on` and other conditional features; `knex.raw` really is for special cases but basic filtering is not a reason. – Kalnode Mar 01 '23 at 18:12

score 8 · Answer 2 · answered Apr 25 '19 at 00:01

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
      }
    });
  });

score 2 · Answer 3 · answered Mar 04 '21 at 05:47

2

code for node js

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

answered Mar 04 '21 at 05:47

manoj patel

1,150
12
10

I like this approach – Atiq Baqi Nov 16 '22 at 09:53

score 0 · Answer 4 · answered Apr 02 '20 at 11:13

this seems to work correctly and is a bit simpler

knex('Quotes').count('quoteBody')
    .then((res)=>{
        //console.log("rows "+JSON.stringify(res))        
        console.log("rowspl2 "+res[0]['count(`quoteBody`)'])
    })
    .catch((err)=>{
        console.log("err "+err)
    })

or try it like this

    knex('Quotes').count('quoteBody', {as: 'rows'})
        .then((res)=>{
           // console.log("rows "+JSON.stringify(res))
            console.log("rowsp "+res[0]['rows'])
        })
        .catch((err)=>{
            console.log("err "+err)
        })

Get count result with knex.js / bookshelf.js

4 Answers4