Server-side processing for JQuery Datatable plug-in
Node.js implementation of a server-side processor for the jQuery DataTables plug-in.
The node-datatable module provides backend SQL query generation and result parsing to support DataTables server-side processing for SQL databases. This module does not connect nor query a database, instead leaving this task to the calling application. SQL querying has been separated so that the caller can leverage his or her existing module choices for connection pools, database interfaces, and the like. This module has been used with node-mysql, sequelize, and strong-oracle.
An incomplete code example:
var async = require'async'QueryBuilder = require'datatable';var tableDefinition =sTableName: "Orgs";var queryBuilder = tableDefinition ;// requestQuery is normally provided by the DataTables ajax callvar requestQuery =iDisplayStart: 0iDisplayLength: 5;// Build an object of SQL query statementsvar queries = queryBuilderbuildQuery requestQuery ;// Connect with and query the database.var myDbObject = ;myDbObjectqueryqueriesuseif err res.errorerr;elseasyncparallelmyDbObjectqueryqueriesrecordsFiltered cb;myDbObjectqueryqueriesrecordsTotal cb;myDbObjectqueryqueriesselect cb;if err res.errorerr;elseresjsonqueryBuilderparseResponseresults;;;
The source code contains additional comments that will help you understand this module.
Construct a QueryBuilder object.
The node-datatable constructor takes an object parameter that has the following options. In the simplest case only the first two options will be necessary.
dbType - The database engine you work with (
oracle). This will affect the construction queries which include LIMIT. The default value for
sTableName - The name of the table in the database where a JOIN is not used. If JOIN is used then set
sCountColumnName For simple queries this is the name of the column on which to do a SQL COUNT(). Defaults to
For more complex queries, meaning when sSelectSql is set,
* will be used.
sDatabaseOrSchema - If set,
buildQuery will add a changeDatabaseOrSchema property to the object returned containing a
USE sDatabaseOrSchema statement for MySQL / Postgres or ALTER SESSION SET CURRENT_SCHEMA = sDatabaseOrSchema statement for Oracle.
aSearchColumns - In database queries where JOIN is used, you may wish to specify an alternate array of column names
that the search string will be applied against. Example:
aSearchColumns: "table3.username" "table1.timestamp" "table1.urlType" "table1.mimeType" "table1.url" "table2.description"
sSelectSql - If set then this defines the columns that should be selected, otherwise
* is used. This can be
used in combination with joins (see
sFromSql - If set then this is used as the FROM section for the SELECT statement. If not set then
is used. Use this for more complex queries, for example when using JOIN. Example when using a double JOIN:
"table1 LEFT JOIN table2 ON table1.errorId=table2.errorId LEFT JOIN table3 ON table1.sessionId=table3.sessionId"
sWhereAndSql - Use this to specify an arbitrary custom SQL that you wish to AND with the generated WHERE clauses.
sDateColumnName - If this property and one of
dateTo is set, a date range WHERE clause
will be added to the SQL query. This should be set to the name of the datetime column that is to be used in the clause.
dateFrom - If set then the query will filter for records greater then or equal to this date.
dateTo - If set then the query will filter for records less then or equal to this date.
The query builder object.
var queryBuilder =sTableName: 'user';
Builds an object containing between two and four SQL statements:
sDatabaseis set) A USE statement that specifies which database to use.
requestQuery.search.valueis set) A SELECT statement that counts the number of filtered entries. This is used to calculate the
Note that #2, #3 and #4 will include date filtering as well as any other filtering specified in
requestQuery: An object containing the properties set by the client-side DataTables library as defined in sent parameters.
The resultant object of query strings. The "use" query should be executed first, and the others can be executed in sequence, or (ideally) in parallel. Each database
response should be collected into an object property having a key name that matches the query object. The response object is later passed to the
var queries = queryBuilderbuildQuery oRequestQuery ;
Parses an object of responses that were received in response to each of the queries generated by the
queryResult: The object of query responses.
An object containing the properties defined in returned data.
var result = queryBuilderparseResponse queryResponseObject ;resjsonresult;
Extract a value from a database response. This is useful in situations where your database query returns a primitive value nested inside of an object inside of an array:
res: A database response.
The first enumerable object property of the first element in an array, or undefined
var val = queryBuilderextractResponseValCOUNTID: 13;console.logval //13
sFromSql to create a JOIN query.
sSelectSql: "table3.username,table1.timestamp,urlType,mimeType,table1.table3Id,url,table2.code,table2.description"sFromSql: "table1 LEFT JOIN table2 ON table1.errorId=table2.errorId LEFT JOIN table3 ON table1.sessionId=table3.sessionId"