1 of 14

Building Queries

Selects

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

distinct

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

distinct applies to the entire query, not just certain fields.

addSelect

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.

selectRaw

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

The expression is added to the other already selected columns.

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.

clearSelect

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

reselect

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.

Group By and Having

groupBy

Name

Type

Required

Default

Description

groups

string | array

true

A single column name, a list of column names, or an array of column names to group by. An Expression can be passed as well.

Passing a single string will group by that one column.

QueryBuilder

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

MySQL

SELECT *
FROM `users`
GROUP BY `country`

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

QueryBuilder

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

MySQL

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

An array of column names can be provided.

QueryBuilder

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

MySQL

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

Calling groupBy multiple times will to the current groups.

QueryBuilder

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

MySQL

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

An Expression can be passed in place of a column.

QueryBuilder

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

MySQL

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

having

Name

Type

Required

Default

Description

column

string | Expression

true

The name of the column or Expression with which to constrain the query.

operator

any

false

The operator to use for the constraint (i.e. "=", "<", ">=", etc.). A value can be passed as the operator and the value left null as a shortcut for equals (e.g. where( "column", 1 ) == where( "column", "=", 1 ) ).

value

any

false

The value with which to constrain the column. An Expression can be passed as well.

combinator

string

false

"and"

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the andHaving and orHaving methods instead.

Adds a having clause to a query.

QueryBuilder

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

MySQL

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

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

QueryBuilder

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

MySQL

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