data-grid.js

• Classes
  • DataGrid
    • constructor
    • buildBaseCriteria
    • buildSqlPagination
    • buildSqlCriteria
    • buildNoSqlPagination
    • buildNoSqlCriteria

helper/controller/data-grid.js
  
/**
 * DataGrid Helper
 *
 * @module greppy/helper/controller/data-grid
 * @author Hermann Mayer <hermann.mayer92@gmail.com>
 */
var sqlUtils = require('sequelize').Utils;
var extend   = require('extend');
/**
 * @constructor
 */
var DataGrid = function()
{
};
/**
 * Build the filter criteria for a given request.
 *
 * @param {Object} req - Request to analyse
 * @param {Object} res - Response to call on errors
 * @param {Object} options - Options to use
 * @return {Object}
 */
DataGrid.prototype.buildBaseCriteria = function(req, res, options)
{
    options.limit = options.limit || 25;
    options.properties = options.properties || ['created_at'];
    options.pageSizes = options.pageSizes || [10, 25, 50, 100];
    options.fuzzySearch = ('undefined' !== typeof options.fuzzySearch) ? options.fuzzySearch : true;
    options.softDeletion = ('undefined' !== typeof options.softDeletion) ? options.softDeletion : true;
    var page   = parseInt(req.query.page) || 1;
    var limit  = parseInt(req.query.limit) || options.limit;
    var offset = (1 < page) ? ((page * limit) - limit) : 0;
    var view   = 'index';
    var render = req.query.render || 'all';
    // Render the whole page or just the partials
    if ('rows' === render) {
        view = '_index_rows';
    }
    if ('pagination' === render) {
        view = '_pagination';
    }
    return {
        view      : view,
        limit     : limit,
        offset    : offset,
        page      : page,
        pageSizes : options.pageSizes
    };
};
/**
 * Build a SQL pagination object by a sql-criteria object.
 *
 * @param {Object} sqlCriteria - Criteria object to get data from
 * @param {Object} options - Options to use
 * @param {Function} callback - Function to call on finish
 * @return {Object}
 */
DataGrid.prototype.buildSqlPagination = function(sqlCriteria, options, callback)
{
    greppy.db.get(options.connection).getORM(function(orm, models) {
        var stripedSqlCriteria = {
            where: sqlCriteria.where
        };
        if (sqlCriteria.include) {
            stripedSqlCriteria.include = sqlCriteria.include;
        }
        models[options.entity].count(stripedSqlCriteria).success(function(count) {
            callback && callback(null, {
                limit     : sqlCriteria.limit,
                page      : sqlCriteria.page,
                count     : count || 0,
                pageSizes : sqlCriteria.pageSizes
            });
        }).error(function(err) {
            callback && callback(err);
        });
    });
};
/**
 * Build the SQL filter criteria for a given request.
 *
 * @param {Object} req - Request to analyse
 * @param {Object} res - Response to call on errors
 * @param {Object} options - Options to use
 * @return {Object}
 */
DataGrid.prototype.buildSqlCriteria = function(req, res, options)
{
    var sqlCriteria = this.buildBaseCriteria(req, res, options);
    var where       = undefined;
    var order       = undefined;
    var properties  = null;
    // Add includes to criteria object
    if (options.include) {
        sqlCriteria.include = options.include;
    }
    // Searching
    if (req.query.search && req.query.sprop) {
        properties = options.properties;
        if (options.fuzzySearch) {
            properties.unshift('fuzzy');
        }
        // We don't accept any other properties
        if (-1 === properties.indexOf(req.query.sprop)) {
            res.end();
            return false;
        }
        var prefix = (options.wherePrefix || '');
        var query = ' LIKE ' + sqlUtils.escape('%' + req.query.search + '%');
        if ('fuzzy' == req.query.sprop) {
            properties.shift();
            where = properties.join(query + ' OR ' + prefix) + query;
        } else {
            where = req.query.sprop + query;
        }
        where = '(' + prefix + where + ')';
    }
    // Soft-deletion filter
    if (options.softDeletion) {
        if ('undefined' === typeof where) {
            where = '';
        } else {
            where += ' AND ';
        }
        if ('trash' == req.query.filter) {
            where += '(' + (options.wherePrefix || '') + 'deleted_at IS NOT NULL)';
        } else {
            where += '(' + (options.wherePrefix || '') + 'deleted_at IS NULL)';
        }
    }
    // Ordering
    if (req.query.order && req.query.oprop) {
        properties = options.properties;
        var modes  = ['DESC', 'ASC'];
        // We don't accept any other properties
        if (-1 === properties.indexOf(req.query.oprop)) {
            res.end();
            return false;
        }
        // We don't accept any other modes
        if (-1 === modes.indexOf(req.query.order.toUpperCase())) {
            res.end();
            return false;
        }
        order = req.query.oprop + ' ' + req.query.order.toUpperCase();
    }
    if ('undefined' === typeof where) {
        sqlCriteria.where = (options.where ? options.where : undefined);
    } else {
        sqlCriteria.where = where + (options.where ? ' ' + options.where : '');
    }
    sqlCriteria.order = order;
    return sqlCriteria;
};
/**
 * Build a NoSQL pagination object by a NoSql-criteria object.
 *
 * @param {Object} noSqlCriteria - Criteria object to get data from
 * @param {Object} options - Options to use
 * @param {Function} callback - Function to call on finish
 * @return {Object}
 */
DataGrid.prototype.buildNoSqlPagination = function(noSqlCriteria, options, callback)
{
    greppy.db.get(options.connection).getORM(function(orm, models) {
        models[options.entity].count(noSqlCriteria.where, function(err, count) {
            if (err) {
                return callback && callback(err);
            }
            callback && callback(null, {
                limit     : noSqlCriteria.limit,
                page      : noSqlCriteria.page,
                count     : count || 0,
                pageSizes : noSqlCriteria.pageSizes
            });
        });
    });
};
/**
 * Build the NoSQL filter criteria for a given request.
 *
 * @param {Object} req - Request to analyse
 * @param {Object} res - Response to call on errors
 * @param {Object} options - Options to use
 * @return {Object}
 */
DataGrid.prototype.buildNoSqlCriteria = function(req, res, options)
{
    var noSqlCriteria = this.buildBaseCriteria(req, res, options);
    var where         = {};
    var order         = {};
    var properties    = null;
    // Searching
    if (req.query.search && req.query.sprop) {
        properties = options.properties;
        if (options.fuzzySearch) {
            properties.unshift('fuzzy');
        }
        // We don't accept any other properties
        if (-1 === properties.indexOf(req.query.sprop)) {
            res.end();
            return false;
        }
        var query = {
            "$regex": '.*' + req.query.search + '.*',
            "$options": 'i'
        };
        if ('fuzzy' == req.query.sprop) {
            properties.shift();
            var or = [];
            properties.forEach(function(property) {
                if (/^(created|updated|deleted)_at$/i.test(property)) {
                    return;
                }
                var expression = {};
                expression[property] = query;
                or.push(expression);
            });
            where.$or = or;
        } else {
            var expression = {};
            expression[req.query.sprop] = query;
            where = expression;
        }
    }
    // Soft-deletion filter
    if (options.softDeletion) {
        if ('trash' == req.query.filter) {
            where.deleted_at = {
                "$ne": null
            };
        } else {
            where.deleted_at = null;
        }
    }
    // Ordering
    if (req.query.order && req.query.oprop) {
        properties = options.properties;
        var modes  = ['DESC', 'ASC'];
        // We don't accept any other properties
        if (-1 === properties.indexOf(req.query.oprop)) {
            res.end();
            return false;
        }
        // We don't accept any other modes
        if (-1 === modes.indexOf(req.query.order.toUpperCase())) {
            res.end();
            return false;
        }
        order[req.query.oprop] = ('DESC' === req.query.order.toUpperCase()) ? -1 : 1;
    }
    // Allow extending the where condition
    extend(true, where, (options.where || {}));
    noSqlCriteria.where = where;
    noSqlCriteria.order = order;
    return noSqlCriteria;
};
module.exports = DataGrid;