Query Builders

We've talked about how you can build and initialize your database using migrations and seeders. There is also another aspect of working with databases, which is running database queries to fetch or update your database.

Writing queries in different databases, needs you to know the specific syntax of that database system. However, writing raw database queries in your code would make it dirty and hard to maintain. There are some database helpers which come with a query builder feature to ease the process of writing queries.

Query Builder#

The query builder is upon the Builder pattern which allows you to glue different parts of your query then it will be converted to a query of that database. This will be done by the database drivers. Silvie has a built-in query builder which is configured on the Models by default to point to the right table belonging to the model.

Constructor#

To start using a query builder you need to create an instance of it. This can be done by using the QueryBuilder constructor which is located at silvie/database/builders/query. The constructor will accept a table name which is optional if you plan not to select data from an actual table.

import QueryBuilder from 'silvie/database/builders/query';
const qb = new QueryBuilder('users');

Selections#

qb.select()#

The basic select method accepts one or more string parameters as column names and adds them to the selection collection.

qb.select('name', 'family', 'age');
qb.select('phone', 'email');
// The result will fetch all these 5 columns

qb.selectCount()#

To select the count of the result set, you can use this method, and you should provide an alias name for the aggregate column.

qb.selectCount('users_count');

qb.selectAverage()#

You may also want to get the average of column, so you can pass the name of that column, and an alias name for the aggregate column to this method. Note that the average method only works for numeric columns.

qb.selectAverage('age', 'age_average');

qb.selectSum()#

This method will select the summation of values of a column. This method accepts a column name and an alias name for the aggregate column. Note that the sum method only works for numeric columns.

qb.selectSum('balance', 'total_users_balance');

qb.selectMin()#

This method will select the minimum value of a column. Jus give it a column name and an alias name for the aggregate column.

qb.selectMin('games_played', 'minimum_games_played');

qb.selectMax()#

This method will add an aggregate selection to your query to select the maximum value of a column. You need to give it a column name and an alias for the aggregate column.

qb.selectMax('balance', 'maximum_user_balance');

qb.selectSub()#

There are some cases that you need to do a complex query for a single column of your selection. You can pass a query builder to this method to do the sub selection. Note that your sub query needs to have only one column in its selection.

qb.selectSub(
new QueryBuilder('posts')
.select('posts.id')
.whereColumn('posts.user_id', 'users.id')
.orderBy('posts.id', 'desc')
.first(),
'latest_post_id'
);

qb.selectRaw()#

If your database supports raw queries, you will be able to write a raw database query here. You should pass a query string, and a params array which defaults to an empty array [] if you don't specify it.

qb.selectRaw("CONCAT(`name`, ' ', `family`) AS `fullname`");
// Selects the user fullname by concatenating the name and familty columns
qb.selectRaw(
'(SELECT COUNT(*) FROM `comments` WHERE `comments`.`user_id` = `users`.`id` AND `comments`.`post_id` = ?) AS `commentes_on_post`',
[12]
);
// Selects the count of user comments on an specific post
caution

The raw queries will be added to the final query untouched, so use it if you know what you are doing.

Conditions#

A query builder needs to have conditions to limit what you are fetching from the database. Here we group conditions into 3 groups based on what their usage is:

Where conditions will specify the query criteria. Having conditions will define the query criteria for grouped queries using aggregate functions. Join conditions will indicate how two tables or queries should be joined together.

The QueryBuilder class implements all of Where Condition Builder methods to let your define your query criteria. It also implements all of Having Condition Builder methods for you to use them in combination with your group queries. Therefore, you are able to call those methods directly from a query builder instance. Take a look at the following examples:

const results = await qb.where('name', 'Hannah').get();
// Returns all users where their name is 'Hannah'
const results = await qb.whereBetween('age', [20, 32]).get();
// Returns all users where their age is between 20 and 32

They also can be used in combination:

const results = await qb.whereLike('name', 'Jo%')
.whereBetween('birthdate', ['1998-01-18', '2020-11-23'])
.where('email_verified', false)
.get();
// Returns all users where their name starts with 'Jo'
// and their birthdate is between 1998-01-18 and 2020-11-23
// and they have not verified their email

As mentioned before, having condition methods are also available on the query builder instance when you are using groups in your queries. Here are a few examples of their usage:

const results = await qb.select('name')
.selectCount('count')
.groupBy('name')
.having('count', '>', 3)
.get();
// Returns the names that are repeated more than 3 times

To learn more about available methods and their usage, please read Condition Builders of this page.

There are other conditional methods for querying soft deleted records too. You need to enable soft deletes on your query builder before you try to use these methods. These methods will add the proper conditions to your queries to indicate how soft deleted records should be treated.

qb.withTrashed()#

This method will allow the query to fetch all records, including soft deleted records.

qb.withTrashed();

qb.onlyTrashed()#

This method will only fetch soft deleted records.

qb.onlyTrashed();

qb.withoutTrashed()#

This method will only fetch records that are not soft deleted. The default behavior of query builder is not fetching soft deleted records.

qb.withoutTrashed();

Order#

qb.orderBy()#

This method will order the results by a given column. The direction can be specified by the second parameter which accepts 'asc', 'desc', 'ASC' or 'DESC' as its value. The direction parameter is optional and defaults to ASC.

qb.orderBy('name');
// Orders results by an ascending order of 'name' field
qb.orderBy('created_at', 'DESC');
// Orders results by a descending order of 'created_at' field

The order methods can be used more than once if you are going to order the results by multiple columns. For example, the above code orders the result by their name and orders the records with the same name, by their creation dates.

qb.reorder()#

This method will clear the previously set orders on the query builder instance. If you pass a column and a direction to it, it will set a new order on the query builder.

qb.reorder();
// Clears the query order
qb.reorder('family');
// Clears order and sets a new order by 'family' field

qb.shuffle()#

This method will order the results randomly which results to a shuffled result.

qb.shuffle();

qb.orderByRaw()#

You can write more complex order queries by using this method. This method will accept a query string and its parameters, And will add this query to your order clause untouched.

qb.orderByRaw(
'(SELECT COUNT(*) FROM `posts` WHERE `posts`.`user_id` = `users`.`id`) DESC'
);

For example, the above code will order the result by post count of each user. This code is just an example and has a very low performance, since it will run that sub query for each record.

caution

The raw queries will be added to the final query untouched, so use it if you know what you are doing.

Group#

qb.groupBy()#

This method will group the results with the given columns. It is often used with the aggregate selections.

const userPostCount = await qb.selectCount('post_count')
.groupBy('user_id')
.get();

The above code will return the count of posts for each user.

qb.groupByRaw()#

This method will add a raw query to the group clause.

const userDailyPosts = qb.selectCount('daily_posts')
.join('posts', 'posts.user_id', 'users_id')
.groupByRaw('`users`.`id`, DATE(`posts`.`created_at`)')
.where('user.id', 2)
.get();
caution

The raw queries will be added to the final query untouched, so use it if you know what you are doing.

Offset#

qb.offset()#

This method will be used to skip a specified number of records and fetch the rest.

qb.offset(10);
// This query won't fetch the first 10 records

qb.skip()#

This is an equivalent of offset() method.

qb.skip(10);
// This query won't fetch the first 10 records

Limit#

qb.limit()#

This method will be used to limit the number of records that are going to be fetched.

qb.limit(7);
// This will return the first 7 records
qb.offset(10).limit(5);
// This will skip the first 10 records
// And returns the next 5 records

qb.take()#

This is an equivalent of limit() method.

qb.take(7);
// This will return the first 7 records
qb.offset(10).take(5);
// This will skip the first 10 records
// And returns the next 5 records

Joins#

qb.join()#

This method will do an INNER JOIN between two tables.

If the table parameter is a string, it will try to join that with an actual table. However, it is possible to do a join with another QueryBuilder which will be assumed as a sub query.

If the column1 parameter is a Function, it will be used as a callback function which will get a JoinConditionBuilder as its only parameter, and you need to specify the join condition criteria with that instance of join condition builder.

If you pass a string to the column1 parameter, It will be assumed as a column name and expects you specify the next parameter.

The operator parameter is a string and its value depends on column2 parameter. If you specify a value for the column2, This parameter will be treated as an actual operator. If you don't specify a column2, the operator parameter will be assumed as the column2 parameter and operator will take its default which is =.

The alias parameter will set an alias for the joined table to be used as an abbreviation in other query parts.

This specification will apply to the rest of join methods as mention below.

qb.leftJoin()#

This method will do a LEFT JOIN between two tables.

For more information about what these parameters do, please read join() method description.

qb.rightJoin()#

This method will do a RIGHT JOIN between two tables.

For more information about what these parameters do, please read join() method description.

qb.crossJoin()#

This method will do a CROSS JOIN between two tables.

For more information about what these parameters do, please read join() method description.

qb.outerJoin()#

This method will do an OUTER JOIN between two tables.

For more information about what these parameters do, please read join() method description.

Unions#

qb.union()#

This method will add the results of a query builder to the end of the current query builder. Note that the columns, and their data types must be the same. The union will remove the duplicate records from your results by default, unless you pass a true to the all parameter.

qb.select('name', 'family').union(
new QueryBuilder('admins').select('name', 'family'),
true
);

qb.unionRaw()#

This method will be used when you want to write a custom select for the union clause. It accepts a query string, and a parameter array. The union will remove the duplicate records from your results by default, unless you pass a true to the all parameter.

qb.select('name', 'family').union(
'SELECT `name`, `family` FROM `admins` WHERE `admins`.`id` > ?',
[10],
true
);
caution

The raw queries will be added to the final query untouched, so use it if you know what you are doing.

Fetch#

qb.get()#

This method will execute the query and returns an array of results.

const results = await qb.get();

qb.first()#

This method will execute the query and returns the first entry of the result set. This is done by temporarily limiting the query itself. You may get a null if there was nothing found within the query criteria.

const result = await qb.first();

qb.pluck()#

Pluck method will return an array containing the values of a given column. It also will return a hash table if you specify a key column. This method will retrieve the data from the database and maps the results into an array or reduces it into a hash table.

const userIds = await qb.pluck('id');
// Returns an array of user ids
const userBalances = await qb.pluck('id', 'balance');
// Returns an object with 'id' keys and 'balance' values

When you specify a value column and expecting the method to return a hash table, by default it will throw an error if it faces a duplicate key. You can set the overwrite parameter to true to overwrite the values for existing keys.

Existence#

qb.exists()#

This method will return true if it finds one or more records in your query criteria.

const userExists = await qb.where('phone', '+18005551234')
.exists();

qb.doesntExist()#

This method will return true if there were no records in the query criteria.

const isNewUser = await qb.where('phone', '+18005551234')
.doesntExist();

Aggregates#

qb.count()#

This method will return the count of database records matching your query criteria.

const usersCount = await qb.count();

qb.average()#

This method will return the average of a given column. This only works for a numeric column.

const averageAge = await qb.average('age');

qb.sum()#

This method will return the summation of a given column. This only works for a numeric column.

const totalBalance = await qb.sum('balance');

qb.min()#

This method will return the minimum value of a given column.

const minimumAge = await qb.min('age');

qb.max()#

This method will return the maximum value of a given column.

const maximumBalance = await qb.max('balance');

Update#

qb.update()#

This method will update the records matching your query with the given data. The object keys will be used as column names, and their values will be set to them. This method will update all records with the same data. If you want to update multiple records with different data, you need to use bulkUpdate() method.

Update will also update the updated_at column to the current date and time. This can be disabled by passing a true to silent parameter.

new QueryBuilder('users').where('id', 10).update({
name: 'Silvie'
});

qb.bulkUpdate()#

The bulk update will be used to update multiple records with different data. This is handy when reducing the update queries in your application if you are doing multiple updates in a row.

The data parameter is the dataset to be used for the update, and it must contain the keys to search for their corresponding records.

The keys parameter is an array of key names. This will indicate which keys of the dataset should be used to find each record. The keys that will not be mentioned in this parameter will be used to update the record.

Update will also update the updated_at column to the current date and time. This can be disabled by passing a true to silent parameter.

new QueryBuilder('users').bulkUpdate(
[
{
id: 2,
name: 'Sarah',
family: 'Connor'
},
{
id: 52,
name: 'Matthew',
age: 33
},
{
id: 21,
balance: 21000
}
],
['id']
);

The above code will update the name and the family of the user 2, the name and the age of user 52 and the balance of user 21.

Delete#

qb.delete()#

This method will delete the records matching the query criteria.

await new QueryBuilder('users').where('age', '<', 18).delete();
// This will delete all underaged users

qb.useSoftDeletes()#

This method will just mark the query builder to be able to use softDelete() and restore() methods. You need to call this method before calling those methods.

qb.useSoftDeletes();

qb.softDelete()#

This method will soft delete the records matching the query criteria. Soft deleting must be enabled on the table. This can be done by adding a deleted_at timestamp field to the table. If you are going to change the name of this field, you need needs to indicate the new name in the query builder options which can be done through the extend() method. You also need to call useSoftDeletes() method before using this one.

await new QueryBuilder('users').where('id', 12).softDelete();
// This will soft delete the user with id = 12

qb.restore()#

This method will be used to restore soft deleted records. This is done by setting the soft delete field to null. Before using this method, you should enable soft deleting on your table, and on your query builder. The default soft delete column name is deleted_at which can be changed by extend(). You also need to call useSoftDeletes() before using this one.

await new QueryBuilder('users').where('id', 12).restore();
// This will restore the soft deleted user with id = 12

Insert#

qb.insert()#

This method will insert a dataset into the database table. The ignore parameter indicates weather to ignore duplicate keys or not. Otherwise, it will throw a database error if you are inserting a duplicate key.

The keys of the first object in the array will be used to form the insertion query, so you need to keep those objects integrated. The keys must be same as the first one.

  • If you have other keys that doesn't exist on the first object, they won't be used.
  • If the keys of the first object are missing from other objects it will throw an error.
new QueryBuilder('users').insert([
{
name: 'Hossein',
family: 'Maktoobian',
age: 23,
},
{
name: 'John',
family: 'Doe',
age: 45,
},
]);

Alias Table#

qb.fromAliasTable()#

If you don't want to select your data from an actual table, it is possible to specify another query builder to use it as the reference table of the query builder. The alias parameter will be an alias name for the sub query to be used as reference to the sub query columns in other query parts.

new QueryBuilder()
.fromAliasTable(
new QueryBuilder('users').select('id', 'created_at'),
'u'
)
.where('u.id', '<', 10)
.get();

Other Methods#

qb.clone()#

This method will create a fresh independent copy of the query builder instance.

const qbc = qb.clone();

qb.extend()#

This method can be used to set options on the query builder instance. Use this method if you are familiar with the options structure and their valid values. Otherwise, just use the provided helper methods of the query builder.

qb.extend({
softDeleteTimestamp: 'removed_at', // Change default soft delete column name
useTimestamps: false, // Disable using create and update timestamps
processData: (data) => { // Register a data processor
console.log(data);
return data;
}
});

The full query builder options explanation will be added to documentation later.

Condition Builders#

Condition builders are a way to create complex conditions by calling their methods. They will be transpiled into valid database query conditions using the database driver. There are different places that you need to specify conditions for.

The first one is where you are going to limit the query criteria by defining conditions on its normal fields, which you need to use Where Condition Builder.

The second case is where you want to limit the query criteria, but you want to set conditions on the aggregate fields that are going to be in your query, which you need to use Having Condition Builder.

The last case is where you are joining tables or queries, and you want to specify when two records should be joined. In that case, you can use the simple condition parameters in the join method which only checks for equality. Otherwise, you can use a Join Condition Builder to create more complex conditions on your joins.

info

The methods described in this section, will be tied together with AND operator. This can be changed by using the OR version of these methods. It just gets a or prefix, but keeps the camelCase naming convention. For example, whereBetween() method has another version named orWhereBetween(), and havingNull() method will be orHavingNull(), etc.

Where Condition Builder#

This type of conditions will be applied to the query and limits its criteria. This condition builder will be used when you want to group conditions together in another condition clause, to change their precedence. In that case, you need to pass a Function to where() or orWhere() methods which accepts a where condition builder.

tip

All of WhereConditionBuilder methods are available on the QueryBuilder class too. Since they need to be applied on a query in the first place.

The WhereConditionBuilder class can be found at silvie/database/builders/condition/where.

import WhereConditionBuilder from 'silvie/database/builders/condition/where';
const wcb = new WhereConditionBuilder();

However, you usually don't need to create an instance, the common use case of this class is through the where() or orWhere() method of the query builder class.

qb.where((wcb) => {
// Configure the condition builder
});

wcb.where()#

This method will add a condition to check a column with a value.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

You are able to pass a Function to the column parameter. This will be callback function which will get a hereConditionBuilder instance, and the queries made with that condition builder will be grouped together in the final query.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

wcb.where('username', 'hmak')
.orWhere('username', 'hmak-me')
// Restricts the 'username' field to be 'hmak' ro 'hmak-me'
wcb.where((cb) => {
cb.whereNotNull('email')
.where('email_verified', false);
}).orWhere((cb) => {
cb.whereNotNull('phone')
.where('phone_verified', false);
});
// Restricts to the users who
// have an email and not verified it
// or have a phone and not verified it
wcb.where(
new QueryBuilder('posts')
.selectCount('post_count')
.whereColumn('posts.user_id', 'users.id')
.groupBy('posts.user_id'),
'>=',
2
);
// Restricts to those users who
// submitted between 2 or more posts

wcb.whereNull()#

This method will add a condition which its operand should be null.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

wcb.whereNull('email');
// Restricts to those users who
// don't have an email

wcb.whereNotNull()#

This method will add a condition which its operand should not be null.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

wcb.whereNotNull('email');
// Restricts to those users who
// have an email address

wcb.whereBetween()#

This method will add a condition which its operand should be between two values.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The values parameter must be an array containing two TBaseValue entries, or a query builder instance which returns a single column and two rows.

wcb.whereBetween('age', [10, 20]);
// Restricts to those users aged between 10 and 20

wcb.whereNotBetween()#

This method will add a condition which its operand should not be between two values. The values parameter must be an array with two TBaseValue entries.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The values parameter must be an array containing two TBaseValue entries, or a query builder instance which returns a single column and two rows.

wcb.whereNotBetween('birthdate', ['2020-01-01', '2020-12-31']);
// Restricts to those users that
// wasn't born in 2020

wcb.whereIn()#

This method will add a condition which its operand should be present in a set of values. The values parameter must be an array of TBaseValues. This array should contain one or more entries in it.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The values parameter must be an array containing two TBaseValue entries, or a query builder instance which returns a single column.

wcb.whereIn('name', ['Mike', 'Michael', 'Micah']);
// Restricts to those users who
// their name is listed in the array
wcb.whereIn(
'id',
new QueryBuilder('premium_users')
.select('user_id')
);
// Restricts to those users who
// are listed in premium users

wcb.whereNotIn()#

This method will add a condition which its operand should not be present in a set of values. The values parameter must be an array of TBaseValues. This array should contain one or more entries in it.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The values parameter must be an array containing two TBaseValue entries, or a query builder instance which returns a single column.

wcb.whereNotIn('id', [1, 2]);
// Restricts to those users who
// their id is not 1 or 2

wcb.whereLike()#

This method will add a condition which its operand should be like a specified pattern. The pattern is the same pattern used in SQL which you can define wild cards with '%' and single unknown characters with '_'.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

wcb.whereLike('family', '%son')
.orWhereLike('family', 'Jack%');
// Restricts to those users who their family name
// starts with 'Jack' or ends with 'son'

wcb.whereNotLike()#

This method will add a condition which its operand should be like a specified pattern. The pattern is the same pattern used in SQL which you can define wild cards with '%' and single unknown characters with '_'.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

wcb.whereNotLike('family', 'S___h');
// Restricts to those users who their family name
// is not a 5 letter which start with an 'S' and ending with 'h'

wcb.whereColumn()#

This method will add a condition to compare two columns values with each other.

If you don't specify a secondColumn, The value of the operator parameter will be assumed as the second column, and the operator will be = by default.

wcb.whereColumn('family', '!=', 'name');
// Restricts to those users who their
// family is not equal to their name

wcb.whereDate()#

This method will add a condition for the date part of a date like column.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

wcb.whereDate('birthdate', '>=', '2000-01-01');
// Restricts to those uses who was born after 2000

wcb.whereYear()#

This method will add a condition for the year part of a date like column.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

wcb.whereYear('birthdate', '<', '2000');
// Restricts to those uses who was born before 2000

wcb.whereMonth()#

This method will add a condition for the month part of a date like column.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

wcb.whereMonth('birthdate', 3);
// Restricts to those uses who was born in March

wcb.whereDay()#

This method will add a condition for the day part of a date like column.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

wcb.whereDay('birthdate', 18);
// Restricts to those uses who was born
// in the 18th day of the month

wcb.whereTime()#

This method will add a condition for the time part of a date like column.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

wcb.whereTime('created_at', '>=', '00:00')
.whereTime('created_at', '<=', '06:00');
// Restricts to those uses who registered between 00:00 and 06:00

wcb.whereRaw()#

This method will add a raw query as a condition to your final query.

caution

The raw queries will be added to the final query untouched, so use it if you know what you are doing.

wcb.whereRaw('id % 7 = 0')
.orWhereRaw('id % 9 = 0');
// Restricts to those user with an id divisible by 7 or 9 :D

Having Condition Builder#

This type of conditions will be applied to the grouped queries and limits their criteria. This condition builder will be used when you want to group having conditions together in another having condition clause, to change their precedence. In that case, you need to pass a Function to having() or orHaving() methods which accepts a where condition builder.

tip

All of HavingConditionBuilder methods are available on the QueryBuilder class too. Since they need to be applied on a query in the first place.

The HavingConditionBuilder class can be found at silvie/database/builders/condition/having.

import HavingConditionBuilder from 'silvie/database/builders/condition/having';
const hcb = new HavingConditionBuilder();

Most of the times, you don't need to create an instance, the common use case of this class is through the where() or orWhere() method of the query builder class.

qb.having((hcb) => {
// Configure the condition builder
});

hcb.having()#

This method will add a condition to check a column with a value.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

You are able to pass a Function to the column parameter. This will be callback function which will get a HavingConditionBuilder instance, and the queries made with that condition builder will be grouped together in the final query.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

hcb.having('username', 'hmak')
.orHaving('username', 'hmak-me')
// Restricts the 'username' field to be 'hmak' ro 'hmak-me'
hcb.having((cb) => {
cb.havingNotNull('email')
.having('email_verified', false);
}).orHaving((cb) => {
cb.havingNotNull('phone')
.having('phone_verified', false);
});
// Restricts to the users who
// have an email and not verified it
// or have a phone and not verified it
hcb.having(
new QueryBuilder('posts')
.selectCount('post_count')
.havingColumn('posts.user_id', 'users.id')
.groupBy('posts.user_id'),
'>=',
2
);
// Restricts to those users who
// submitted between 2 or more posts

hcb.havingNull()#

This method will add a condition which its operand should be null.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

hcb.havingNull('email');
// Restricts to those users who
// don't have an email

hcb.havingNotNull()#

This method will add a condition which its operand should not be null.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

hcb.havingNotNull('email');
// Restricts to those users who
// have an email address

hcb.havingBetween()#

This method will add a condition which its operand should be between two values.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The values parameter must be an array containing two TBaseValue entries, or a query builder instance which returns a single column and two rows.

hcb.havingBetween('age', [10, 20]);
// Restricts to those users aged between 10 and 20

hcb.havingNotBetween()#

This method will add a condition which its operand should not be between two values. The values parameter must be an array with two TBaseValue entries.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The values parameter must be an array containing two TBaseValue entries, or a query builder instance which returns a single column and two rows.

hcb.havingNotBetween('birthdate', ['2020-01-01', '2020-12-31']);
// Restricts to those users that
// wasn't born in 2020

hcb.havingIn()#

This method will add a condition which its operand should be present in a set of values. The values parameter must be an array of TBaseValues. This array should contain one or more entries in it.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The values parameter must be an array containing two TBaseValue entries, or a query builder instance which returns a single column.

hcb.havingIn('name', ['Mike', 'Michael', 'Micah']);
// Restricts to those users who
// their name is listed in the array
hcb.havingIn(
'id',
new QueryBuilder('premium_users')
.select('user_id')
);
// Restricts to those users who
// are listed in premium users

hcb.havingNotIn()#

This method will add a condition which its operand should not be present in a set of values. The values parameter must be an array of TBaseValues. This array should contain one or more entries in it.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The values parameter must be an array containing two TBaseValue entries, or a query builder instance which returns a single column.

hcb.havingNotIn('id', [1, 2]);
// Restricts to those users who
// their id is not 1 or 2

hcb.havingLike()#

This method will add a condition which its operand should be like a specified pattern. The pattern is the same pattern used in SQL which you can define wild cards with '%' and single unknown characters with '_'.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

hcb.havingLike('family', '%son')
.orHavingLike('family', 'Jack%');
// Restricts to those users who their family name
// starts with 'Jack' or ends with 'son'

hcb.havingNotLike()#

This method will add a condition which its operand should be like a specified pattern. The pattern is the same pattern used in SQL which you can define wild cards with '%' and single unknown characters with '_'.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

hcb.havingNotLike('family', 'S___h');
// Restricts to those users who their family name
// is not a 5 letter which start with an 'S' and ending with 'h'

hcb.havingColumn()#

This method will add a condition to compare two columns values with each other.

If you don't specify a secondColumn, The value of the operator parameter will be assumed as the second column, and the operator will be = by default.

hcb.havingColumn('family', '!=', 'name');
// Restricts to those users who their
// family is not equal to their name

hcb.havingDate()#

This method will add a condition for the date part of a date like column.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

hcb.havingDate('birthdate', '>=', '2000-01-01');
// Restricts to those uses who was born after 2000

hcb.havingYear()#

This method will add a condition for the year part of a date like column.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

hcb.havingYear('birthdate', '<', '2000');
// Restricts to those uses who was born before 2000

hcb.havingMonth()#

This method will add a condition for the month part of a date like column.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

hcb.havingMonth('birthdate', 3);
// Restricts to those uses who was born in March

hcb.havingDay()#

This method will add a condition for the day part of a date like column.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

hcb.havingDay('birthdate', 18);
// Restricts to those uses who was born
// in the 18th day of the month

hcb.havingTime()#

This method will add a condition for the time part of a date like column.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

hcb.havingTime('created_at', '>=', '00:00')
.havingTime('created_at', '<=', '06:00');
// Restricts to those uses who registered between 00:00 and 06:00

hcb.havingRaw()#

This method will add a raw query as a condition to your final query.

caution

The raw queries will be added to the final query untouched, so use it if you know what you are doing.

hcb.havingRaw('id % 7 = 0')
.orHavingRaw('id % 9 = 0');
// Restricts to those user with an id divisible by 7 or 9

Join Condition Builder#

This type of condition builder will be used when you want to set a complex condition on a join method. Usually, there will be a column equality comparison for a join method to work, but if you want more than a single condition, or it is more complicated, just give your join() method a function and do that with an instance of join condition builder which will be passed to that function as its only parameter.

The JoinConditionBuilder class can be found at silvie/database/builders/condition/join.

import JoinConditionBuilder from 'silvie/database/builders/condition/join';
const jcb = new JoinConditionBuilder();

There are almost no cases that you might need to create an instance of this class on your own. You just need to use the instance provided to the join method callback function in a query builder, in case you want to write complex joining conditions.

qb.join('posts', (jcb) => {
// Conficure the condition builder
});

jcb.on()#

This method will add a condition to check a column with a value.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

You are able to pass a Function to the column parameter. This will be callback function which will get a JoinConditionBuilder instance, and the queries made with that condition builder will be grouped together in the final query.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

jcb.onColumn('users.id', 'posts.user_id')
.on('posts.published', true);
// Only joining the published posts with their users
jcb.onColumn('users.id', 'posts.user_id')
.on((cb) => {
cb.on('posts.published', true)
.orOn('posts.verified', true)
});
// Only joining the published and verified posts

jcb.onNull()#

This method will add a condition which its operand should be null.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

jcb.onColumn('users.id', 'posts.user_id')
.onNull('posts.thumbnail');
// Only joining posts without a thumbnail

jcb.onNotNull()#

This method will add a condition which its operand should not be null.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

jcb.onColumn('users.id', 'posts.user_id')
.onNotNull('posts.translator_name');
// Only joining posts with a translator name

jcb.onBetween()#

This method will add a condition which its operand should be between two values.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The values parameter must be an array containing two TBaseValue entries, or a query builder instance which returns a single column and two rows.

jcb.onColumn('users.id', 'posts.user_id')
.onBetween('posts.created_at', ['2019-01-01', '2020-12-31']);
// Only joining posts that was
// posted in '2019 Jan 1' and '2020 Dec 31'

jcb.onNotBetween()#

This method will add a condition which its operand should not be between two values. The values parameter must be an array with two TBaseValue entries.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The values parameter must be an array containing two TBaseValue entries, or a query builder instance which returns a single column and two rows.

jcb.onColumn('users.id', 'posts.user_id')
.onNotBetween('posts.review', [2, 7]);
// Only joining posts that was not
// reviewed 2 to 7 times

jcb.onIn()#

This method will add a condition which its operand should be present in a set of values. The values parameter must be an array of TBaseValues. This array should contain one or more entries in it.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The values parameter must be an array containing two TBaseValue entries, or a query builder instance which returns a single column.

jcb.onColumn('users.id', 'posts.user_id')
.onIn('posts.category', ['Science', 'Technology', 'Art']);
// Only joining posts from
// 'Science', 'Technology' and 'Art' categories

jcb.onNotIn()#

This method will add a condition which its operand should not be present in a set of values. The values parameter must be an array of TBaseValues. This array should contain one or more entries in it.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The values parameter must be an array containing two TBaseValue entries, or a query builder instance which returns a single column.

jcb.onColumn('users.id', 'posts.user_id')
.onNotIn('posts.user_id', [1, 2]);
// Only joining posts that was not
// from users with 1 or 2 id

jcb.onLike()#

This method will add a condition which its operand should be like a specified pattern. The pattern is the same pattern used in SQL which you can define wild cards with '%' and single unknown characters with '_'.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

jcb.onColumn('users.id', 'posts.user_id')
.onLike('posts.title', 'Breaking News:%');
// Only joining posts that their
// title starts with 'Breaking News:'

jcb.onNotLike()#

This method will add a condition which its operand should be like a specified pattern. The pattern is the same pattern used in SQL which you can define wild cards with '%' and single unknown characters with '_'.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

jcb.onColumn('users.id', 'posts.user_id')
.onNotLike('posts.title', '[DRAFT]%');
// Only joining posts that their
// title does not start with '[DRAFT]' tag

jcb.onColumn()#

This method will add a condition to compare two columns values with each other.

If you don't specify a secondColumn, The value of the operator parameter will be assumed as the second column, and the operator will be = by default.

jcb.onColumn('users.id', 'posts.user_id')
.onColumn('posts.thumbnail', 'users.picture');
// Only joining those posts that their thumbnail
// is their user profile picture

jcb.onDate()#

This method will add a condition for the date part of a date like column.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

jcb.onColumn('users.id', 'posts.user_id')
.onDate('posts.published_at', '2020-02-02');
// Only joining posts that was posted on '2020-02-02'

jcb.onYear()#

This method will add a condition for the year part of a date like column.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

jcb.onColumn('users.id', 'posts.user_id')
.onYear('posts.published_at', '>=', '2018');
// Only joining posts that was posted after 2018

jcb.onMonth()#

This method will add a condition for the month part of a date like column.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

jcb.onColumn('users.id', 'posts.user_id')
.onMonth('posts.published_at', '12');
// Only joining posts that was posted in December

jcb.onDay()#

This method will add a condition for the day part of a date like column.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

jcb.onColumn('users.id', 'posts.user_id')
.onDay('posts.published_at', '22');
// Only joining posts that was posted on 22th day of month

jcb.onTime()#

This method will add a condition for the time part of a date like column.

The column parameter can be string value indicating a column name, or a query builder instance which returns a single column in a single row, and will be treated as a sub select in the condition.

The operator parameter can either be a TOperator indicating the operator of condition, a TBaseValue or a QueryBuilder instance. However, this depends on the next parameter.

The value parameter should be a TBaseValue or a QueryBuilder instance. In case that you pass a query builder as the value, it will be treated as a sub select in the condition clause.

If you don't specify the value parameter, the value of operator parameter will be assumed as the value, and the operator will be = by default.

jcb.onColumn('users.id', 'posts.user_id')
.onTime('posts.published_at', '<', '10:30');
// Only joining posts that was posted before 10:30 AM

jcb.onRaw()#

This method will add a raw query as a condition to your final query.

caution

The raw queries will be added to the final query untouched, so use it if you know what you are doing.

jcb.onRaw(
"`users`.`id` = `posts`.`user_id` AND UPPER(`users`.`name`) = 'JOE'"
);

Types#

TBaseValue#

Values of this type can be one these:

TOperator#

Values of this type are strings matching the following list:

  • =
  • !=
  • >
  • >=
  • <
  • <=