04. QueryBuilder

With QueryBuilder you can create conditions or you can catch the result from.

var builder = DMBS().find();
// builder === QueryBuilder

Quick Links:

• builder.autofill($, allowedfields, skippedfields, def_sort, [def_maxlimit], [localized])
• builder.between(name, a, b)
• builder.callback(fn)
• builder.conn(id)
• builder.contains(name)
• builder.custom(fn)
• builder.data(fn)
• builder.done($, fn, [param])
• builder.day(name, [operator], value)
• builder.empty(name)
• builder.equal(fields)
• builder.fail(fn)
• builder.fields(a, b, c, ...)
• builder.first()
• builder.fulltext(name, value, [weight])
• builder.get(path)
• builder.gridfields(fields, [allowed])
• builder.gridfilter(name, obj, type, [column_name])
• builder.gridsort(name_type)
• builder.hour(name, [operator], value)
• builder.id(value)
• builder.in(name, value, [field])
• builder.inarray(name, value, [orempty])
• builder.insert(fn, [params])
• builder.join(field, name)
• builder.language(language, [prefix], [skip])
• builder.limit(count)
• builder.log(msg, [user])
• builder.make(fn)
• builder.minute(name, [operator], value)
• builder.month(name, [operator], value)
• builder.notin(name, value)
• builder.or(fn)
• builder.orm([pk])
• builder.owner(name, value, memberof, [condition])
• builder.page(page, limit)
• builder.paginate(page, limit, maxlimit)
• builder.permit(name, type, value, [useridfield], [userid], [must])
• builder.primarykey(name)
• builder.regexp(name, value)
• builder.search(name, value, [operator])
• builder.searchall(name, value)
• builder.searchfull(value)
• builder.set(prop)
• builder.skip(count)
• builder.sort(name, [desc])
• builder.subquery(prop, query)
• builder.take(count)
• builder.transform(fn)
• builder.query(query, [argument])
• builder.year(name, [operator], value)
• builder.undeleted()
• builder.userid(value)
• builder.where(name, [operator], value)

Properties

builder.value

Contains a raw object for insert/update/modify.

Methods

builder.autofill($, allowedfields, skippedfields, def_sort, [def_maxlimit], [localized])

Creates a filter automatically according to the Total.js Schema. Query parameters will be used for the filtering and parameters:

• fields (separated by comma) will be returned from DB
• page will be used as a page
• limit will be used as a limit otherwise def_maxlimit
• sort will be used for sorting otherwise def_sort (can contain multiple values separated by , comma)

// builder.autofill($, allowedfields, skipfields, def_sort, def_maxlimit);
// @$ {SchemaOptions} Instance of SchemaOptions
// @allowedfields {String} Optional, can contain fields not defined in the schema and must be separated by comma ","
// @skippedfields {String} Optional, can contain skip fields defined in schema and must be separated by comma ","
// @def_sort {String} Optional, default sort in the form "FIELDNAME_asc" or "FIELDNAME_desc"
// @def_maxlimit {Number} Optional, a maximum count of items per page
// @localized {String} Optional, defines localized name of fields
// returns QueryBuilder

// IMPORTANT:
// If "def_maxlimit" is not defined then the pagination isnt't applied

// Example:
db.find('users').autofill($, 'dtcreated:Date,dtupdated:Date', 'id', 'dtcreated_desc', 50);

// Example with localized fields:
db.find('users').autofill($, 'dtcreated:Date,dtupdated:Date', 'id', 'dtcreated_desc', 50, 'name,description').language('sk');

builder.between(name, a, b)

Creates a between condition.

// builder.between(name, a, b);
// @name {String} A field name
// @a {Number} A value
// @b {Number} B value
// returns QueryBuilder

db.find('users').between('age', 20, 30);

builder.callback(fn)

A callback for the response. It's executed if the operation will have a response.

// builder.callback(fn);
// @fn {Function}
// returns QueryBuilder

db.find('users').callback(function(err, response) {
    // @err {ErrorBuilder} can be "null"
    // @response {Array/Object}
});

builder.conn(id)

Sets the connection string according to id.

// builder.conn(id);
// @name {String} A connection ID
// returns QueryBuilder

db.query('SELECT * FROM tbl_user').conn('server_perun');

// BUT IMPORTANT:
// You need to have declared initialization in the form:
dbms.init('server_perun', 'postgresql://user:pass@localhost:5432/dbname1');

builder.contains(name)

Checks if the field name contains some value.

// builder.contains(name);
// @name {String} A field name
// returns QueryBuilder

db.find('users').contains('name');

builder.custom(fn)

Makes a custom filter with help of responded data.

// builder.custom(fn);
// returns QueryBuilder

// fn(QueryBuilder, response, [last_response])

db.read('users').where('id', $.id);
db.read('orders').custom((builder, response) => builder.where('userid', response.id));
// db.read('orders').custom((builder, response, response_last) => builder.where('userid', response.id));

builder.data(fn)

A data-callback. The method is executed if the operation will have a response without any error.

// builder.data(fn);
// @fn {Function}
// returns QueryBuilder

db.find('users').data(function(response) {
    // @response {Array/Object}
});

builder.done($, fn, [param])

This method returns a function with a wrapped callback. If the result is positive then the fn function is executed otherwise is executed $.invalid(err).

// db.done($, fn);
// $ {Total.js SchemaOptions/OperationOptions/Controller}
// @fn {Function(response)} A callback
// @param {Object}
// returns Function(err, response, param)

schema.setUpdate(function($) {
    var db = DBMS();
    db.mod('tbl_user', $.model).where('id', $.id).err('error-users-404').done($, function(response, param) {
        // @response {Object} with a positive result
        console.log(response);
    });
});

builder.day(name, [operator], value)

Creates a day condition.

// builder.day(name, [operator], value);
// @name {String} A field name
// @operator {String} Optional, default "="
// @value {Number} A value
// returns QueryBuilder

db.find('users').day('created', 28);
db.find('users').day('created', '>', 12);

builder.empty(name)

Checks if the field name is empty.

// builder.empty(name);
// @name {String} A field name
// returns QueryBuilder

db.find('users').empty('name');

builder.equal(fields)

This method applies values (according fields argument) as where condition in update() or modify().

// builder.equal(fields);
// @fields {String} Fields separated by comma
// returns QueryBuilder

db.modify('tbl_user', { id: model.id, name: model.name }, true).equal('id');

// The case above will be translated to:
// UPDATE tbl_user SET name=$2 WHERE id=$1
// and if no records won't be updated then DBMS perform insert in the form:
// INSERT tbl_user (id, name) VALUES($1, $2)

builder.fail(fn)

A fail-callback. The method is executed if the operation will contain an error.

// builder.fail(fn);
// @fn {Function}
// returns QueryBuilder

db.find('users').fail(function(err) {
    // @err {ErrorBuilder}
});

builder.fields(a, b, c, ...)

Selects only specific fields.

// builder.fields(a, b, c, ...);
// @a {String} A field name
// @b {String} Optional, a field name
// @c {String} Optional, a field name
// returns QueryBuilder

db.find('users').fields('id', 'name');

builder.first()

Returns only the one document.

// builder.first();
// returns QueryBuilder

db.find('users').first();

builder.fulltext(name, value, [weight])

Creates a fulltext condition.

// builder.fulltext(name, value, [weight]);
// @name {String} A field name
// @value {String} A value
// @weight {Number} Optional, a weight in percentage (default "0")
// returns QueryBuilder

db.find('users').fulltext('name', 'Peter Sirka');

builder.get(path)

Reads a value from DB response. It can read a value from Array of responses or with a plain Object.

// db.find(name);
// @path {String} A path to response
// returns QueryBuilder

builder.in('id', builder.get('users.id'));

builder.gridfields(fields, [allowed])

This method cleans fields defined in e.g. URL address according to the allowed fields.

// db.gridfields(fields, [allowed]);
// @fields {String} A fields defined for example in URL address
// @allowed {String Array} Optional, Allowed fields
// returns QueryBuilder

db.gridfields($.query.fields, schema.fields);

builder.gridfilter(name, obj, type, [column_name])

This is a special method for creating a condition according to j-DataGrid filter.

// db.gridfilter(name, obj, type, [key]);
// @name {String} A property name in "obj"
// @obj {Object} With values for filtering
// @type {Object} A type of value in DB (String, Number, Date, Boolean)
// @column_name {String} Optional column name (default "name" argument)
// returns QueryBuilder

// For example usage in Total.js Schemas
var opt = $.query;

// Sample data:
// opt.type = 'something';
// opt.price = '200 - 300';
// opt.created = '2018';

opt.type && builder.gridfilter('type', opt, String);
opt.price && builder.gridfilter('price', opt, Number);
opt.created && builder.gridfilter('created', opt, Date);

builder.gridsort(name_type)

This is a special method for creating a sort condition according to j-DataGrid filter.

// db.gridsort(name_type);
// @name_type {String} A column name
// returns QueryBuilder

builder.gridsort('price_asc');
builder.gridsort('price_desc');
// or
builder.gridsort('price asc');
builder.gridsort('price desc');
// or multi sort
builder.gridsort('price_asc,dtcreated_desc');

builder.hour(name, [operator], value)

Creates a hour condition.

// builder.hour(name, [operator], value);
// @name {String} A field name
// @operator {String} Optional, default "="
// @value {Number} A value
// returns QueryBuilder

db.find('users').hour('created', 23);
db.find('users').hour('created', '>', 12);

builder.id(value)

Creates a where condition with where('id', value).

// builder.id(value);
// @value {Object/Array} A value
// returns QueryBuilder

db.read('users').id('Peter');
db.read('users').id(1);

builder.in(name, value, [field])

Creates a in condition.

// builder.in(name, value, [field]);
// @name {String} A field name
// @value {Object/Array} A value
// @field {String} Optional, a field name for obtaining of value from value (it takes a value according to the field name)
// returns QueryBuilder

db.find('users').in('name', ['Peter', 'Lucia']);
db.find('users').in('age', [20, 30, 40]);

// Example with "field" argument
db.find('users').in('id', [{ id: 1 }, { id: 2 }], 'id');

builder.inarray(name, value, [orempty])

Tries to find a value (or array) in array column. The condition is valid if some (just one) value is part of array.

// builder.inarray(name, value, [orempty]);
// @name {String} A field name
// @value {Array/Object} A value (can be array)
// @orempty {Boolean} it will be valid if the array will be empty (default: false)
// returns QueryBuilder

db.find('users').inarray('roles', ['admin', 'support']);

builder.insert(fn, [params])

An insert callback. This callback is executed if the inserting will be performed.

// builder.insert(fn);
// @fn {Function}
// @params {Object} Optional
// returns QueryBuilder

db.modify('users', { name: 'Peter' }, true).where('name', 'Peter').insert(function(doc) {
    // doc === { name: 'Peter' }
    doc.created = new Date();
});

// Or with params
db.modify('users', { name: 'Peter' }, true).where('name', 'Peter').insert(function(doc, params) {
    // doc === { name: 'Peter' }
    doc.created = new Date();

    console.log(params);
    // Output: { custom: 'params' }

}, { custom: 'params' });

builder.join(field, name)

This method can join another table/database. This method doesn't create a real join, it performs another SELECT with unique values. You can join data between different database, nested joins and you can do 1:1 or 1:N relations.

// builder.join(field, name);
// @field {String} Joined data wil be assigned into this field name
// @name {String} (Database+) Table name
// returns {QueryBuilder} returns a new QueryBuilder for "table"

Example for relation: 1:1

// Good to know:
var merchants = db.find('tbl_merchant');
var products = merchants.join('products', 'tbl_product').on('merchantid', 'id').first();
// builder.on('COLUM_NAME_IN_tbl_product', 'COLUM_NAME_IN_tbl_merchant')

Output:

[
    {
      "name": "Merchant name",
      "products": { name: 'Shoes', ... }
    }
]

Example for relation: 1:N

var merchants = db.find('tbl_merchant');
var products = merchants.join('products', 'tbl_product').on('merchantid', 'id');

Output:

[
    {
      "name": "Merchant name",
      "products": [{ name: 'Shoes', ... }]
    }
]

Example for relation nested joins

var merchants = db.find('tbl_merchant');
var products = merchants.join('products', 'tbl_product').on('merchantid', 'id').first();
var categories = products.join('category', 'cl_category').on('id', 'categoryid').first();

Output:

[
    {
      "name": "Merchant name"
      "products": { name: 'Shoes', category: { name: 'Shoes', ... }, ... }
    }
]

builder.language(language, [prefix], [skip])

Very helpful feature for multi-language records (localization). The function replaces § chars for entered language with the prefix. It works with builder.fields() and all filtering methods.

// builder.language(language, [prefix])
// @language {String}, can be nullable
// @prefix {String} optional, default "DBMS.languageprefix" (default: "_")
// @skip {String} optional

builder.language('sk');
builder.fields('name§,description§');

// Will be translated to:
// SELECT name_sk AS name, description_sk AS description

Skips specific language:

builder.language('en', '_', 'en');
builder.fields('name§,description§');
// SELECT name, description

builder.language('sk', '_', 'en');
builder.fields('name§,description§');
// SELECT name_sk AS name, description_sk AS description

builder.limit(count)

Sets a limit.

// builder.limit(count);
// @count {Number} A count of records
// returns QueryBuilder

db.find('users').limit(10);

builder.log(msg, [user])

Writes a log into the file.

// builder.log(msg, [user]);
// @msg {String} A log message
// @user {String} Optional, a user name
// returns QueryBuilder

db.remove('users').log('Removed all users', 'Peter');

builder.make(fn)

Creates a QueryBuilder scope.

// builder.make(fn);
// @fn {Function}
// returns DBMS

db.find('users').make(function(builder) {
    builder.take(10);
    builder.set('users');
}).find('products').make(function(buider) {
    builder.take(10); 
    builder.set('products');
});

builder.minute(name, [operator], value)

Creates a minute condition.

// builder.minute(name, [operator], value);
// @name {String} A field name
// @operator {String} Optional, default "="
// @value {Number} A value
// returns QueryBuilder

db.find('users').minute('created', 23);
db.find('users').minute('created', '>', 12);

builder.month(name, [operator], value)

Creates a month condition.

// builder.month(name, [operator], value);
// @name {String} A field name
// @operator {String} Optional, default "="
// @value {Number} A value
// returns QueryBuilder

db.find('users').month('created', 11);
db.find('users').month('created', '>', 2);

builder.notin(name, value)

Creates a not in condition.

// builder.notin(name, value);
// @name {String} A field name
// @value {Object/Array} A value
// returns QueryBuilder

db.find('users').notin('name', ['Peter', 'Lucia']);
db.find('users').notin('age', [20, 30, 40]);

builder.or(fn)

Creates or scope.

// builder.or(fn);
// @fn {Function}
// returns DBMS

db.find('users').or(function(builder) {
    builder.where('name', 'Peter');
    builder.where('name', 'Lucia');
    // Creates a where condition (name='Peter' OR name='Lucia')
});

builder.orm([pk])

Creates an object relation mapping.

// builde.orm([pk])
// @pk {String} a primary key (optional, default empty)

db.read('tbl_user').where('id', 123).orm().data(function(err, user) {

    // "user.dbms" will contain a special copy of QueryBuilder

    // Copies all "obj" values to "user" instance
    // dbms.copy(obj, [true/false: only existing keys])
    user.dbms.copy(obj);
    
    // Replaces the record with only modified values
    // Returns {Boolean}
    if (user.dbms.modified(obj)) {
        // some values are modified
    }

    // Replaces "user" instance with a new object
    user.dbms.replace(obj);

    // Continues with DBMS processing of other queries and "user" instance will be processed in a new connection
    user.dbms.continue();

    // Saves the current values into the db
    user.dbms.save(callback(err, inserted_count));

    // Removes the current object
    user.dbms.remove(callback(err, inserted_count));

});

builder.owner(name, value, memberof, [condition])

Creates an ownership condition.

// builder.owner(name, value, memberof, [condition])
// @name {String} a colum name
// @value {String/Number} a value for column "name"
// @member {Array String/Number} a member identifiers (or user is member of owner IDENTIFIERS)
// @condition {Object} optional, additianal condition COLUMN:VALUE

db.find('tbl_app').owner('userid', $.user.id, $.user.members, { shared: true });
// DMBS will generate something like this:
// (userid=$.user.id OR (userid IN ($.user.members) AND shared=TRUE))

Another example:

db.find('tbl_user').fields('dtupdated').take(5).orm('id').data(function(err, users) {
    for (var i = 0; i < users.length; i++) {
        var user = users[i];
        user.dtupdated = new Date();
        user.save();
    }
});

builder.page(page, limit)

Sets take and skip according to the page and limit.

// builder.page(page, limit);
// @page {Number} A current page index
// @limit {Number} A maximum count of records per page
// returns QueryBuilder

db.find('users').page(1, 20);

builder.paginate(page, limit, maxlimit)

Sets take and skip according to the page and limit. This method converts page and limit to number automatically and safely if some value is string.

// builder.page(page, limit);
// @page {String} A current page index (default: 1)
// @limit {String} A maximum count of records per page
// @maxlimit {Number} A maximum/default count of records if "@limit" exceeds
// returns QueryBuilder

db.find('users').paginate(query.page, query.limit, 100);

builder.permit(name, type, value, [useridfield], [userid], [must])

Makes a condition for permissions. It's targeted for our designed permission system. It works with PostgreSQL version only:

-- Data-Type VARCHAR ARRAY
permissions _varchar

Permissions are stored like this:

['C@admin', 'W@admin', 'R@admin', 'D@admin', 'R123456', 'R#employees']

• first char e.g. C means a permission, in this case:
  • C create
  • R read
  • U update
  • D delete
• second char @ means a role name, so e.g. @admin means admin role
• second char # means a group name, so e.g. #employees means employees group
• otherwise it means user identifier, e.g. R123456 is a user with id 123456

// builder.permit(name, type, value, [useridfield], [userid], [must]);
// @name {String} a field name
// @type {String} a permission type
// @value {String Array} list of user permissions
// @useridfield {String} optional, a name of userfield
// @userid {String} optional, current user id
// @must {Boolean} optional, reverses condition (default: false)

var mypermissions = ['@admin', '123456', '#employees'];
var myuserid = '654321';

// Tries to find all records with "R"userrolegroup, e.g. "R@admin", "@R123456" or "R#employees"
builder.permit('permissions', 'R', permissions);

// Tries to find all records with "R"userrolegroup, "W"userrolegroup, "D"userrolegroup
// A record must contain only the one permission type
builder.permit('permissions', 'RWD', permissions);

// Tries to find all records with "R"userrolegroup or user records
builder.permit('permissions', 'R', permissions, 'userid', myuserid);

builder.primarykey(name)

Sets a name of the primary key. It's targeted for PostgreSQL only for inserting values into tables, schema of which contains primary key with serial data type.

// builder.primarykey(name);
// @name {String} Name of primary key
// returns QueryBuilder

db.insert('tbl_document', { name: 'New document' }).primarykey('id').callback(function(err, response) {
    console.log('Inserted ID: ' + response);
});

builder.regexp(name, value)

Creates a Regular Expression condition.

// builder.regexp(name, value);
// @name {String} A field name
// @value {RegExp} A value
// returns QueryBuilder

db.find('users').regexp('name', /\d+/);

builder.search(name, value, [operator])

Performs like operation.

// builder.search(name, value, [operator]);
// @name {String} A field name
// @value {String} A value
// @operator {String} Optional, possible values: "*" (default), "beg", "end"
// returns QueryBuilder

db.find('users').search('name', 'Peter');
db.find('users').search('name', 'Peter', 'beg');

builder.searchall(name, value)

Performs like operation for each word.

// builder.search(name, value, [operator]);
// @name {String} A field name
// @value {String / String Array} A value
// returns QueryBuilder

db.find('users').searchall('name', 'Peter Sirka');
// is same as:
db.find('users').searchall('name', ['Peter', 'Sirka']);

builder.searchfull(value)

Performs like operation for the entire value in a row (in all columns). DBMS performs lowercase and replaces y for i.

// builder.searchfull(value);
// @value {String} A value
// returns QueryBuilder

db.find('users').searchfull('Peter Sirka');

builder.set(prop)

Assigns a response to the defined property or it can assign a value to insert/update/modify operation (value must be defined).

// builder.set(prop, [value]);
// @prop {String} A property name
// @value {Object} A value, optional
// returns QueryBuilder

// !!! IMPORTANT TO KNOW !!!
// 1. Wihtout value the method assigns the result to output
db.find('users').set('users');
// Main output: { users: [ ..., ..., ... ] }

// 2. With filled value the method assigns the value into the model for insert/update/modify operation
db.update('users').set('name', 'Peter').set('age', 33).where('id', 1234);

builder.skip(count)

Skips a count of records.

// builder.skip(count);
// @count {Number} A count of records
// returns QueryBuilder

db.find('users').skip(5);

builder.sort(name, [desc])

Creates a sort condition.

// builder.sort(name, [desc]);
// @name {String} A field name
// @desc {Boolean} Default: "false"
// returns QueryBuilder

db.find('users').sort('name');
db.find('users').sort('age', true);

builder.subquery(prop, query)

Creates a subquery for SELECT. Currently is supported for PostgreSQL only.

// builder.subquery(prop, query);
// @prop {String} A field name
// @query {String} A query
// returns QueryBuilder

db.find('tbl_user').subquery('countorders', 'SELECT COUNT(1) FROM tbl_order WHERE tbl_order.userid=tbl_user.id');

builder.take(count)

Takes a count of records.

// builder.take(count);
// @count {Number} A count of records
// returns QueryBuilder

db.find('users').take(5);

builder.transform(fn)

Performs a transformation for value which will be inserted/modified. Works only with db.insert(), db.modify() or db.update() methods.

builder.transform(function(item, response, lastresponse) {
    // item {Object} Current object for inserting/updating (keep a reference)
    // response {Object} Contains all results from previous commands
    // lastresponse {Object} Contains latest response

    // Example:
    item.roomid = lastresponse.id;
});

builder.query(query, [argument])

Appends a raw query.

// builder.query(query, [arguments]);
// @query {String}
// returns QueryBuilder

db.find('users').query('age>30');

builder.year(name, [operator], value)

Creates a year condition.

// builder.year(name, [operator], value);
// @name {String} A field name
// @operator {String} Optional, default "="
// @value {Number} A value
// returns QueryBuilder

db.find('users').year('created', 2018);
db.find('users').year('created', '>', 2012);

builder.userid(value)

Creates a where condition with where('userid', value).

// builder.userid(value);
// @value {Object/Array} A value
// returns QueryBuilder

db.read('users').userid('Peter');
db.read('users').userid(1);

builder.undeleted()

Creates a where condition with where('isremoved', false).

// builder.undeleted();
// returns QueryBuilder

db.find('users').undeleted();

builder.where(name, [operator], value)

Creates a where condition.

// builder.where(name, [operator], value);
// @name {String} A field name
// @operator {String} Optional, default "="
// @value {Object} A value
// returns QueryBuilder

db.find('users').where('name', 'Peter');
db.find('users').where('age', '<>', 33);