LogoLogo
9.1.0
9.1.0
  • Introduction
  • What's New?
  • Installation & Usage
  • Migration Guide
  • Contributing & Filing Issues
  • Query Builder
    • Getting a New Query
    • Building Queries
      • Selects
      • From
      • Joins
      • Wheres
      • Order By
      • Group By and Having
      • Limit, Offset, and Pagination
      • Locks
      • Unions
      • Common Table Expressions (i.e. CTEs)
      • Raw Expressions
      • When / Conditionals
      • Query Parameters and Bindings
    • Executing Queries
      • Retrieving Results
      • Aggregates
      • Inserts, Updates, and Deletes
    • Options and Utilities
      • Query Options
      • Clone and Reset
      • Return Format
      • Column Formatter
      • Interception Points
    • Debugging
      • sqlCommenter
  • Schema Builder
    • Overview
    • Create
    • Columns
    • Column Modifiers
    • Column Constraints
    • Creating Table Constraints
    • Alter
    • Drop
  • External Links
    • API Docs
    • Source Code
    • Issue Tracker
Powered by GitBook
On this page
  • Where Methods
  • where
  • andWhere
  • orWhere
  • whereBetween
  • whereNotBetween
  • whereColumn
  • whereExists
  • whereNotExists
  • whereLike
  • whereNotLike
  • whereIn
  • whereNotIn
  • whereRaw
  • whereNull
  • whereNotNull
  • Dynamic Where Methods
  • andWhere... and orWhere...
  • where{Column}

Was this helpful?

Edit on GitHub
Export as PDF
  1. Query Builder
  2. Building Queries

Wheres

Table of Contents

Where Methods

where

Name

Type

Required

Default

Description

column

true

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

combinator

string

false

"and"

Adds a where clause to a query.

QueryBuilder
query.from( "users" )
    .where( "active", "=", 1 );
MySQL
SELECT *
FROM `users`
WHERE `active` = ?
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` = ?
  )

andWhere

Name

Type

Required

Default

Description

column

true

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

orWhere

Name

Type

Required

Default

Description

column

true

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

whereBetween

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"

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

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"

whereColumn

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"

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"

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"

whereLike

Name

Type

Required

Default

Description

column

true

value

any

false

combinator

string

false

"and"

QueryBuilder
query.from( "users" )
    .whereLike( "username", "J%" );
MySQL
SELECT *
FROM `users`
WHERE `username` LIKE ?

whereNotLike

Name

Type

Required

Default

Description

column

true

value

any

false

combinator

string

false

"and"

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

values

true

combinator

string

false

"and"

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 (?, ?, ?)
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

values

true

combinator

string

false

"and"

whereRaw

Name

Type

Required

Default

Description

sql

string

true

The raw SQL to add to the query.

whereBindings

array

false

[]

combinator

string

false

"and"

Shorthand to add a raw SQL statement to the where clauses.

QueryBuilder
query.from( "users" )
    .whereRaw(
        "id = ? OR email = ? OR is_admin = 1",
        [ 1, "foo" ]
    );
MySQL
SELECT *
FROM `users`
WHERE id = ? OR email = ? OR is_admin = 1

whereNull

Name

Type

Required

Default

Description

column

string | Expression

true

combinator

string

false

"and"

negate

boolean

false

false

False for NULL, True for NOT NULL.

Adds a where null clause to the query.

QueryBuilder
query.from( "users" )
    .whereNull( "id" );
MySQL
SELECT *
FROM `users`
WHERE `id` IS NULL

whereNotNull

Name

Type

Required

Default

Description

column

string | Expression

true

combinator

string

false

"and"

negate

boolean

false

false

False for NULL, True for NOT NULL.

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.

QueryBuilder
query.from( "users" )
    .where( "username", "like", "j%" )
    .andWhere( function( q ) {
        q.where( "isSubscribed", 1 )
            .orWhere( "isOnFreeTrial", 1 );
     } );
MySQL
SELECT *
FROM `users`
WHERE `username` LIKE ?
  AND (
    `isSubscribed` = ?
    OR
    `isOnFreeTrial` = ?
  )

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.

QueryBuilder
query.from( "users" )
    .whereUsername( "like", "j%" )
    .whereActive( 1 );
MySQL
SELECT *
FROM `users`
WHERE `username` LIKE ?
  AND `active` = ?
PreviousJoinsNextOrder By

Last updated 2 years ago

Was this helpful?

string | | Function

The name of the column or with which to constrain the query. A function can be passed to begin a nested where statement.

string |

The value with which to constrain the column. An can be passed as well. If a QueryBuilder or Function is passed, it will be used as a subselect expression.

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

Using the where method will parameterize the value passed. If you want to constrain a column to another column, use the method.

You can also pass an as the value.

You may also use statements to simplify this further.

string | | Function

The name of the column or with which to constrain the query. A function can be passed to begin a nested where statement.

string |

The value with which to constrain the column. An 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 with the combinator set to "and".

string | | Function

The name of the column or with which to constrain the query. A function can be passed to begin a nested where statement.

string |

The value with which to constrain the column. An 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 with the combinator set to "or".

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

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

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

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

Adds a where not in clause to the query. This behaves identically to the method with the negateflag set to true. See the documentation for for usage and examples.

string |

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

string |

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

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

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

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

Adds a where not in clause to the query. This behaves identically to the method with the negateflag set to true. See the documentation for for usage and examples.

string |

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

The value with which to constrain the column. An can be passed as well. If a QueryBuilder or Function is passed, it will be used as a subselect expression.

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

A shortcut for calling with "like" set as the operator.

string |

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

The value with which to constrain the column. An can be passed as well. If a QueryBuilder or Function is passed, it will be used as a subselect expression.

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

A shortcut for calling with "not like" set as the operator.

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

string | array | | Function | QueryBuilder

A single value, list of values, or array of values to constrain a column with. 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.

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

Any value in the list or array can also be passed using a to have more control over the parameter settings.

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

string | array | | Function | QueryBuilder

A single value, list of values, or array of values to constrain a column with. 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.

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the andWhere and orWhere 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 for usage and examples.

Any bindings needed for the raw SQL. Bindings can be simple values or .

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

The name of the column to check if it is NULL. Can also pass an .

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

The name of the column to check if it is NULL. Can also pass an .

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

Adds a where not in clause to the query. This behaves identically to the method with the negateflag set to true. See the documentation for for usage and examples.

Expression
whereColumn
dynamic where{Column}
where
where
whereBetween
whereBetween
whereExists
whereExists
where
where
whereIn
whereNull
whereNull
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expressions
Expression
Expression
Expressions
Expression
Expression
where
andWhere
orWhere
whereBetween
whereNotBetween
whereColumn
whereExists
whereNotExists
whereLike
whereIn
whereNotIn
whereRaw
whereNull
whereNotNull
andWhere
orWhere
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
custom parameter type
custom parameters