Wheres

Table of Contents
​
​
​where​
​andWhere​
​orWhere​
​whereBetween​
​whereNotBetween​
​whereColumn​
​whereExists​
​whereNotExists​
​whereLike​
​whereIn​
​whereNotIn​
​whereRaw​
​whereNull​
​whereNotNull​
​
Where Methods
where
Name
Type
Required
Default
Description
column
string | Expression | Function
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
string | Expression​
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"
The boolean combinator for the clause.  Valid options are "and" or "or".  Avoid passing this parameter explicitly.  Where possible use the andWhere and orWhere methods instead.
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.
You can also pass an Expression as the value.
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` = ?
You may also use dynamic where{Column} statements to simplify this further.
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` = ?
  )
andWhere
Name
Type
Required
Default
Description
column
string | Expression | Function
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
string | Expression​
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.
This method is simply an alias for where with the combinator set to "and".
orWhere
Name
Type
Required
Default
Description
column
string | Expression | Function
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
string | Expression​
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.
This method is simply an alias for where with the combinator set to "or".
whereBetween
Name
Type
Required
Default
Description
column
string | Expression
true
​
The name of the column or Expression with which to constrain the query.
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` = ?
)
whereNotBetween
Name
Type
Required
Default
Description
column
string | Expression
true
​
The name of the column or Expression with which to constrain the query.
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 negateflag set to true.  See the documentation for whereBetween for usage and examples.
whereColumn
Name
Type
Required
Default
Description
first
string | Expression​
true
​
The name of the first column or Expression with which to constrain the query.
operator
string | Expression​
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
​
The name of the second column or Expression with which to constrain the query.
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)
whereExists
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`
)
whereNotExists
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 negateflag set to true.  See the documentation for whereExists for usage and examples.
whereLike
Name
Type
Required
Default
Description
column
string | Expression​
true
​
The name of the column or Expression with which to constrain the query.
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.
A shortcut for calling where with "like" set as the operator.
QueryBuilder
query.from( "users" )
    .whereLike( "username", "J%" );
MySQL
SELECT *
FROM `users`
WHERE `username` LIKE ?
whereNotLike
Name
Type
Required
Default
Description
column
string | Expression​
true
​
The name of the column or Expression with which to constrain the query.
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.
A shortcut for calling where with "not like" set as the operator.
QueryBuilder
query.from( "users" )
    .whereNotLike( "username", "J%" );
MySQL
SELECT *
FROM `users`
WHERE `username` NOT LIKE ?
whereIn
Name
Type
Required
Default
Description
column
string | Expression
true
​
The name of the column or Expression with which to constrain the query.
values
string | array | Expression | Function | QueryBuilder
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.
whereNotIn
Name
Type
Required
Default
Description
column
string | Expression
true
​
The name of the column or Expression with which to constrain the query.
values
string | array | Expression | Function | QueryBuilder
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 negateflag set to true.  See the documentation for whereIn for usage and examples.
whereRaw
Name
Type
Required
Default
Description
sql
string
true
​
The raw SQL to add to the query.
whereBindings
array
false
[]
Any bindings needed for the raw SQL.  Bindings can be simple values or custom parameters.
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<
Table of Contents
where	andWhere	orWhere
whereBetween	whereNotBetween	whereColumn
whereExists	whereNotExists	whereLike
whereIn	whereNotIn	whereRaw
whereNull	whereNotNull
Name	Type	Required	Default	Description
column	string \| Expression \| Function	`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	string \| Expression	`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"`	The boolean combinator for the clause. Valid options are `"and"` or `"or"`. Avoid passing this parameter explicitly. Where possible use the `andWhere` and `orWhere` methods instead.
Valid Operators
=	<	>
<=	>=	<>
!=	like	like binary
not like	between	ilike
&	\|	^
<<	>>	rlike
regexp	not regexp	~
~*	!~	!~*
similar to	not similar to