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();

Paginating results

When you show rows a page at a time, paginate() does the arithmetic for you. Give it a page size and the current page number, and it runs a COUNT over the same filters, fetches just that page's rows, and hands back a Paginator with everything a pager UI needs:

$page = table('posts')
	->where('published', true)
	->orderBy('created', 'desc')
	->paginate(perPage: 15, page: (int) (request()->query['page'] ?? 1));

foreach ($page as $post) {
	echo $post['title'];
}

The Paginator is iterable and countable, so foreach ($page as $row) walks the current page and count($page) is the number of rows on it. It also carries the counters a template reaches for:

$page->total();          // total matching rows across every page
$page->perPage();        // the page size
$page->currentPage();    // the 1-based page number
$page->lastPage();       // the number of the final page (always at least 1)
$page->from();           // 1-based index of the first row on this page, or null when empty
$page->to();             // 1-based index of the last row on this page, or null when empty
$page->hasMorePages();   // is there a page after this one?
$page->isEmpty();        // did this page come back with no rows?

For an API, toArray() gives you a JSON-ready shape with snake_case keys:

return json(table('posts')->where('published', true)->paginate(20, $n)->toArray());
// { "data": [...], "total": 128, "per_page": 20, "current_page": 3, "last_page": 7, "from": 41, "to": 60 }

Repositories paginate the same way, and their pages carry hydrated models instead of arrays. A page requested past the end comes back empty rather than throwing, so a stale ?page=999 link degrades gracefully. Page size is clamped to a sane ceiling so a hostile ?per_page value cannot ask for a million rows at once.

A couple of things to keep in mind. The count runs over your where filters but ignores orderBy, limit, and offset, since those do not change how many rows match. A join can inflate the total when it multiplies rows, so paginate over the base table and load related data separately if the numbers look off. Pagination over a groupBy/having query is rejected outright, because a plain COUNT cannot honestly total grouped rows.

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.