Wheres

Where Methods

where

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.

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

This method is simply an alias for where with the combinator set to "and".

orWhere

This method is simply an alias for where with the combinator set to "or".

whereBetween

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

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

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

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

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

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

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

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

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

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

Adds a where null clause to the query.

QueryBuilder

query.from( "users" )
    .whereNull( "id" );

MySQL

SELECT *
FROM `users`
WHERE `id` IS NULL

whereNotNull

Adds a where not in clause to the query. This behaves identically to the whereNull method with the negateflag 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.

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` = ?