Selects
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.Name | Type | Required | Default | Description |
columns | string | array | false | "*" | A single column, list of columns, or array of columns to retrieve. |
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`
Name | Type | Required | Default | Description |
state | boolean | false | true | Value to set the distinct flag. |
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`
distinct
applies to the entire query, not just certain fields.Name | Type | Required | Default | Description |
columns | string | array | true | | A single column, list of columns, or array of columns to add to the select. |
This method adds the columns passed to it to the currently selected columns.
If the
QueryBuilder
is currently selecting all columns ("*"
) when this method is called, the incoming columns will becoming the only columns selected.QueryBuilder
query.addSelect( [ "fname AS firstName", "age" ] ).from( "users" );
SQL (MySQL)
SELECT `fname` AS `firstName`, `age` FROM `users`
Name | Type | Required | Default | Description |
expression | any | true | | The raw expression for the select statement. |
bindings | array | false | [] | Any bindings needed for the raw expression. |
A shortcut to use a raw expression in the select clause.
The expression is added to the other already selected columns.
QueryBuilder
query.selectRaw( "YEAR(birthdate) AS birth_year" ).from( "users" );
SQL (MySQL)
SELECT YEAR(birthdate) AS birth_year FROM `users`
Name | Type | Required | Default | Description |
alias | string | true | | The alias for the subselect expression. |
query | Function | QueryBuilder | true | | The callback or query to use in the 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
Name | Type | Required | Default | Description |
No arguments | | `` | | |
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`
Name | Type | Required | Default | Description |
columns | string | array | false | "*" | A single column, list of columns, or array of columns to retrieve. |
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`
Name | Type | Required | Default | Description |
expression | any | true | | The raw expression for the select statement. |
bindings | array | false | [] | Any bindings needed for the raw expression. |
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`
Last modified 4mo ago