Query Builder

The query builder provides a fluent interface for constructing SQL queries. All queries use prepared statements automatically.

Basic Queries #

Retrieving Data #

// Get all rows
$users = db()->table('users')->get();

// Get first row
$user = db()->table('users')->where('id', '=', 1)->first();

// Find by primary key
$user = db()->table('users')->find(1);

// Get single column values
$emails = db()->table('users')->pluck('email');

Selecting Columns #

$users = db()->table('users')
    ->select('id', 'name', 'email')
    ->get();

// With alias
$users = db()->table('users')
    ->select('id', 'name as username')
    ->get();

Where Clauses #

Basic Where #

$users = db()->table('users')
    ->where('status', '=', 'active')
    ->where('role', '=', 'admin')
    ->get();

// With comparison operators
$users = db()->table('users')
    ->where('age', '>=', 18)
    ->where('created_at', '>', '2025-01-01')
    ->get();

Or Where #

$users = db()->table('users')
    ->where('role', '=', 'admin')
    ->orWhere('role', '=', 'moderator')
    ->get();

Where In #

$users = db()->table('users')
    ->whereIn('status', ['active', 'pending'])
    ->get();

Where Null / Not Null #

$drafts = db()->table('posts')
    ->whereNull('published_at')
    ->get();

$published = db()->table('posts')
    ->whereNotNull('published_at')
    ->get();

Ordering and Limiting #

$posts = db()->table('posts')
    ->orderBy('created_at', 'desc')
    ->limit(10)
    ->offset(20)
    ->get();

Aggregates #

$count = db()->table('users')->count();

For other aggregates (MAX, MIN, AVG, SUM), use selectRaw():

$stats = db()->table('orders')
    ->selectRaw('MAX(total) as max_total, SUM(total) as revenue')
    ->first();

Inserting Data #

// Insert single row
db()->table('users')->insert([
    'name' => 'John',
    'email' => 'john@example.com',
]);

// Insert and get the new ID
$id = db()->table('users')->insertGetId([
    'name' => 'John',
    'email' => 'john@example.com',
]);

Updating Data #

db()->table('users')
    ->where('id', '=', 1)
    ->update(['status' => 'inactive']);

// Update multiple rows
db()->table('posts')
    ->where('status', '=', 'draft')
    ->where('created_at', '<', '2025-01-01')
    ->update(['status' => 'archived']);

Deleting Data #

db()->table('users')
    ->where('id', '=', 1)
    ->delete();

// Delete multiple
db()->table('sessions')
    ->where('expires_at', '<', now())
    ->delete();

Raw Expressions #

For complex queries, use raw expressions:

$stats = db()->table('orders')
    ->select(
        raw('DATE(created_at) as date'),
        raw('COUNT(*) as count'),
        raw('SUM(total) as revenue')
    )
    ->where('status', '=', 'completed')
    ->groupBy('created_at')
    ->get();

Query Caching #

Cache expensive queries:

$posts = db()->table('posts')
    ->where('status', '=', 'published')
    ->orderBy('created_at', 'desc')
    ->cache(3600)  // Cache for 1 hour
    ->get();

Cached results are returned on subsequent identical queries until the TTL expires. There is no automatic cache invalidation — use cache tags or clear the cache manually when data changes.

Transactions #

See Database Connection for transaction handling.

Pagination #

The paginate() method handles offset-based pagination automatically:

$result = db()->table('posts')
    ->where('status', 'published')
    ->orderBy('created_at', 'desc')
    ->paginate(perPage: 15, page: 2);

// Returns:
// [
//     'data'         => Collection,   // rows for this page
//     'total'        => 47,           // total matching rows
//     'per_page'     => 15,
//     'current_page' => 2,
//     'last_page'    => 4,
// ]

paginate() works with where, join, and other builder clauses. It runs a COUNT(*) query internally and strips the ORDER BY from the count for efficiency.

Complete Example #

// Paginated, filtered post listing
$page = (int) $request->query('page', 1);

$result = db()->table('posts')
    ->select('id', 'title', 'slug', 'status', 'created_at')
    ->where('status', '=', 'published')
    ->whereNotNull('published_at')
    ->orderBy('published_at', 'desc')
    ->cache(300)
    ->paginate(perPage: 10, page: $page);