Wheres
Table of Contents | | |
|
Name | Type | Required | Default | Description |
column | true | | The name of the column or Expression with which to constrain the query. A function can be passed to begin a nested where statement. | |
operator | 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. If a QueryBuilder or Function is passed, it will be used as a subselect expression. |
combinator | string | false | "and" |
Adds a where clause to a query.
QueryBuilder
query.from( "users" )
.where( "active", "=", 1 );
MySQL
SELECT *
FROM `users`
WHERE `active` = ?
Using the
where
method will parameterize the value
passed. If you want to constrain a column to another column, use the whereColumn
method.QueryBuilder
query.from( "users" )
.where( "last_logged_in", ">", query.raw( "NOW()" ) );
MySQL
SELECT *
FROM `users`
WHERE `last_logged_in` > NOW()
Any of the following operators can be used in a where clause.
Valid Operators | | |
= | < | > |
<= | >= | <> |
!= | like | like binary |
not like | between | ilike |
& | | | ^ |
<< | >> | rlike |
regexp | not regexp | ~ |
~* | !~ | !~* |
similar to | not similar to | |
When using the
"="
constraint, you can use a shortcut and define the value as the second argument.QueryBuilder
query.from( "users" )
.where( "active", 1 );
MySQL
SELECT *
FROM `users`
WHERE `active` = ?
To group where statements together, pass a function to the where clause as the only parameter.
QueryBuilder
query.from( "users" )
.where( function( q ) {
q.where( "active", 1 )
.where( "last_logged_in", ">", dateAdd( "ww", -1, now() ) )
} );
MySQL
SELECT *
FROM `users`
WHERE (
`active` = ?
AND
`last_logged_in` > ?
)
This grouping can be nested as many levels as you require.
A Function or QueryBuilder can be used as a subselect expression when passed to
value
.QueryBuilder
query.from( "users" )
.where( "email", "foo" )
.orWhere( "id", "=", function( q ) {
q.select( q.raw( "MAX(id)" ) )
.from( "users" )
.where( "email", "bar" );
} );
MySQL
SELECT *
FROM `users`
WHERE `email` = ?
OR `id` = (
SELECT MAX(id)
FROM `users`
WHERE `email` = ?
)
Name | Type | Required | Default | Description |
column | true | | The name of the column or Expression with which to constrain the query. A function can be passed to begin a nested where statement. | |
operator | 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. If a QueryBuilder or Function is passed, it will be used as a subselect expression. |
Name | Type | Required | Default | Description |
column | true | | The name of the column or Expression with which to constrain the query. A function can be passed to begin a nested where statement. | |
operator | 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. If a QueryBuilder or Function is passed, it will be used as a subselect expression. |
Name | Type | Required | Default | Description |
column | string | Expression | true | | |
start | any | Function | QueryBuilder | true | | The beginning value of the BETWEEN statement. If a function or QueryBuilder is passed it is used as a subselect expression. |
end | any | Function | QueryBuilder | true | | The end value of the BETWEEN statement. If a function or QueryBuilder is passed it is used as a subselect expression. |
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 andWhere and orWhere dynamic methods instead. |
negate | boolean | false | false | False for BETWEEN, True for NOT BETWEEN. |
Adds a where between clause to the query.
QueryBuilder
query.from( "users" )
.whereBetween( "id", 1, 2 );
MySQL
SELECT *
FROM `users`
WHERE `id` BETWEEN ? AND ?
If a function or QueryBuilder is passed it is used as a subselect expression.
QueryBuilder
query.from( "users" )
.whereBetween(
"id",
function( q ) {
q.select( q.raw( "MIN(id)" ) )
.from( "users" )
.where( "email", "bar" );
},
builder.newQuery()
.select( builder.raw( "MAX(id)" ) )
.from( "users" )
.where( "email", "bar" )
);
MySQL
SELECT *
FROM `users`
WHERE `id` BETWEEN (
SELECT MIN(id)
FROM `users`
WHERE `email` = ?
)
AND (
SELECT MAX(id)
FROM `users`
WHERE `email` = ?
)
Name | Type | Required | Default | Description |
column | string | Expression | true | | |
start | any | Function | QueryBuilder | true | | The beginning value of the BETWEEN statement. If a function or QueryBuilder is passed it is used as a subselect expression. |
end | any | Function | QueryBuilder | true | | The end value of the BETWEEN statement. If a function or QueryBuilder is passed it is used as a subselect expression. |
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 andWhere and orWhere dynamic methods instead. |
Adds a where not in clause to the query. This behaves identically to the
whereBetween
method with the negate
flag set to true
. See the documentation for whereBetween
for usage and examples.Name | Type | Required | Default | Description |
first | true | | ||
operator | true | | 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 ) ). | |
second | string | Expression | false | | |
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 andWhere and orWhere dynamic methods instead. |
Adds a where clause to a query that compares two columns.
QueryBuilder
query.from( "users" )
.whereColumn( "first_name", "=", "last_name" );
MySQL
SELECT *
FROM `users`
WHERE `first_name` = `last_name`
Just as with
where
, when using "="
as the operator you can use a shorthand passing the second column in as the operator and leaving the second column null
.QueryBuilder
query.from( "users" )
.whereColumn( "first_name", "last_name" );
MySQL
SELECT *
FROM `users`
WHERE `first_name` = `last_name`
Expressions
can be passed in place of either column.QueryBuilder
query.from( "users" )
.whereColumn( "first_name", query.raw( "LOWER(first_name)" ) );
MySQL
SELECT *
FROM `users`
WHERE `first_name` = LOWER(first_name)
Name | Type | Required | Default | Description |
query | Function | QueryBuilder | true | | A function or QueryBuilder instance to be used as the exists subquery. |
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 andWhere and orWhere dynamic methods instead. |
negate | boolean | false | false | False for EXISTS, True for NOT EXISTS. |
Adds a where exists clause to the query.
It can be configured with a function.
QueryBuilder
query.from( "orders" )
.whereExists( function( q ) {
q.select( q.raw( 1 ) )
.from( "products" )
.whereColumn( "products.id", "orders.id" );
} );
MySQL
SELECT *
FROM `orders`
WHERE EXISTS (
SELECT 1
FROM `products`
WHERE `products`.`id` = `orders`.`id`
)
It can also be configured with a QueryBuilder instance.
QueryBuilder
var existsQuery = query.newQuery()
.select( q.raw( 1 ) )
.from( "products" )
.whereColumn( "products.id", "orders.id" );
query.from( "orders" )
.whereExists( existsQuery );
MySQL
SELECT *
FROM `orders`
WHERE EXISTS (
SELECT 1
FROM `products`
WHERE `products`.`id` = `orders`.`id`
)
Name | Type | Required | Default | Description |
query | Function | QueryBuilder | true | | A function or QueryBuilder instance to be used as the not exists subquery. |
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 andWhere and orWhere dynamic methods instead. |
Adds a where not in clause to the query. This behaves identically to the
whereExists
method with the negate
flag set to true
. See the documentation for whereExists
for usage and examples.Name | Type | Required | Default | Description |
column | true | | ||
value | any | false | | The value with which to constrain the column. An Expression can be passed as well. If a QueryBuilder or Function is passed, it will be used as a subselect expression. |
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 andWhere and orWhere dynamic methods instead. |
QueryBuilder
query.from( "users" )
.whereLike( "username", "J%" );
MySQL
SELECT *
FROM `users`
WHERE `username` LIKE ?
Name | Type | Required | Default | Description |
column | true | | ||
value | any | false | | The value with which to constrain the column. An Expression can be passed as well. If a QueryBuilder or Function is passed, it will be used as a subselect expression. |
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 andWhere and orWhere dynamic methods instead. |
QueryBuilder
query.from( "users" )
.whereNotLike( "username", "J%" );
MySQL
SELECT *
FROM `users`
WHERE `username` NOT LIKE ?
Name | Type | Required | Default | Description |
column | string | Expression | true | | |
values | true | | A single value, list of values, or array of values to constrain a column with. Expressions may be used in any place a value is used. Alternatively, a function or QueryBuilder instance can be passed in to be used as a subquery expression. | |
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 andWhere and orWhere dynamic methods instead. |
negate | boolean | false | false | False for IN, True for NOT IN. |
Adds a where in clause to the query.
The values passed to
whereIn
can be a single value, a list of values, or an array of values.QueryBuilder
query.from( "orders" )
.whereIn( "id", [ 1, 4, 66 ] );
MySQL
SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)
Some database grammars have a hard limit on the number of parameters passed to a SQL statement. Keep this in mind while writing your queries.
If a list of values is passed in, it is converted to an array of values using a single comma (
","
) delimiter.QueryBuilder
query.from( "orders" )
.whereIn( "id", "1,4,66" );
MySQL
SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)
Any value in the list or array can also be passed using a custom parameter type to have more control over the parameter settings.
QueryBuilder
query.from( "orders" )
.whereIn( "id", [ 1, 4, { value = "66", cfsqltype = "CF_SQL_VARCHAR" } ] );
MySQL
SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)
Expressions
can be freely mixed in with other values.QueryBuilder
query.from( "orders" )
.whereIn( "id", [ query.raw( "MAX(id)" ), 4, 66 ] );
MySQL
SELECT *
FROM `orders`
WHERE `id` IN (MAX(id), ?, ?)
A function or QueryBuilder instance can be passed to be used as a subquery expression instead of a list of values.
QueryBuilder
query.from( "users" )
.whereIn( "id", function( q ) {
q.select( "id" )
.from( "users" )
.where( "age", ">", 25 );
} );
MySQL
SELECT *
FROM `users`
WHERE IN (
SELECT `id`
FROM `users`
WHERE `age` > ?
)
You may find a
whereExists
method performs better for you than a whereIn
with a subquery.Name | Type | Required | Default | Description |
column | string | Expression | true | | |
values | true | | A single value, list of values, or array of values to constrain a column with. Expressions may be used in any place a value is used. Alternatively, a function or QueryBuilder instance can be passed in to be used as a subquery expression. | |
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 andWhere and orWhere dynamic methods instead. |
Adds a where not in clause to the query. This behaves identically to the
whereIn
method with the negate
flag set to true
. See the documentation for whereIn
for usage and examples.Name | Type | Required | Default | Description |
sql | string | true | | The raw SQL to add to the query. |
whereBindings | array | false | [] | |
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 andWhere < |