bookshelf.js count method

问题:

I was searching high and low to find how to do basic counting (like SELECT COUNT(something) FROM table) with Bookshelf.js, but to no avail. Is there anything I'm missing? Or is it just used with a manual select query?

Thanks!

回答1:

For now it's a manual query... e.g:

bookshelf.knex(tableName).count('columnName').then(...

Long story as to why that's the case, but it's mainly because of a few complexities around counts in relations, I don't want to hack a half working one on there just for the sake of having it there for the time being... the ultimate solution is going to be to have:

model.query().count(column).then(...

But currently that mutates the knex query builder instance, so doesn't quite work. I've been working on a big refactor of knex and I'm looking to get this implemented in the near future.

回答2:

Here's the format that I'm currently using to do the following from a model:

var Sample = bookshelf.Model.extend({
  tableName: 'example',

  count: function (cb) {
    bookshelf.knex('example')
      .count('id')
      .then(function (count) {
        cb(null, count)
      })
      .catch(function (err) {
        cb(err)
      })
  }
})

Now, to count this table, simply call

new Sample().count(function(err, result) {
    console.log(result)
});

回答3:

Here is a really simple solution just use Bookshelf.knex.raw('count(id) as count')

ReactionsDb
    .query(function(qb) {
      qb.groupBy('reaction_type');
    })
    .fetchAll({columns: ['reaction_type', Bookshelf.knex.raw('count(id) as count')]});

回答4:

Since version 0.8.2 you can just use the Collection#count() method:

User.collection().count().then(function(count) {
  // count === 15
})

This can be used on any collection, like a model's relation:

User.forge({id: 1}).related('comments').count().then(function(count) {
  // count === 16
})

It can also be used on Model classes as a static method:

User.count().then(function(count) {
  // count === 15
})

It also accepts a column name to filter the results of the count by excluding rows whose value is NULL and will take into consideration any other defined query constraints:

User.count('is_active').then(function(count) {
  // count === 8
})

User.where('status', 'single').count().then(function(count) {
  // count === 4
})

回答5:

User.collection().query(function (qb) {
  qb.join('courses', 'users.id', 'courses.user_id');
  qb.groupBy('users.id');
  qb.select("users.*");
  qb.count('* as course_count');
  qb.orderBy("course_count", "desc");
})

回答6:

//user.js
var db = require('./db');
var User = db.Model.extend({  
  tableName: 'users',
  hasTimestamps: true,
});
module.exports = User; 

//main.js
var User = require('./user');
User.where('pet', 'dog').count('*').then(function(count) {
  console.log("Total Count : " , count);
});

In the above code snippet User is a model having the attributes like pet and name.

The generated query will be as follows: select count(*) from user where pet='dog';