Query Builder

API reference for VelvetCMS\Database\QueryBuilder.

Database

Namespace: VelvetCMS\Database

This page is the compact API reference. For day-to-day examples and common query patterns, use the main Query Builder guide.

Definition #

class QueryBuilder
{
    public function __construct(Connection $connection, ?CacheDriver $cache = null);
    
    // Table selection
    public function table(string $table): self;
    
    // Select
    public function select(string|array|RawExpression $columns = '*'): self;
    public function selectRaw(string $expression, array $bindings = []): self;
    
    // Where clauses
    public function where(string|RawExpression $column, string $operator, mixed $value): self;
    public function orWhere(string $column, string $operator, mixed $value): self;
    public function whereIn(string $column, array $values): self;
    public function whereNull(string $column): self;
    public function whereNotNull(string $column): self;
    public function whereRaw(string $expression, array $bindings = []): self;
    
    // Joins
    public function join(string $table, string $first, string $operator, string $second): self;
    public function leftJoin(string $table, string $first, string $operator, string $second): self;
    public function rightJoin(string $table, string $first, string $operator, string $second): self;
    
    // Grouping and ordering
    public function groupBy(string ...$columns): self;
    public function having(string $column, string $operator, mixed $value): self;
    public function orderBy(string|RawExpression $column, string $direction = 'ASC'): self;
    public function orderByRaw(string $expression, array $bindings = []): self;
    
    // Pagination
    public function limit(int $limit): self;
    public function offset(int $offset): self;
    
    // Caching
    public function cache(int $ttl = 300): self;
    
    // Execution
    public function get(): Collection;
    public function first(): ?array;
    public function find(int|string $id): ?array;
    public function pluck(string $column): Collection;
    public function count(): int;
    public function exists(): bool;
    public function paginate(int $perPage = 15, int $page = 1): array;
    
    // Mutations
    public function insert(array $data): bool;
    public function insertGetId(array $data): int;
    public function upsert(array $data, string|array $uniqueBy, ?array $update = null): bool;
    public function update(array $data): int;
    public function delete(): int;
    
    // Debug
    public function toSql(): string;
}

Notes #

select() accepts strings, arrays, and RawExpression instances.
where() accepts either a plain column reference or a RawExpression.
orderBy() stores a single active sort. A later call replaces the earlier one.
cache() caches the final SQL plus bindings as a single cache key.
paginate() returns an array with data, total, per_page, current_page, and last_page.
upsert() is implemented for SQLite, MySQL, and PostgreSQL.

Method groups #

Selecting and filtering #

table(string $table): self
select(string|array|RawExpression $columns = '*'): self
selectRaw(string $expression, array $bindings = []): self
where(string|RawExpression $column, string $operator, mixed $value): self
orWhere(string $column, string $operator, mixed $value): self
whereIn(string $column, array $values): self
whereNull(string $column): self
whereNotNull(string $column): self
whereRaw(string $expression, array $bindings = []): self

Joins, grouping, and sort order #

join(string $table, string $first, string $operator, string $second): self
leftJoin(string $table, string $first, string $operator, string $second): self
rightJoin(string $table, string $first, string $operator, string $second): self
groupBy(string ...$columns): self
having(string $column, string $operator, mixed $value): self
orderBy(string|RawExpression $column, string $direction = 'ASC'): self
orderByRaw(string $expression, array $bindings = []): self
limit(int $limit): self
offset(int $offset): self

Execution and helpers #

cache(int $ttl = 300): self
get(): Collection
first(): ?array
find(int|string $id): ?array
pluck(string $column): Collection
count(): int
exists(): bool
paginate(int $perPage = 15, int $page = 1): array

Mutations and debugging #

insert(array $data): bool
insertGetId(array $data): int
upsert(array $data, string|array $uniqueBy, ?array $update = null): bool
update(array $data): int
delete(): int
toSql(): string

Example #

$result = db()->table('posts')
    ->where('status', '=', 'published')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->first();

get() #

Get all matching records as a Collection:

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

foreach ($users as $user) {
    echo $user['name'];
}

first() #

Get the first matching record:

$user = $db->table('users')
    ->where('email', '=', 'john@example.com')
    ->first();

if ($user) {
    echo $user['name'];
}

find() #

Find by primary key:

$user = $db->table('users')->find(1);

pluck() #

Get a single column as a Collection:

$emails = $db->table('users')
    ->where('status', '=', 'active')
    ->pluck('email');

// ['john@example.com', 'jane@example.com', ...]

count() #

Count matching records:

$total = $db->table('users')
    ->where('status', '=', 'active')
    ->count();

exists() #

Check if any records match:

$hasAdmin = $db->table('users')
    ->where('role', '=', 'admin')
    ->exists();

paginate() #

Offset-based pagination with metadata:

/** @return array{data: Collection, total: int, per_page: int, current_page: int, last_page: int} */
public function paginate(int $perPage = 15, int $page = 1): array

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

$result['data'];         // Collection of rows for page 2
$result['total'];        // Total matching rows
$result['per_page'];     // 15
$result['current_page']; // 2
$result['last_page'];    // Calculated from total / per_page

The count query automatically strips ORDER BY for efficiency. $page is clamped to a minimum of 1.

Inserting Data #

insert() #

$db->table('users')->insert([
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'password' => password_hash('secret', PASSWORD_DEFAULT),
    'created_at' => date('Y-m-d H:i:s'),
]);

insertGetId() #

Insert a record and return the auto-generated primary key:

$id = $db->table('posts')->insertGetId([
    'title' => 'Hello World',
    'slug' => 'hello-world',
    'content' => '...',
    'created_at' => date('Y-m-d H:i:s'),
]);
// $id = 42

upsert() #

Insert or update on conflict:

// Insert or update based on email
$db->table('users')->upsert(
    [
        'email' => 'john@example.com',
        'name' => 'John Doe',
        'updated_at' => date('Y-m-d H:i:s'),
    ],
    'email',  // Unique column(s)
    ['name', 'updated_at']  // Columns to update on conflict
);

// Multiple unique columns
$db->table('user_settings')->upsert(
    ['user_id' => 1, 'key' => 'theme', 'value' => 'dark'],
    ['user_id', 'key'],
    ['value']
);

Updating Data #

update() #

$affected = $db->table('users')
    ->where('id', '=', 1)
    ->update([
        'name' => 'Jane Doe',
        'updated_at' => date('Y-m-d H:i:s'),
    ]);

echo "Updated {$affected} rows";

Deleting Data #

delete() #

$deleted = $db->table('sessions')
    ->where('expires_at', '<', date('Y-m-d H:i:s'))
    ->delete();

echo "Deleted {$deleted} expired sessions";

Query Caching #

cache() #

Cache query results:

$posts = $db->table('posts')
    ->where('status', '=', 'published')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->cache(300)  // Cache for 5 minutes
    ->get();

Raw Expressions #

Use raw() helper for complex expressions:

$posts = $db->table('posts')
    ->select(raw('*, DATEDIFF(NOW(), created_at) as days_ago'))
    ->where(raw('YEAR(created_at)'), '=', 2024)
    ->orderBy(raw('RAND()'))
    ->limit(5)
    ->get();

Debug #

toSql() #

Get the generated SQL:

$sql = $db->table('users')
    ->where('status', '=', 'active')
    ->orderBy('name')
    ->toSql();

// SELECT * FROM users WHERE status = ? ORDER BY name ASC

Usage Examples #

Paginated List #

public function list(Request $request): Response
{
    $page = (int) $request->query('page', 1);
    
    $result = db()->table('posts')
        ->where('status', '=', 'published')
        ->orderBy('created_at', 'DESC')
        ->paginate(perPage: 20, page: $page);
    
    return Response::json([
        'data' => $result['data']->toArray(),
        'meta' => [
            'page' => $result['current_page'],
            'per_page' => $result['per_page'],
            'total' => $result['total'],
            'pages' => $result['last_page'],
        ]
    ]);
}

Search with Filters #

public function search(Request $request): Collection
{
    $query = db()->table('products');
    
    if ($search = $request->input('q')) {
        $query->whereRaw('name LIKE ?', ["%{$search}%"]);
    }
    
    if ($category = $request->input('category')) {
        $query->where('category_id', '=', $category);
    }
    
    if ($minPrice = $request->input('min_price')) {
        $query->where('price', '>=', $minPrice);
    }
    
    if ($maxPrice = $request->input('max_price')) {
        $query->where('price', '<=', $maxPrice);
    }
    
    $sort = $request->input('sort', 'created_at');
    $order = $request->input('order', 'desc');
    
    return $query->orderBy($sort, $order)->get();
}

Dashboard Statistics #

public function stats(): array
{
    $db = db();
    
    $userStats = $db->table('users')
        ->selectRaw('COUNT(*) as total, COUNT(CASE WHEN status = "active" THEN 1 END) as active')
        ->first();
    
    $revenueStats = $db->table('orders')
        ->selectRaw('SUM(total) as revenue, COUNT(*) as orders')
        ->whereRaw('created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)')
        ->first();
    
    return [
        'users' => $userStats,
        'revenue' => $revenueStats,
    ];
}

← Previous HTTP Client