findAndCountAll and Incrementing certain values of an instance

[amitava82]

(from Sequelize) Really need findAndCountAll for paging. Get total records count with given query and get result with limit and offset. Any way to do it right now?

new Project.findAndCountAll({where: ["title LIKE 'foo%'"], offset: 10, limit: 
  2}).then(function(result) {
  console.log(result.count); //total records with given query
  console.log(result.rows); //result with offset and limit
});

Incrementing a column value without running into concurrency issues. Like:

new User.increment('versionNumber', 1).then(/* ... */) //Increment existing value by 1

[tgriesser]

Not really yet - but it should be easy to put a method like that together do yourself:

Projects = Bookshelf.Collection.extend({
  findAndCountAll: function(modifiers) {
     var knex = Bookshelf.Knex(_.result(this, 'tableName'));
     return when.all([
        this.query(modifiers).fetch(),
        knex.where.apply(knex, modifiers.where).count('*')
     ]).then(function(resp) {
        return  {
           rows: resp[0],
           count: resp[1][0].aggregate
        };
     });
  }
});

projects.findAndCountAll({where: ['title', 'like', 'foo%'], offset: 10, limit: 2}).then(...

pretty sure that or something like that should work, didn't test it or anything...

Also, you may want to look at increment.

user.query().increment('versionNumber').then(...

[amitava82]

findAndCountAll works perfect, thanks! I've added a second argument to be passed to fetch() to load relations.
increment works fine as a standalone query, but I'm looking for a way to do it with update statement; as in when I update a row with where clause without fetching the model first.

[tgriesser]

I'm not sure I follow, would this work?

var User = Bookshelf.Model.extend({
   increment: function(where, count) {
     count = count || 1;
     return this.query().where(where).increment('field', count)
   } 
});

then:

user.increment(['id', '>', 10]).then(...

[amitava82]

Sorry I was not very clear. What I meant was this:

UPDATE users
   SET logins = logins + 1, emailId='x@z.com', phone='1234567'
   WHERE id = 12

[tgriesser]

My general inclination is that this is something best accomplished with custom method:

incrementLogin: function() {
   var model = this;
   return this.query()
      .where(id, this.id)
      .update(_.extend(this.omit('logins'), {logins: Knex.Raw('logins + 1')})
      .then(function() {
         return model;
   });
} 

But I could consider adding something like this in the future... I just don't want to clutter the bookshelf api with too many things that could be accomplished fairly easily by dropping down to the Knex layer.

user.incrementLogin().then(...

[demisx]

@tgriesser Is Bookshelf.knex the current way to get a hold of the knex instance from the bookshelf instance? Bookshelf.Knex shown in the example above returns undefined.

[bendrucker]

Yes, lowercase