Query Builder

The query builder gives you a fluent, chainable way to compose SQL without writing it by hand. You start from table(), add clauses, and finish with a method that runs the query. It is the sweet spot between raw SQL and full models.

Selecting rows

Start with the table name and chain conditions onto it:

$rows = table('users')
	->where('active', true)
	->orderBy('name')
	->limit(100)
	->get();

get() returns all matching rows as associative arrays. When you only want one row, use first(), which returns a single row or null:

$user = table('users')->where('id', $id)->first();

Where clauses

where() is flexible. Pass a column and value for an equality check, or add an operator in the middle for anything else:

table('orders')->where('total', '>', 100)->get();
table('orders')->where('status', 'paid')->get();      // shorthand for = 'paid'

There is a whole family of where methods for common cases:

->where('a', 1)->orWhere('b', 2)   // OR
->whereIn('id', [1, 2, 3])
->whereNotIn('id', [4, 5])
->whereNull('deleted_at')
->whereNotNull('confirmed_at')
->whereRaw('created_at >= ?', [$since])   // an escape hatch, with bindings

Values you pass to these methods are always bound as parameters, so untrusted input is safe here. Column names and operators, on the other hand, are treated as trusted configuration.

SQL identifiers like table names, column names, and sort directions cannot be bound as parameters. Never pass raw request input as a column name, an operator, or a sort field. Bind untrusted values; keep untrusted input away from identifiers.

Ordering, grouping, and joins

The builder covers the usual shaping clauses:

table('sales')
	->select('region', 'SUM(total) AS revenue')
	->where('year', 2024)
	->groupBy('region')
	->having('revenue', '>', 10000)
	->orderBy('revenue', 'desc')
	->get();

table('orders')
	->join('users', 'orders.user_id', '=', 'users.id')
	->leftJoin('coupons', 'orders.coupon_id', '=', 'coupons.id')
	->get();

Aggregates and existence checks

Some questions do not need the rows themselves, just a number or a yes/no:

$count  = table('users')->where('active', true)->count();
$total  = table('orders')->where('paid', true)->value('SUM(total)');
$exists = table('users')->where('email', $email)->exists();

Writing data

The builder writes as well as reads. insert() returns the last insert id, insertMany() returns how many rows it wrote, and update() and delete() return the number of affected rows:

$id = table('users')->insert(['name' => 'Ada', 'active' => true]);

table('users')->insertMany([
	['name' => 'Grace'],
	['name' => 'Alan'],
]);

table('users')->where('id', $id)->update(['active' => false]);

table('users')->where('active', false)->delete();

Streaming big result sets

For queries that return a lot of rows, do not pull them all into memory at once. The builder has a stream() method that yields rows in bounded batches:

foreach (table('events')->where('kind', 'sale')->stream(250) as $row) {
	process($row);
}

There is a whole page on this, since it has its own rules; see Streaming Results.

When you want your rows to come back as typed objects instead of arrays, move on to Models & Repositories.