DBMS / 04. QueryBuilder
Updated: 12. September 2019
Author: Peter Širka

04. QueryBuilder

Professional Support Chat with contributors

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

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

Quick Links:

Properties

builder.value

Contains a raw object for insert/update/modify.

Methods

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

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
// builder.autofill($, allowedfields, skipfields, def_sort, def_maxlimit);
// @$ {SchemaOptions} Instance of SchemaOptions
// @allowedfields {String} Optional, can contain fields separated by comma ","
// @skippedfields {String} Optional, can contain fields separated by comma ","
// @def_sort {String} Optional, default sort in the form "FIELDNAME_asc" or "FIELDNAME_desc"
// @def_maxlimit {Number} Optional, a maximum items per page
// 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);

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.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.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.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');

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.in(name, value)

Creates a in condition.

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

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

builder.insert(fn)

Inserting callback. This callback is executed if the inserting will be performed.

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

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

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

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

Output:

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

Example for relation: 1:N

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

Output:

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

Example for relation nested joins

var merchants = db.find('merchants');
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.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
    user.dbms.copy(obj);

    // 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));

});

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.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.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.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.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);