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 "*".

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

SQL (MySQL)
SELECT `fname` AS `firstName`, `age` FROM `users`

distinct

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

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

SQL (MySQL)
SELECT DISTINCT `username` FROM `users`

addSelect

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

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

SQL (MySQL)
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.)

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

SQL (MySQL)
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.

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

SQL (MySQL)
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.

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

SQL (MySQL)
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.

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

SQL (MySQL)
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.

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

SQL (MySQL)
SELECT YEAR(birthdate) AS birth_year FROM `users`