Query Builder
Introduction
The query builder provides a fluent interface for building and running database queries. Every value is bound through $wpdb->prepare() and every identifier is validated and backtick-quoted, so builder queries are protected against SQL injection. See SQL-injection safety.
All examples assume a model, since the builder is always bound to one:
use BitApps\WPDatabase\Model;
class Contact extends Model
{
protected $fillable = ['first_name', 'last_name', 'email', 'status', 'age'];
}
Retrieving results
Retrieving all rows
$contacts = Contact::query()->get();
foreach ($contacts as $contact) {
echo $contact->email;
}
get() returns a Collection of hydrated models (false on database error). all() is an alias.
Selecting columns
Contact::query()->select('id', 'email')->get();
Contact::query()->select(['id', 'email'])->get();
Contact::query()
->select('id')
->addSelect('email') // append to an existing select
->get();
Contact::query()
->selectRaw('COUNT(*) AS total') // raw select expression with bindings
->get();
Contact::query()->distinct()->select('status')->get();
Retrieving a single row
$contact = Contact::query()->where('email', 'jane@example.com')->first();
$contact = Contact::query()->find(5); // primary-key lookup
$contact = Contact::query()->findOne(['email' => 'jane@example.com']);
first() returns a single model, or an empty array when nothing matches. find() accepts a scalar primary-key value; findOne() accepts an array of attribute constraints and returns the first match.
Plucking values
pluck() lives on Collection, not the builder:
$emails = Contact::query()->select('email')->get()->pluck('email');
Pagination
$page = Contact::query()->where('status', 'active')->paginate(2, 20);
paginate($pageNo, $perPage) returns an array:
[
'data' => Collection, // the page of models
'total' => 45, // rows matching the query
'current_total' => 20, // rows on this page
'pages' => 3,
'current_page' => 2,
'last_page' => 3,
'per_page' => 20,
]
There is no chunk(), cursor(), or lazy(). To process a large table in batches, loop with take() / skip():
$offset = 0;
do {
$batch = Contact::query()->orderBy('id')->asc()->take(500)->skip($offset)->get();
// ... process $batch ...
$offset += 500;
} while (count($batch) === 500);
Aggregates
$count = Contact::query()->where('status', 'active')->count();
$max = Contact::query()->max('age');
$min = Contact::query()->min('age');
$avg = Contact::query()->avg('age');
$sum = Contact::query()->sum('age');
$stddev = Contact::query()->aggregate('STDDEV', 'age'); // any bare-identifier function
count() compiles to COUNT(primary_key), not COUNT(*), so rows with a NULL primary key are not counted.
Where clauses
Basic wheres
Contact::query()->where('status', 'active')->get(); // = implied
Contact::query()->where('age', '>=', 18)->get(); // explicit operator
Contact::query()
->where('status', 'active')
->where('age', '>', 21) // chained = AND
->get();
Allowed operators: =, !=, <>, >, <, >=, <=, LIKE, NOT LIKE. Anything else throws RuntimeException before any SQL runs.
Contact::query()->where('last_name', 'LIKE', 'Sm%')->get();
Contact::query()->where('deleted_reason', null)->get(); // compiles to IS NULL
Or wheres
Contact::query()
->where('status', 'active')
->orWhere('age', '>', 65)
->get();
Grouped wheres
Pass a closure to group conditions in parentheses:
Contact::query()
->where('status', 'active')
->where(function ($query) {
$query->where('age', '>', 65)
->orWhere('last_name', 'Smith');
})
->get();
// SELECT ... WHERE status = 'active' AND (age > 65 OR last_name = 'Smith')
You can also pass an array of clauses:
Contact::query()->where([
['status', 'active'],
['age', '>', 21],
])->get();
whereIn / whereNull / whereBetween
Contact::query()->whereIn('id', [1, 2, 3])->get();
Contact::query()->whereNull('deleted_reason')->get();
Contact::query()->whereNotNull('email')->get();
Contact::query()->whereBetween('age', 18, 65)->get();
Contact::query()->orWhereBetween('created_at', $start, $end)->get();
whereIn() requires an array; passing a scalar throws. Avoid null values inside the whereIn() array; use whereNull() for null checks. Not supported: whereNotIn, orWhereIn, whereColumn, whereExists, and date-part wheres (whereDate etc.). Express those with whereRaw().
Raw wheres
Contact::query()
->whereRaw('LOWER(email) = %s', ['jane@example.com'])
->orWhereRaw('age * 2 > %d', [100])
->get();
Conditional clauses
when() applies a clause only when the first argument is truthy:
$status = isset($_GET['status']) ? sanitize_text_field(wp_unslash($_GET['status'])) : null;
Contact::query()
->when($status, function ($query, $status) {
$query->where('status', $status);
}, function ($query) {
$query->where('status', 'active'); // default when falsy
})
->get();
Soft-delete scopes
On models with public $soft_deletes = true, reads exclude trashed rows by default:
Contact::query()->withTrashed()->get(); // include trashed
Contact::query()->onlyTrashed()->get(); // only trashed
Ordering, grouping & limits
Ordering
Direction is set by chaining asc() / desc() after orderBy(). There is no direction argument and no orderByDesc():
Contact::query()->orderBy('created_at')->desc()->get();
Contact::query()->orderBy('last_name')->asc()->get();
Contact::query()->orderByRaw('FIELD(status, %s, %s)', ['active', 'pending'])->get();
Calling asc() / desc() without a preceding orderBy() orders by the primary key.
Grouping
Contact::query()
->select('status')
->selectRaw('COUNT(*) AS total')
->groupBy('status')
->having('total', '>', 10)
->get();
having() / orHaving() take the same argument forms as where().
Limit & offset
Contact::query()->take(10)->get(); // LIMIT 10
Contact::query()->take(10)->skip(20)->get(); // LIMIT 10 OFFSET 20
There are no limit() / offset() aliases.
skip() without take() is silently ignored: MySQL requires a LIMIT for OFFSET, and the grammar drops the offset when no limit is set.
Joins
Contact::query()
->join('deals', 'contacts.id', '=', 'deals.contact_id')
->select('contacts.*', 'deals.amount')
->get();
Contact::query()->leftJoin('deals', 'contacts.id', '=', 'deals.contact_id')->get();
Contact::query()->rightJoin('deals', 'contacts.id', '=', 'deals.contact_id')->get();
Contact::query()->crossJoin('regions')->get();
The joined table gets the same prefix as the model's table automatically. Aliases use the table AS alias form:
Contact::query()
->join('deals AS d', 'contacts.id', '=', 'd.contact_id')
->get();
Additional ON conditions
Chain on() / orOn() for extra column comparisons, onValue() for comparisons against bound values, and onRaw() for raw fragments:
Contact::query()
->join('deals', 'contacts.id', '=', 'deals.contact_id')
->on('contacts.region_id', '=', 'deals.region_id')
->onValue('deals.status', '=', 'won')
->orOnValue('deals.status', '=', 'pending')
->orOnRaw('deals.closed_at > %s', ['2026-01-01'])
->get();
Each on* method has an orOn* counterpart that joins the condition with OR: on() / orOn(), onValue() / orOnValue(), onRaw() / orOnRaw(). All of them throw if no join has been added yet.
joinWhere() joins with a value comparison directly:
Contact::query()
->joinWhere('deals', 'deals.status', '=', 'won')
->get();
Not supported: FULL JOIN (MySQL has no FULL JOIN; fullJoin() exists but always throws), subquery joins (joinSub), and closure-based join definitions.
Unions
The builder has no union() / unionAll(). Write a reviewed UNION query with unsafeRaw() if you need one.
Inserts
// Single row: returns the hydrated model (false on failure)
$contact = Contact::query()->insert([
'first_name' => 'Jane',
'email' => 'jane@example.com',
]);
echo $contact->id;
// Multiple rows: one bulk INSERT, returns a Collection
Contact::query()->insert([
['email' => 'a@example.com'],
['email' => 'b@example.com'],
]);
// Skip rows that would violate a unique index
Contact::query()->insertOrIgnore([
['email' => 'a@example.com'],
['email' => 'dupe@example.com'],
]);
insertOrIgnore() is not in any published release (latest is 2.0.3). Calling it on an installed version raises Call to undefined method.
With $timestamps enabled, created_at / updated_at are filled automatically. Array and object values are JSON-encoded before storage.
Updates
Contact::query()
->where('status', 'pending')
->update(['status' => 'active']);
You can also mutate a loaded model and save() it:
$contact = Contact::query()->find(5);
$contact->status = 'active';
$contact->save();
Both return false on failure; check Connection::prop('last_error').
Not supported: increment() / decrement(), updateOrCreate(), firstOrCreate(). For counters, use a raw update expression.
Upserts
upsert() inserts rows, and updates the listed columns when a row with the same primary or unique key already exists:
Contact::query()->upsert(
[
['email' => 'jane@example.com', 'status' => 'active'],
['email' => 'john@example.com', 'status' => 'pending'],
],
['status'] // columns to update on duplicate key; defaults to all value columns
);
With $timestamps enabled, created_at is excluded from the update set and updated_at is always bumped.
upsertRaw() allows SQL expressions in the update clause. It is also unreleased — same caveat as insertOrIgnore() above:
Contact::query()->upsertRaw(
[['email' => 'jane@example.com', 'visits' => 1]],
['visits' => 'visits + VALUES(visits)']
);
The update set is exactly the expressions you pass. updated_at is never bumped automatically, so include it if you need it.
upsert() emits INSERT … ON DUPLICATE KEY UPDATE, which is MySQL/MariaDB only, and the table needs a primary or unique index on the conflict column(s).
Deletes
Contact::query()->where('status', 'spam')->delete();
Contact::query()->destroy([1, 2, 3]); // delete by primary keys
On soft-delete models (public $soft_deletes = true), delete() sets deleted_at instead of removing rows:
Contact::query()->where('id', 5)->delete(); // soft delete
Contact::query()->onlyTrashed()->restore(); // un-trash
Contact::query()->where('id', 5)->forceDelete(); // permanent
forceDelete() and restore() throw on models without soft deletes enabled.
Reusing and inspecting a query
$base = Contact::query()->where('status', 'active');
$count = $base->clone()->count(); // clone keeps every clause
$newest = $base->clone()->orderBy('created_at')->desc()->take(5)->get();
$fresh = $base->newQuery(); // empty builder for the same model
clone() copies the builder with all clauses intact; use it before a terminal call so the original stays reusable. newQuery() returns a clause-free builder bound to the same model.
from() sets a table alias, not a table name:
Contact::query()->from('c')->where('c.status', 'active')->get();
// SELECT * FROM `wp_contacts` AS `c` WHERE `c`.`status` = %s
Introspection
These return the builder's current state, useful for debugging and for tooling built on top of the builder:
| Method | Returns |
|---|---|
getModel() | The bound model |
getTable() | Physical, prefixed table name |
getBindings() | Bindings in placeholder order |
addBindings($bindings) / resetBindings() | Append to or clear the binding list |
getClauseList('where'|'having') | Clause array; the where list includes the injected soft-delete scope on SELECTs |
getJoins() | Join definitions |
getGroupByList() / getOrderByList() | Grouping and ordering state |
getLimitValue() / getOffsetValue() | take() / skip() values |
getFromAlias() | Alias set by from() |
getSelectExpressions() | Framework-generated select expressions (relation aggregates) |
isDistinct() | Whether distinct() was applied |
getTableMap() | Logical → physical table names for non-aliased joins |
getTableAliases() | Aliases in scope (from() + join aliases) |
grammar() | The Grammar instance that compiles SELECTs |
prepareRaw() | The stored raw SQL string, after unsafeRaw() / rawPrepared() |
getValueType($value) | The placeholder a value would bind as: %d, %f or %s |
prepareColumnName($column), renderIdentifier($column, $allowWildcard, $allowAlias), resolveQualifier($column) | Identifier compilation, the same validation the builder applies internally |
queryFor() sets a value nothing reads, so it has no effect on the compiled query. prepareKeySubquery() is used internally by relation loading.
SQL-injection safety
Builder queries are safe by construction:
- Values are never interpolated. Every scalar becomes a
$wpdb->prepare()placeholder chosen by type (%dfor int,%ffor float,%sotherwise). Arrays/objects are JSON-encoded;nullcompiles to a literalNULL. - Identifiers (tables, columns, aliases) are validated against a strict regex and backtick-quoted. Schema-qualified or malformed identifiers throw.
- Operators, join types, and boolean connectors are whitelisted; an unknown operator throws
RuntimeExceptionbefore any SQL is built.
Raw SQL enters only through explicitly named methods. Pick the narrowest one that works:
There is no DB::raw() expression object. Raw fragments go through selectRaw(), whereRaw(), orderByRaw(), onRaw(), upsertRaw(), and the raw query APIs on the Getting Started page, each of which accepts a bindings array. Bind request data; never concatenate it.