Restrict Knex query on Bookshelf model to return only n records

Question

I have the following code where I am using the splice function to pass only the first 10 /JSON objects to the JADE template.

app.get('/index', function(req, res) {
    new models.Condos()
      .query('orderBy', 'age', 'asc')
      .fetch()
      .then(function(names) {
        var name = names.splice(0,10);
        res.render('index', {
          names: name.toJSON()
        });
      });
  });
};

Is there any way where i could restrict the query itself to return only the first 10 records instead of splicing the array to do that (use the offset and limit parameters) ?

Answer 1

You can write a knex query to achieve this it will look something like:

app.get('/index', function(req, res) {
  knex.select('*')
    .from('condos')
    .limit(10)
    .then(function(names) {
      res.render(names);
    });
});

You will also need to require knex in your router file.

Answer 2

What I was looking for was something more along these lines.

app.get('/index', function(req, res) {
    new models.Condos()
      .query('orderBy', 'age', 'asc')
      .query('limit','10')
      .fetch()
      .then(function(names) {
        var name = names.splice(0,10);
        res.render('index', {
          names: name.toJSON()
        });
      });
  });
};

Answer 3

You can use the Pagination plugin from Bookshelf.

models.Condos.fetchPage( {page:1, pageSize:10} )

Note: for the first page if the page size is 10, you can leave out the params and just

models.Condos.fetchPage()