From

Queries come in many varieties—from the basic to extremely complex. In order to provide you maximum flexibility there are several ways to define the source table for you query.

Using the from() method

The most common method for defining the source table is using the from() method. For the majority of queries, the from() method is all you need. It's syntax is very easy:

//qb
var getResults = query.from('users').get();

//sql
SELECT * FROM `users`

This would return all columns from the users table.

NOTE: Alternatively, you can use the table() method as an alias to from().

Declaring a table alias

Optionally you can specify an alias for the table by using the syntax:

//qb
var getResults = query.from('users as u').get();

//sql
SELECT * FROM `users` AS `u`

This would parse the string users as u and convert it into the correct syntax for current grammar.

Alternatively, you can use the ANSI SQL shorthand and leave out the as keyword:

//qb
var getResults = query.from('users u').get();

//sql
SELECT * FROM `users` AS `u`

Defining the from clause using raw SQL

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.

If you need complete control over your from clause you can use the fromRaw().

For example, to provide a table hint for a SQL Server query you could use:

//qb
var getResults = query.fromRaw('[users] u (nolock)').get();

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

NOTE: Using the fromRaw() will most likely tie your code to a specific database, so think carefully before using the fromRaw() method if you want your project to be database agnostic.

Adding bindings to your raw from clause

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:

//qb
var getResults = query
    .fromRaw('dbo.generateDateTable(?, ?, ?) as dt', ['2017-01-01', '2017-12-31', 'm'])
    .get()
;

//sql
SELECT * FROM dbo.generateDateTable('2017-01-01', '2017-12-31', 'm') as dt

Derived tables

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

You can build queries that comprise of derived tables by using the fromSub() method, which requires two arguments:

• The alias to use for the derived table (which is how you reference your query)

• Either a QueryBuilder instances or closure defining the subquery

Defining a derived table using a closure

The simplest way to create a derived table is by using a closure to define the subquery:

//qb
var getResults = query
    .select('firstName', 'lastName')
    .fromSub('u', function (q){
        q
            .select('lName as lastName', 'fName as firstName')
            .from('users')
            .where('age', '>=', 21)
        ;
    })
    .orderBy('lastName')
    .get()
;

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

Defining a derived table using a QueryBuilder instance

Alternatively you can supply a QueryBuilder instance to the fromSub() method:

//qb
var derivedQA = query
    .select('lName as lastName', 'fName as firstName')
    .from('users')
    .where('age', '>=', 21)
;

var getResults = query
    .select('firstName', 'lastName')
    .fromSub('u', derivedQA)
    .orderBy('lastName')
    .get()
;

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