Bookshelf JS Relation - Getting Count

Question

I'm trying to get users count belongs to specific company.

Here is my model;

var Company = Bookshelf.Model.extend({
    tableName: 'companies',
    users: function () {
        return this.hasMany(User.Model, "company_id");
    },
    users_count : function(){
        return new User.Model().query(function(qb){
            qb.where("company_id",9);
            qb.count();
        }).fetch();
    },
    organization: function () {
        return this.belongsTo(Organization.Model, "organization_id");
    }
});

• method "users" works very well, no problem.
• method "users_count" query works well, but cant get value to "company" model.

in routes, i'm using bookshelf models like this;

new Company.Model({id:req.params.id})
            .fetch({withRelated:['users']})
            .then(function(model){
                res.send(model.toJSON())
            })
            .catch(function(error){
                res.send(error);
            });

How should i use users_count method, i'm kinda confused (probably because of promises)

Answer 1

Collection#count()

If you upgrade to 0.8.2 you can use the new Collection#count method.

Company.forge({id: req.params.id}).users().count().then(userCount =>
  res.send('company has ' + userCount + ' users!');
);

Problem with your example

The problem with your users_count method is that it tries to make Bookshelf turn the result of your query into Models.

users_count : function(){
  return new User.Model().query(function(qb){
      qb.where("company_id",9);
      qb.count();
  }).fetch(); // Fetch wanted an array of `user` records.
},

This should work in this instance.

users_count : function(){
  return new User.Model().query()
    .where("company_id",9)
    .count()
},

See relevant discussion here.

EDIT: How to get this in your attributes.

Maybe try something like this:

knex = bookshelf.knex;

var Company = bookshelf.Model.extend({
    tableName: 'companies',
    initialize: function() {
      this.on('fetching', function(model, attributes, options) {
        var userCountWrapped = knex.raw(this.getUsersCountQuery()).wrap('(', ') as user_count');
        options.query.select('*', userCountWrapped);
      }
    }
    users: function () {
        return this.hasMany(User.Model, "company_id");
    },
    getUsersCountQuery: function() {
      return User.Model.query()
        .where("company_id",9)
        .count();
    }
    organization: function () {
        return this.belongsTo(Organization.Model, "organization_id");
    }
});

Answer 2

Check out the bookshelf-eloquent extension. The withCount() function is probably what you are looking for. Your code would look something like this:

let company = await Company.where('id', req.params.id)
    .withCount('users').first();