Query Builder

The database query builder in OstroJS provides a simple, intuitive interface for generating and performing database queries. It works with all of OstroJS's supported database systems and may be used to conduct most database operations in your application.

Prepare parameter binding is used by the OstroJS query builder to safeguard your application from SQL injection threats. Strings supplied to the query builder as query bindings do not need to be cleaned or sanitised.

To start a query, utilise the table method given by the DB facade. The table method produces a fluent query builder object for the specified table, enabling you to add extra restrictions to the query and then use the get method to receive the query's results:

const Controller = require('~/app/http/controllers/controller')
const DB = require('@ostro/support/facades/database')
class UserController extends Controller {
    /**
     * Show a list of all of the application's users.
     *
     */
    async index({ view }) {
        let users = await DB.table('users').get();

        view('user.index', {users});
    }
}

module.exports = UserController

The get function returns an  instance containing the query's results, each of which is a NodeJS object instance. You may get the value of each column by using the column as a property of the object:

const Controller = require('~/app/http/controllers/controller')
const DB = require('@ostro/support/facades/database')

class UserController extends Controller {

    /**
     * Show a list of all of the application's users.
     *
     */
    async index({ view }) {
    
        let users = await DB.table('users').get();

        for(let user of users) {
            console.log(user.name);
        }
    }
}

module.exports = UserController

To start a query, utilise the table method given by the DB facade. The table method produces a fluent query builder object for the specified table, enabling you to add extra restrictions to the query and then use the get method to receive the query's results:

const Controller = require('~/app/http/controllers/controller')
const DB = require('@ostro/support/facades/database')
class UserController extends Controller {
    /**
     * Show a list of all of the application's users.
     *
     */
    async index({ view }) {
        let users = await DB.table('users').get();

        view('user.index', {users});
    }
}

module.exports = UserController

The get function returns an @ostro/support/collection instance containing the query's results, each of which is a NodeJS object instance. You may get the value of each column by using the column as a property of the object:

const Controller = require('~/app/http/controllers/controller')
const DB = require('@ostro/support/facades/database')

class UserController extends Controller {

    /**
     * Show a list of all of the application's users.
     *
     */
    async index({ view }) {
    
        let users = await DB.table('users').get();

        for(let user of users) {
            console.log(user.name);
        }
    }
}

module.exports = UserController

You may use the first method of the DB facade to obtain a single row from a database table. A single object will be returned by this method:

let user = await DB.table('users').where('name', 'John').first();

console.log(user.email);

The pluck method may be used to get an @ostro/support/collection object containing the values of a single column. We'll obtain a list of user titles in this example:

const DB = require('@ostro/support/facades/database')
let titles = await DB.table('users').pluck('title');

for (let title of titles) {
    console.log(title);
}

By passing a second parameter to the pluck function, you may define which column the resultant collection should use as its keys:

let titles = await DB.table('users').pluck('title','name');

for (let title of titles) {
    console.log(title);
}

It's possible that you'll need to put an arbitrary string into a query at some point. You may utilise the raw method offered by the DB facade to construct a raw string expression:

let users = await DB.table('users')
             .select(DB.raw('count(*) as user_count, status'))
             .where('status', '<>', 1)
             .groupBy('status')
             .get();

To inject a raw "where" clause into your query, use the whereRaw and orWhereRaw methods. As a second input, these methods take an optional array of bindings:

let orders = await DB.table('orders')
                .whereRaw('price > IF(state = "TX", ?, 100)', [200])
                .get();

To inject a raw "where" clause into your query, use the whereRaw and orWhereRaw methods. As a second input, these methods take an optional array of bindings:

To supply a raw string as the value of the "having" clause, use the havingRaw and orHavingRaw methods. As a second input, these methods take an optional array of bindings:

let orders = await DB.table('orders')
                .select('department', DB.raw('SUM(price) as total_sales'))
                .groupBy('department')
                .havingRaw('SUM(price) > ?', [2500])
                .get();

To supply a raw string as the value of the "order by" clause, use the orderByRaw method:

let orders = await DB.table('orders')
                .orderByRaw('updated_at - created_at DESC')
                .get();

To supply a raw string as the value of the group by clause, use the groupByRaw method:

let orders = await DB.table('orders')
                .select('city', 'state')
                .groupByRaw('city, state')
                .get();

You may also use the query builder to add join clauses to your queries. You may use the join method on a query builder instance to execute a rudimentary "inner join." The name of the table you want to connect to is the first parameter supplied to the join function, and the subsequent arguments indicate the join's column constraints. You may even use a single query to connect many tables:

const DB = require('@ostro/support/facades/database');
let users = await DB.table('users')
            .join('contacts', 'users.id', '=', 'contacts.user_id')
            .join('orders', 'users.id', '=', 'orders.user_id')
            .select('users.*', 'contacts.phone', 'orders.price')
            .get();

Use the leftJoin and rightJoin methods instead of the innerJoin method to do a "left join" or "right join" instead of a "inner join." The signature of these methods is the same as that of the join method:

let users = await DB.table('users')
            .leftJoin('posts', 'users.id', '=', 'posts.user_id')
            .get();

let users = await DB.table('users')
            .rightJoin('posts', 'users.id', '=', 'posts.user_id')
            .get();

To make a "cross join," use the crossJoin method. Between the first table and the linked table, cross joins produce a cartesian product:

let sizes = await DB.table('sizes')
            .crossJoin('colors')
            .get();

You may even utilise more complex join clauses. To get started, call the join function with a closure as the second parameter. 

await DB.table('users')
        .join('contacts', function ($join) {
            $join.on('users.id', '=', 'contacts.user_id').orOn(...);
        })
        .get();

If you want to employ a "where" clause in your joins, you may use the JoinClause instance's where and orWhere methods. Rather of comparing two columns, these approaches compare a column to a value:

await DB.table('users')
        .join('contacts', function ($join) {
            $join.on('users.id', '=', 'contacts.user_id')
                 .where('contacts.user_id', '>', 5);
        })
        .get();

The query builder also has a feature that allows you to "union" two or more queries. You might, for example, make an initial query and then use the union method to combine it with others:

const DB = require('@ostro/support/facades/database');
let first = DB.table('users')
            .whereNull('first_name');

let users = DB.table('users')
            .whereNull('last_name')
            .union(first)
            .get();

The query builder also has a unionAll method in addition to the union method. Duplicate results from queries coupled with the unionAll technique will not be eliminated. The method signature of the unionAll method is the same as that of the union method.

You may use the where method in the query builder to add "where" clauses to the query. Three parameters are required for the most basic call to the where function. The first input is the column's name. The operator, which can be any of the database's available operators, is the second parameter. The value to compare to the column's value is the third parameter.

For example, the following query returns people whose votes column value is equal to 100 and whose age column value is more than 35:

let users = await DB.table('users')
                .where('votes', '=', 100)
                .where('age', '>', 35)
                .get();

If you wish to check if a column is equal to a certain value, you may send the value as the second parameter to the where method for convenience. OstroJS will make the assumption that you want to utilise the = operator:

let users = await DB.table('users').where('votes', 100).get();

As previously stated, you may use any operator that your database system supports:

var users = await DB.table('users')
                .where('votes', '>=', 100)
                .get();

var users = await DB.table('users')
                .where('votes', '<>', 100)
                .get();

var users = await DB.table('users')
                .where('name', 'like', 'T%')
                .get();

The "where" clauses will be combined together using the and operator when chaining together calls to the query builder's where function. The orWhere function, on the other hand, may be used to link a clause to the query using the or operator. The parameters for the orWhere method are the same as for the where method:

let users = await DB.table('users')
                    .where('votes', '>', 100)
                    .orWhere('name', 'John')
                    .get();

If you need to group an "or" condition within parentheses, you may pass a closure as the first argument to the orWhere method:

let users = await DB.table('users')
            .where('votes', '>', 100)
            .orWhere(function($query) {
                $query.where('name', 'Abigail')
                      .where('votes', '>', 50);
            })
            .get();

The following SQL will be generated as a result of the preceding example:

select * from users where votes > 100 or (name = 'Abigail' and votes > 50)

whereBetween / orWhereBetween

The whereBetween method checks whether the value of a column is between two values:

let users = await DB.table('users')
           .whereBetween('votes', [1, 100])
           .get();

whereNotBetween / orWhereNotBetween

The whereNotBetween method checks that the value of a column is not between two values:

let users = await DB.table('users')
                    .whereNotBetween('votes', [1, 100])
                    .get();

whereIn / whereNotIn / orWhereIn / orWhereNotIn

The whereIn function checks that the value of a given column is contained inside the array:

let users = await DB.table('users')
                   .whereIn('id', [1, 2, 3])
                   .get();

The whereNotIn function checks if the value of a given column is not present in the provided array:

let users = await DB.table('users')
                    .whereNotIn('id', [1, 2, 3])
                    .get();

whereNull / whereNotNull / orWhereNull / orWhereNotNull

The whereNull function checks that the provided column's value is NULL:

let users = await DB.table('users')
                .whereNull('updated_at')
                .get();

The whereNotNull function checks that the value of a column isn't NULL:

let users = await DB.table('users')
                .whereNotNull('updated_at')
                .get();

In order to accomplish the logical grouping requested by your query, you may need to group numerous "where" clauses between parenthesis. In fact, to avoid unexpected query behaviour, you should always combine calls to the orWhere function in parenthesis. You may do this by passing a closure to the where method:

let users = await DB.table('users')
           .where('name', '=', 'John')
           .where(function ($query) {
               $query.where('votes', '>', 100)
                     .orWhere('title', '=', 'Admin');
           })
           .get();

Passing a closure to the where function tells the query builder to start a constraint group, as you can see. A query builder object will be returned to the closure, which you can use to specify the constraints that should be included within the parenthesis group. The following SQL will be generated as a result of the preceding example:

select * from users where name = 'John' and (votes > 100 or title = 'Admin')

You may write "where exists" SQL clauses with the whereExists function. The whereExists method takes a closure that returns a query builder object, enabling you to specify the query to be used inside the "exists" clause:

let users = await DB.table('users')
           .whereExists(function ($query) {
               $query.select(DB.raw(1))
                     .from('orders')
                     .whereColumn('orders.user_id', 'users.id');
           })
           .get();

The query above will produce the following SQL:

select * from users
where exists (
    select 1
    from orders
    where orders.user_id = users.id
)

A "where" clause that compares the results of a subquery to a specific value is sometimes required. You may do this by calling the where function with a closure and a value. The following query, for example, will return all users with a recent "membership" of a specific kind;

let users = await DB.table('users').where(function($query) {
                $query.select('type')
                    .from('membership')
                    .where('membership.user_id', '123')
                    .orderBy('membership.start_date', 'desc')
                    .limit(1);
            }).get()

You may use the orderBy method to arrange the query results by a specific column. The orderBy method's first parameter should be the column you want to sort by, while the second argument sets the sort direction, which may be either asc or desc:

let users = await DB.table('users')
            .orderBy('name', 'desc')
            .get();

You may use orderBy as many times as you like to sort by several columns:

let users = await DB.table('users')
            .orderBy('name', 'desc')
            .orderBy('email', 'asc')
            .get();

You may quickly arrange results by date using the most recent and oldest ways. The result will be arranged by the table's created at column by default. You may also give the name of the column you want to sort by:

let user = await DB.table('users')
                .latest()
                .first();

To sort the query results in a random order, use the inRandomOrder function. You might, for example, use this way to get a random user:

$randomUser = await DB.table('users')
                .inRandomOrder()
                .first();

The groupBy and having methods may be used to group the query results, as you might imagine. The signature of the having method is identical to that of the where method:

let users = await DB.table('users')
                .groupBy('account_id')
                .having('account_id', '>', 100)
                .get();

You can give the group numerous parameters. To group by many columns, use the following method:

let users = await DB.table('users')
                .groupBy('first_name', 'status')
                .having('account_id', '>', 100)
                .get();

In addition, the query builder has an insert function for inserting records into the database table. An array of column names and values is sent to the insert method:

await DB.table('users').insert({
    'email' : 'kayla@example.com',
    'votes' : 0
});

By supplying an object of arrays, you may insert several records at once. Each object corresponds to a record to be added into the table:

await DB.table('users').insert([
    {'email' : 'picard@example.com', 'votes' : 0},
    {'email' : 'janeway@example.com', 'votes' : 0},
]);

The upsert method will create new records if they don't already exist and update existing records with new values you specify. The first input to the method is a list of values to insert or update, and the second argument is a list of the column(s) that uniquely identify entries in the related table. If a matched record already exists in the database, the method's third and final parameter is an array of columns that should be updated:

await DB.table('flights').upsert([
    {'departure' : 'Oakland', 'destination' : 'San Diego', 'price' : 99},
    {'departure' : 'Chicago', 'destination' : 'New York', 'price' : 150}
], ['departure', 'destination'], ['price']);

OstroJS will attempt to insert two records in the example above. If a record with the identical departure and destination column values already exists, OstroJS will change the price column of that record.

The query builder can use the update method to edit existing entries in addition to adding them into the database. The update method, like the insert method, takes an array of column and value pairs to specify which columns should be modified. You may use where clauses to limit the update query:

let affected = await DB.table('users')
              .where('id', 1)
              .update({'votes' : 1});

You might wish to edit an existing database record or create one if there isn't one there. The updateOrInsert technique might be used in this case. The updateOrInsert method takes two arguments: an array of search criteria to discover the record and an array of column and value pairs to update the columns.

Using the first argument's column and value pairs, the updateOrInsert function will try to find a matching database record. If the record already exists, the values in the second parameter will be modified. If the record cannot be found, a new record with the combined characteristics of both parameters will be created:

await DB.table('users')
    .updateOrInsert(
        {'email' : 'john@example.com', 'name' : 'John'},
        {'votes' : '2'}
    );

To delete records from the table, use the delete method in the query builder. Before executing the delete function, you can include "where" clauses to confine remove statements:

await DB.table('users').delete();

await DB.table('users').where('votes', '>', 100).delete();