How to add simple Where-clause with bookshelf-pagemaker

Question

Using the bookshelf-pagemaker NodeJS Module:

• https://www.npmjs.com/package/bookshelf-pagemaker
• https://github.com/bhoriuchi/bookshelf-pagemaker

.. I was able to get basic paginate working where it selects all rows from a table. But I am not seeing how to add a WHERE clause to the query.

QUESTION:

Can someone please share a small example of using bookshelf-pagemaker with search criteria - e.g. SELECT * FROM user WHERE id > 10 ?

var Bookshelf = require('bookshelf').mysqlAuth;
var pagemaker = require('bookshelf-pagemaker')(Bookshelf);

var UserDAO = Bookshelf.Model.extend({
    tableName: 'user'
});

function getRegisteredUsers(pageNum, pageSize, offset, callback) {   

    var args = {
        params: {
            start: offset,
            page: pageNum,
            length: pageSize
        },
        model: UserDAO
    };

    var resultObj = pagemaker.datatables.paginate(args);
    resultObj.then(function (result) {
        console.log('Enter: paginate(args)');
        callback(result);
    });
}

Answer 1

I have added support for this in the freshly published version 0.1.3. You can now supply a where statement to the args object you pass to the paginate function. the sql statement you supply will be added with an and to the filter sql so you will still be able to use search functionality

your args object would look like

    var args = {
        params: {
            start: offset,
            page: pageNum,
            length: pageSize
        },
        model: UserDAO,
        where: '(id > 10 AND id < 100)'
    };

var resultObj = pagemaker.datatables.paginate(args);

resultObj.then(function (result) {
        paginateHandler(result);
    });
}

function paginateHandler (result) 
{
    var numPaginatedRecords = result.recordsFiltered;

    var pagesTotal = Math.ceil(numPaginatedRecords / ITEMS_ON_PAGE);

    res.render('my_paginated_view', {
        data: result.data,
        currentPage: <from URL or default 1>,
        pagesCount: pagesTotal
    });
};