WHERE Clauses

WHERE Clauses

The bulk of the DSL lives here. WHERE, HAVING and ON share the same clause-builder internals — every method below returns the same fragment shape, just into a different bucket.

The three buckets

Method	Bucket	Used by
where	where	SELECT, UPDATE, DELETE
having	having	SELECT (aggregate filter)
on	on	JOIN closure (see JOIN Queries)

The shared signature:

where(column, operator = '=', value = null, logical = 'AND')

• column — a string identifier (escaped) or a RawQuery (verbatim).
• operator — = (default), !=, <>, >, <, >=, <=, or any of the special operators introduced below ('IS', 'BETWEEN', 'IN', 'LIKE', 'REGEXP', …).
• value — the right-hand side. Strings are parameterized; integers and a few "safe" forms are inlined (see Parameters).
• logical — 'AND' (default) or 'OR'. '&&' / '||' aliases are also accepted.

An unknown connector raises QueryBuilderInvalidArgumentException.

The value-shortcut

The most common form is two arguments:

$qb->where('id', 5);
// WHERE `id` = 5

When the value slot is null and the operator slot is not actually a SQL operator, the two are swapped and = is assumed. So where('id', 5) and where('id', '=', 5) produce identical SQL.

💡 Boolean values are valid via this shortcut after the v2.0.0 fix. Pre-v2 the loose in_array() comparison made where('active', true) collapse to WHERE active — see the CHANGELOG B27 entry.

Comparison operators

$qb->where('age', '>=', 18);            // WHERE `age` >= 18
$qb->where('status', '!=', 'banned');   // WHERE `status` != :status
$qb->where('score', '<>', 0);           // WHERE `score` <> 0

The full set: =, !=, <>, >, <, >=, <=.

AND / OR connectors

$qb->from('users')
   ->where('country', 'TR')
   ->andWhere('active', 1);
// WHERE `country` = :country AND `active` = 1

$qb->from('users')
   ->where('country', 'TR')
   ->orWhere('country', 'US');
// WHERE `country` = :country OR `country` = :country_1

andWhere(...) / orWhere(...) are convenience aliases for where(..., 'AND') / where(..., 'OR'). Every other where-sugar method below has the same and* / or* variant pairs.

When you need parenthesized groups, use Grouped Conditions.

NULL checks

Helper	SQL
whereIsNull(col)	col IS NULL
orWhereIsNull(col)	OR bucket
andWhereIsNull(col)	AND bucket
whereIsNotNull(col)	col IS NOT NULL
orWhereIsNotNull(col)	OR bucket
andWhereIsNotNull(col)	AND bucket

$qb->from('post')->whereIsNull('deleted_at');
// SELECT * FROM `post` WHERE `deleted_at` IS NULL

BETWEEN

Two argument forms — separate bounds OR a two-element array:

$qb->between('id', 10, 20);
$qb->between('id', [10, 20]);
// WHERE `id` BETWEEN 10 AND 20

Numeric bounds are inlined; strings, dates, and other values flow through the parameter bag:

$qb->between('date', '2026-01-01', '2026-12-31');
// WHERE `date` BETWEEN :date AND :date_1

Mixing parameters with a RawQuery (e.g. a SQL function) on one side:

$qb->between('date', '2026-01-01', $qb->raw('NOW()'));
// WHERE `date` BETWEEN :date AND NOW()

Helper	Variant
between(col, a, b) / andBetween(...)	AND BETWEEN
orBetween(col, a, b)	OR BETWEEN
notBetween(col, a, b) / andNotBetween(...)	AND NOT BETWEEN
orNotBetween(col, a, b)	OR NOT BETWEEN

IN / NOT IN

$qb->whereIn('id', [1, 2, 3]);          // WHERE `id` IN (1, 2, 3)
$qb->whereIn('country', ['TR', 'US']);  // WHERE `country` IN (:country, :country_1)
$qb->whereNotIn('id', [4, 5]);          // WHERE `id` NOT IN (4, 5)

• Numeric items are inlined.
• String items are parameterized, with auto-suffix collision protection.
• Items are deduplicated before emission ([1, 2, 2, 3, 1] → (1, 2, 3)).
• A RawQuery (e.g. a sub-query) is passed through verbatim:

$qb->whereIn('id', $qb->raw('(SELECT user_id FROM bans)'));
// WHERE `id` IN (SELECT user_id FROM bans)

Helper
whereIn(col, vals) / andWhereIn(...) / orWhereIn(...)
whereNotIn(col, vals) / andWhereNotIn(...) / orWhereNotIn(...)

LIKE family

The LIKE helpers wrap the supplied value with % according to $type ('both', 'before'/'start', 'after'/'end').

Helper	Value 'foo' → pattern	SQL
like(col, 'foo')	%foo%	col LIKE :p
notLike(col, 'foo')	%foo%	col NOT LIKE :p
startLike(col, 'foo')	foo%	col LIKE :p
notStartLike(col, 'foo')	foo%	col NOT LIKE :p
endLike(col, 'foo')	%foo	col LIKE :p
notEndLike(col, 'foo')	%foo	col NOT LIKE :p

Each helper has the usual and* / or* variants:

$qb->from('user')->orLike('username', 'php');
// WHERE `username` LIKE :username   (in the OR bucket)

🔐 LIKE wildcard auto-escape (v2.0.0). %, _, and \ inside the user-supplied value are escaped by default so a user typing % doesn't accidentally turn into a wildcard. To opt out, pass a RawQuery:

$qb->like('name', $qb->raw("'custom%pattern'"));
// LIKE 'custom%pattern'   — no escape applied

See Security §V4 for the full discussion.

REGEXP

$qb->regexp('username', '^[a-z]+$');
// WHERE `username` REGEXP :username

⚠️ MySQL-flavored. PostgreSQL users want ~; use RawQuery for that.

andRegexp(col, val) / orRegexp(col, val) are the connector-specific variants.

SOUNDEX

$qb->soundex('name', 'Robert');
// WHERE SOUNDEX(`name`) LIKE CONCAT('%', TRIM(TRAILING '0' FROM SOUNDEX(:name)), '%')

andSoundex(col, val) / orSoundex(col, val) exist as expected.

FIND_IN_SET

MySQL-specific set-membership check:

$qb->findInSet('roles', 'admin');
// WHERE FIND_IN_SET(:roles, `roles`)

$qb->notFindInSet('roles', 'admin');
// WHERE NOT FIND_IN_SET(:roles, `roles`)

🔐 The v2.0.0 release fixed an SQL-injection vector in this method — raw string values are now always parameterized (see CHANGELOG B28).

andFindInSet, orFindInSet, andNotFindInSet, orNotFindInSet are the connector variants.

HAVING

having() accepts the same arguments as where() and routes into the HAVING bucket. Combine with GROUP BY:

$qb->select('author_id')
   ->selectCount('id', 'post_count')
   ->from('post')
   ->groupBy('author_id')
   ->having('post_count', '>', 5);
// SELECT `author_id`, COUNT(`id`) AS `post_count`
//   FROM `post` WHERE 1
//  GROUP BY `author_id`
// HAVING `post_count` > 5

The full sugar family (between, in, like, …) is WHERE-only. For HAVING, call having() directly with the operator you need, or use Raw Queries:

$qb->having($qb->raw('COUNT(id) > 5'));

ON (for JOIN closures)

on() is where() / having()'s sibling, except that string values containing a dot are treated as column references (not parameter values):

$qb->on('c.id', 'p.category_id');
// ON `c`.`id` = `p`.`category_id`

That makes it natural to compose ON expressions via the closure form of join(). See JOIN Queries.

Quick lookup

You want…	Use
col = value	where(col, value)
col >= value	where(col, '>=', value)
col IS NULL	whereIsNull(col)
col IS NOT NULL	whereIsNotNull(col)
col BETWEEN a AND b	between(col, a, b)
col IN (...)	whereIn(col, [...])
col LIKE '%foo%'	like(col, 'foo')
col LIKE 'foo%'	startLike(col, 'foo')
col LIKE '%foo'	endLike(col, 'foo')
col REGEXP '…'	regexp(col, '…')
SOUNDEX(col) ~= SOUNDEX(value)	soundex(col, value)
FIND_IN_SET(value, col)	findInSet(col, value)
parenthesized group	group(closure, 'AND' | 'OR')
raw SQL fragment	where($qb->raw('…'))

---

Next: JOIN Queries