DBMS / 04. QueryBuilder
Updated: 15. November 2018
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:

Methods

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.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').data(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 resopnse
// returns Function

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

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.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
// @limit {String} A maximum count of records per page
// @maxlimit {Number} A maximum count of records per page
// returns QueryBuilder

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

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])

Creates a not in condition.

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

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