Query Builder

Query Builder

The fluent query builder compiles SQL at execution time. Create one via the DB facade:

use Antimonial\Database\DB;

SELECT

$users = DB::table('users')
    ->select('id', 'name', 'email')
    ->where('active', true)
    ->orderBy('name')
    ->limit(10)
    ->get();

$user = DB::table('users')->where('id', 42)->first();
$name = DB::table('users')->where('id', 42)->value('name');
$emails = DB::table('users')->pluck('email', 'id');  // Key-value array

Distinct

$roles = DB::table('users')->distinct()->select('role')->get();

WHERE Clauses

Basic

DB::table('users')->where('age', 18)->get();                   // age = 18
DB::table('users')->where('age', '>=', 18)->get();            // age >= 18

OR

DB::table('users')
    ->where('role', 'admin')
    ->orWhere('role', 'moderator')
    ->get();

Nested (Closure)

DB::table('users')
    ->where('active', true)
    ->where(function ($q) {
        $q->where('role', 'admin')
          ->orWhere('role', 'moderator');
    })
    ->get();

IN / NOT IN

DB::table('users')->whereIn('id', [1, 2, 3])->get();
DB::table('users')->whereNotIn('id', [4, 5])->get();

NULL / NOT NULL

DB::table('users')->whereNull('deleted_at')->get();
DB::table('users')->whereNotNull('email_verified_at')->get();

Raw

DB::table('users')
    ->whereRaw('age > ? OR role = ?', [18, 'admin'])
    ->get();

JOIN

DB::table('users')
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->select('users.name', 'posts.title')
    ->get();

DB::table('users')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->get();

GROUP BY / HAVING

DB::table('users')
    ->select('role', 'COUNT(*) AS cnt')
    ->groupBy('role')
    ->having('cnt', '>', 5)
    ->get();

ORDER BY / LIMIT / OFFSET

DB::table('users')
    ->orderBy('name', 'DESC')
    ->orderBy('created_at')
    ->limit(10)
    ->offset(20)
    ->get();

Pagination

$result = DB::table('posts')
    ->where('status', 'published')
    ->orderBy('created_at', 'DESC')
    ->paginate(10, $page);

// $result->items       - array of rows
// $result->total       - total matching rows
// $result->perPage     - items per page
// $result->currentPage - current page (1-based)
// $result->totalPages  - total pages

Aggregates

$count = DB::table('users')->where('active', true)->count();
$exists = DB::table('users')->where('name', 'John')->exists();
$sum = DB::table('orders')->sum('amount');
$avg = DB::table('orders')->avg('amount');
$min = DB::table('products')->min('price');
$max = DB::table('products')->max('price');

INSERT

$id = DB::table('users')->insert([
    'name'  => 'John',
    'email' => 'john@example.com',
]);

UPDATE

$affected = DB::table('users')
    ->where('id', 42)
    ->update(['name' => 'Jane']);

DELETE

$affected = DB::table('users')->where('id', 42)->delete();

Increment / Decrement

DB::table('users')->where('id', 42)->increment('views');
DB::table('users')->where('id', 42)->increment('views', 5);
DB::table('users')->where('id', 42)->decrement('score', 10);

Raw Expressions

DB::table('users')->update(['updated_at' => DB::raw('NOW()')]);

Transactions

DB::beginTransaction();
try {
    DB::table('users')->insert(['name' => 'John']);
    DB::table('accounts')->where('user_id', 1)->increment('balance', 100);
    DB::commit();
} catch (\PDOException $e) {
    DB::rollBack();
}

SQL Inspection

$sql = DB::table('users')->where('active', true)->toSql();
// SELECT * FROM users WHERE active = ?

Single-Use Builders

Terminal methods (get, insert, update, delete, count, etc.) reset the builder state. Create a fresh instance for each query.

Security

Column and table names passed to where(), orWhere(), join(), orderBy(), groupBy(), having(), increment(), and decrement() are validated against a strict whitelist (^[a-zA-Z_][a-zA-Z0-9_]*). Values are always bound via prepared statements. Column lists in select() and aggregate expressions are trusted — only pass developer-controlled values.