Specifying A Select Clause

You may not always want to select all columns from a database table. You can influence the select list of a query with the following methods.

Individual columns can contain fully-qualified names (some_table.some_column), table aliases (alias.some_column), and even set column aliases themselves (some_column AS c). The columns argument can be a single column, a list of columns (comma-separated), or an array of columns.

select

When calling select any previous columns are discarded. If you want to incrementally select columns, use the addSelect method.

If you pass no columns to this method, it will default to "*".

query.select( [ "fname AS firstName", "age" ] ).from( "users" );

SELECT `fname` AS `firstName`, `age` FROM `users`

distinct

Calling distinct will cause the query to be executed with the DISTINCT keyword.

query.select( "username" ).distinct().from( "users" );

SELECT DISTINCT `username` FROM `users`

addSelect

This method adds the columns passed to it to the currently selected columns.

query.addSelect( [ "fname AS firstName", "age" ] ).from( "users" );

SELECT `fname` AS `firstName`, `age` FROM `users`

selectRaw

A shortcut to use a raw expression in the select clause.

The expression is added to the other already selected columns.

(To learn more about raw and expressions, check out the docs on Raw Expressions.)

query.selectRaw( "YEAR(birthdate) AS birth_year" ).from( "users" );

SELECT YEAR(birthdate) AS birth_year FROM `users`

subSelect

The method lets you pass either a callback or a QueryBuilder instance to be used as a subselect expression. If a callback is passed it will be passed a new query instance as the only parameter.

The subselect is added to the other already selected columns.

query.subSelect( "last_login_date", function( q ) {
    q.selectRaw( "MAX(created_date)" )
        .from( "logins" )
        .whereColumn( "users.id", "logins.user_id" );
} ) ).from( "users" );

SELECT (
    SELECT MAX(created_date)
    FROM `logins`
    WHERE `users`.`id` = `logins`.`user_id`
) AS `last_login_date`
FROM `users

clearSelect

Clears out the selected columns for a query along with any configured select bindings.

query.from( "users" )
    .select( [ "fname AS firstName", "age" ] )
    .clearSelect();

SELECT * FROM `users`

reselect

Clears out the selected columns for a query along with any configured select bindings. Then sets a selection of columns to select from the query. Any valid argument to select can be passed here.

query.from( "users" )
    .select( [ "fname AS firstName", "age" ] )
    .reselect( "username" );

SELECT `username` FROM `users`

reselectRaw

Clears out the selected columns for a query along with any configured select bindings. Then adds an Expression or array of expressions to the already selected columns.

query.from( "users" )
    .select( [ "fname AS firstName", "age" ] )
    .reselectRaw( "YEAR(birthdate) AS birth_year" );

SELECT YEAR(birthdate) AS birth_year FROM `users`

from

Used to set the base table for the query.

query.from( "users" );

SELECT * FROM `users`

You can optionally specify an alias for the table.

query.from( "users as u" );

SELECT * FROM `users` AS `u`

table

An alias for from where you like how calling table looks.

query.table( "users" ).insert( { "name" = "jon" } );

INSERT INTO `users` (`name`) VALUES (?)

fromRaw

Sometimes you need more control over your from clause in order to add grammar specific instructions, such as adding SQL Server table hints to your queries.

query.fromRaw( "[users] u (nolock)" ).get();

SELECT * FROM [users] u (nolock) 

Since the fromRaw() takes your string verbatim, it's important that you make sure your SQL declaration is escaped properly. Failure to properly escape your table names may result in SQL errors.

Many database engines allow you to define User Defined Functions. For example, SQL Server allows you to define UDFs that will return a table. In these type of cases, it may be necessary to bind parameters to your from clause.

You can bind parameters to the fromRaw() method by passing a secondary argument that is an array of the parameters to bind.

query.fromRaw(
    "dbo.generateDateTable(?, ?, ?) as dt",
    [ "2017-01-01", "2017-12-31", "m" ]
).get();

SELECT * FROM dbo.generateDateTable(?, ?, ?) as dt

fromSub

Complex queries often contain derived tables. Derived tables are essentially a temporal table defined as a subquery in the from statement.

query.select( [ "firstName", "lastName" ] )
    .fromSub( "legalUsers", function ( q ) {
        q.select( [ "lName as lastName", "fName as firstName" ] )
            .from( "users" )
            .where( "age", ">=", 21 )
        ;
    } )
    .orderBy( "lastName" )
    .get()

SELECT `firstName`, `lastName`
FROM (
    SELECT `lName` as `lastName`, `fName` as `firstName`
    FROM `users`
    WHERE `age` >= 21
) AS `legalUsers`
ORDER BY `lastName`

In additional a function callback, a separate QueryBuilder instance can be passed to the fromSub method.

var legalUsersQuery = query
    .select( [ "lName as lastName", "fName as firstName" ] )
    .from( "users" )
    .where( "age", ">=", 21 );

query.select( [ "firstName", "lastName" ] )
    .fromSub( "legalUsers", legalUsersQuery )
    .orderBy( "lastName" )
    .get();

SELECT `firstName`, `lastName`
FROM (
    SELECT `lName` as `lastName`, `fName` as `firstName`
    FROM `users`
    WHERE `age` >= 21
) AS `legalUsers`
ORDER BY `lastName`

withAlias

Adds an alias to the specified from table or renames a current alias. Any existing aliased values in columns, wheres, joins, groupBys, or orders that match the previous alias will be remapped to the new alias. This includes the full table name when used as an alias.

qb.from( "users" ).select( [ "users.name", "birthdate" ] );
// SELECT "users"."name", "birthdate" FROM "users"
qb.withAlias( "u1" );
// SELECT "u1"."name", "birthdate" FROM "users" AS "u1"

The orderBy method seems simple but has a lot of depth depending on the type of arguments you pass in.

Order By (String)

query.from( "users" )
    .orderBy( "email" );

SELECT *
FROM `users`
ORDER BY `email` ASC

Calling orderBy multiple times will append to the order list.

query.from( "users" )
    .orderBy( "email" )
    .orderBy( "username", "desc" );

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

query.from( "users" )
    .orderBy( query.raw( "DATE(created_at)" ) );

SELECT *
FROM `users`
ORDER BY DATE(created_at)

Order By (List)

query.from( "users" )
    .orderBy( "email|asc,username", "desc" );

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Array of Strings)

Order By (Array of Structs)

Order By (Subquery)

You can order with a subquery using either a function or a QueryBuilder instance.

Order By Raw

clearOrders

reorder

groupBy

Passing a single string will group by that one column.

query.from( "users" )
    .groupBy( "country" );

SELECT *
FROM `users`
GROUP BY `country`

You can also pass a list of column names. A single comma (",") will be used as the delimiter.

query.from( "users" )
    .groupBy( "country,city" );

SELECT *
FROM `users`
GROUP BY `country`, `city`

An array of column names can be provided.

query.from( "users" )
    .groupBy( [ "country", "city" ] );

SELECT *
FROM `users`
GROUP BY `country`, `city`

Calling groupBy multiple times will to the current groups.

query.from( "users" )
    .groupBy( "country" )
    .groupBy( "city" );

SELECT *
FROM `users`
GROUP BY `country`, `city`

An Expression can be passed in place of a column.

query.from( "users" )
    .groupBy( query.raw( "DATE(created_at)" ) );

SELECT *
FROM `users`
GROUP BY DATE(created_at)

having

Adds a having clause to a query.

query.from( "users" )
    .groupBy( "email" )
    .having( "email", ">", 1 );

SELECT *
FROM `users`
GROUP BY `email`
HAVING `email` > ?

Expressions can be used in place of the column or the value.

query.from( "users" )
    .groupBy( "email" )
    .having( query.raw( "COUNT(email)" ), ">", 1 );

SELECT *
FROM `users`
GROUP BY `email`
HAVING COUNT(email) > ?

qb includes a few methods to help you lock certain rows when executing select statements.

sharedLock

A shared lock prevents the selected rows from being modified until your transaction is committed.

query.from( "users" )
    .where( "id", 1 )
    .sharedLock();

SELECT *
FROM `users`
WHERE `id` = ?
LOCK IN SHARE MODE

SELECT *
FROM [users] WITH (ROWLOCK,HOLDLOCK)
WHERE [id] = ?

SELECT *
FROM "users"
WHERE "id" = ?
FOR SHARE

LOCK TABLE "USERS"
IN SHARE MODE NOWAIT;

SELECT *
FROM "USERS"
WHERE "ID" = ?

lockForUpdate

A lock for update lock prevents the selected rows from being modified or selected with another shared lock until your transaction is committed.

The main difference between a sharedLock and lockForUpdate is that a lockForUpdate prevents other reads or selects as well as updates.

query.from( "users" )
    .where( "id", 1 )
    .lockForUpdate();

SELECT *
FROM `users`
WHERE `id` = ?
FOR UPDATE

SELECT *
FROM [users] WITH (ROWLOCK,UPDLOCK,HOLDLOCK)
WHERE [id] = ?

SELECT *
FROM "users"
WHERE "id" = ?
FOR UPDATE

SELECT *
FROM "USERS"
WHERE "ID" = ?
FOR UPDATE

When using the skipLocked flag, the query will skip over locked records and only return and lock available records.

query.from( "users" )
    .where( "id", 1 )
    .lockForUpdate( skipLocked = true )
    .orderBy( "id" )
    .limit( 5 );

SELECT *
FROM `users`
WHERE `id` = ?
ORDER BY `id`
LIMIT 5
FOR UPDATE SKIP LOCKED

SELECT TOP 5 *
FROM [users] WITH (ROWLOCK,UPDLOCK,HOLDLOCK,READPAST)
WHERE [id] = ?
ORDER BY [id]

SELECT *
FROM "users"
WHERE "id" = ?
ORDER BY "id"
LIMIT 1
FOR UPDATE SKIP LOCKED

noLock

noLock will instruct your grammar to ignore any shared locks when executing the query.

Currently this only makes a difference in SQL Server grammars.

query.from( "users" )
    .where( "id", 1 )
    .noLock();

SELECT *
FROM [users] WITH (NOLOCK)
WHERE [id] = ?

lock

The lock method will allow you to add a custom lock directive to your query. Think of it as the raw method for lock directives.

These lock directives vary from grammar to grammar.

clearLock

Clears any lock directive on the query.