Wheres
Table of Contents | ||
Where Methods
where
Name | Type | Required | Default | Description |
column |
| |||
operator |
| The operator to use for the constraint (i.e. "=", "<", ">=", etc.). A value can be passed as the | ||
value | any |
| ||
combinator | string |
|
|
Adds a where clause to a query.
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.
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.
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.
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
.
andWhere
Name | Type | Required | Default | Description |
column |
| |||
operator |
| The operator to use for the constraint (i.e. "=", "<", ">=", etc.). A value can be passed as the | ||
value | any |
|
This method is simply an alias for where
with the combinator set to "and"
.
orWhere
Name | Type | Required | Default | Description |
column |
| |||
operator |
| The operator to use for the constraint (i.e. "=", "<", ">=", etc.). A value can be passed as the | ||
value | any |
|
This method is simply an alias for where
with the combinator set to "or"
.
whereBetween
Name | Type | Required | Default | Description |
column | string | Expression |
| ||
start | any | Function | QueryBuilder |
| 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 |
| The end value of the BETWEEN statement. If a function or QueryBuilder is passed it is used as a subselect expression. | |
combinator | string |
|
| |
negate | boolean |
|
| False for BETWEEN, True for NOT BETWEEN. |
Adds a where between clause to the query.
If a function or QueryBuilder is passed it is used as a subselect expression.
whereNotBetween
Name | Type | Required | Default | Description |
column | string | Expression |
| ||
start | any | Function | QueryBuilder |
| 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 |
| The end value of the BETWEEN statement. If a function or QueryBuilder is passed it is used as a subselect expression. | |
combinator | string |
|
|
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.
whereColumn
Name | Type | Required | Default | Description |
first |
| |||
operator |
| The operator to use for the constraint (i.e. "=", "<", ">=", etc.). A value can be passed as the | ||
second | string | Expression |
| ||
combinator | string |
|
|
Adds a where clause to a query that compares two columns.
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
.
Expressions
can be passed in place of either column.
whereExists
Name | Type | Required | Default | Description |
query | Function | QueryBuilder |
| A function or QueryBuilder instance to be used as the exists subquery. | |
combinator | string |
|
| |
negate | boolean |
|
| False for EXISTS, True for NOT EXISTS. |
Adds a where exists clause to the query.
It can be configured with a function.
It can also be configured with a QueryBuilder instance.
whereNotExists
Name | Type | Required | Default | Description |
query | Function | QueryBuilder |
| A function or QueryBuilder instance to be used as the not exists subquery. | |
combinator | string |
|
|
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.
whereLike
Name | Type | Required | Default | Description |
column |
| |||
value | any |
| ||
combinator | string |
|
|
A shortcut for calling where
with "like"
set as the operator.
whereIn
Name | Type | Required | Default | Description |
column | string | Expression |
| ||
values |
| |||
combinator | string |
|
| |
negate | boolean |
|
| 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.
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.
Any value in the list or array can also be passed using a custom parameter type to have more control over the parameter settings.
Expressions
can be freely mixed in with other values.
A function or QueryBuilder instance can be passed to be used as a subquery expression instead of a list of values.
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 |
| ||
values |
| |||
combinator | string |
|
|
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.
whereRaw
Name | Type | Required | Default | Description |
sql | string |
| The raw SQL to add to the query. | |
whereBindings | array |
|
| |
combinator | string |
|
|
Shorthand to add a raw SQL statement to the where clauses.
whereNull
Name | Type | Required | Default | Description |
column | string | Expression |
| ||
combinator | string |
|
| |
negate | boolean |
|
| False for NULL, True for NOT NULL. |
Adds a where null clause to the query.
whereNotNull
Name | Type | Required | Default | Description |
column | string | Expression |
| ||
combinator | string |
|
| |
negate | boolean |
|
| False for NULL, True for NOT NULL. |
Adds a where not in clause to the query. This behaves identically to the whereNull
method with the negate
flag set to true
. See the documentation for whereNull
for usage and examples.
Dynamic Where Methods
qb uses onMissingMethod
to provide a few different helpers when working with where...
methods.
andWhere... and orWhere...
Every where...
method in qb can be called prefixed with either and
or or
. Doing so will call the original method using the corresponding combinator.
where{Column}
If you call a method starting with where
that does not match an existing qb method, qb will instead call the where
method using the rest of the method name as the first column name. (The rest of the arguments will be shifted to account for this.) This also applies to andWhere{Column}
and orWhere{Column}
method signatures.
Last updated