Query Builder

Fluent SQL query builder for Lyger framework

Overview

The QueryBuilder class provides a fluent, convenient interface for building and executing database queries. It supports SELECT, INSERT, UPDATE, and DELETE operations with method chaining for a clean, readable syntax.

Class Reference

Lyger\Database\QueryBuilder

Fluent SQL query builder inspired by Laravel’s query builder.

Location: Lyger/Database/QueryBuilder.php

Creating Query Builders

Constructor

public function __construct(string $table)

Create a new query builder instance for a specific table.

table · string · required

The name of the database table

Example:

$query = new QueryBuilder('users');

table()

public static function table(string $table): self

Static method to create a query builder instance.

table · string · required

The name of the database table

Returns: QueryBuilder instance

Example:

$users = QueryBuilder::table('users')->get();

Select Methods

select()

public function select(array $columns): self

Set the columns to be selected.

columns · array · required

Array of column names to select

Returns: $this for method chaining

Example:

$users = QueryBuilder::table('users')
    ->select(['id', 'name', 'email'])
    ->get();

get()

public function get(): array

Execute the query and get all results.

Returns: Array of associative arrays

Example:

$users = QueryBuilder::table('users')
    ->where('active', '=', 1)
    ->get();

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

first()

public function first(): ?array

Execute the query and get the first result.

Returns: Associative array or null if no results

Example:

$user = QueryBuilder::table('users')
    ->where('email', '=', '[e-mail]')
    ->first();

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

value()

public function value(string $column): mixed

Execute the query and get a single column value from the first result.

column · string · required

The column name to retrieve

Returns: Column value or null

Example:

$name = QueryBuilder::table('users')
    ->where('id', '=', 1)
    ->value('name');

Where Clauses

where()

public function where(string $column, $operator, $value = null): self

Add a WHERE clause to the query. If only two parameters provided, assumes = operator.

column · string · required

The column name

operator · string

Comparison operator (=, !=, >, <, >=, <=, LIKE, etc.)

value · mixed

The value to compare against

Returns: $this for method chaining

Example:

// With explicit operator
$users = QueryBuilder::table('users')
    ->where('age', '>', 18)
    ->get();

// Shorthand for equals
$user = QueryBuilder::table('users')
    ->where('email', '[e-mail]')
    ->first();

// Using LIKE
$users = QueryBuilder::table('users')
    ->where('name', 'LIKE', 'John%')
    ->get();

orWhere()

public function orWhere(string $column, $operator, $value = null): self

Add an OR WHERE clause to the query.

column · string · required

The column name

operator · string

Comparison operator

value · mixed

The value to compare against

Returns: $this for method chaining

Example:

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

whereIn()

public function whereIn(string $column, array $values): self

Add a WHERE IN clause to the query.

column · string · required

The column name

values · array · required

Array of values

Returns: $this for method chaining

Example:

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

whereNull()

public function whereNull(string $column): self

Add a WHERE IS NULL clause to the query.

column · string · required

The column name

Returns: $this for method chaining

Example:

$users = QueryBuilder::table('users')
    ->whereNull('deleted_at')
    ->get();

whereNotNull()

public function whereNotNull(string $column): self

Add a WHERE IS NOT NULL clause to the query.

column · string · required

The column name

Returns: $this for method chaining

Example:

$users = QueryBuilder::table('users')
    ->whereNotNull('email_verified_at')
    ->get();

Ordering and Limiting

orderBy()

public function orderBy(string $column, string $direction = 'ASC'): self

Add an ORDER BY clause to the query.

column · string · required

The column to order by

direction · string · default: "'ASC'"

Sort direction: ‘ASC’ or ‘DESC’

Returns: $this for method chaining

Example:

$users = QueryBuilder::table('users')
    ->orderBy('name', 'ASC')
    ->get();

latest()

public function latest(string $column = 'created_at'): self

Order results by a column in descending order.

column · string · default: "'created_at'"

The column to order by

Returns: $this for method chaining

Example:

$recentUsers = QueryBuilder::table('users')
    ->latest()
    ->get();

oldest()

public function oldest(string $column = 'created_at'): self

Order results by a column in ascending order.

column · string · default: "'created_at'"

The column to order by

Returns: $this for method chaining

Example:

$oldestUsers = QueryBuilder::table('users')
    ->oldest()
    ->get();

limit()

public function limit(int $limit): self

Limit the number of results returned.

limit · int · required

Maximum number of results

Returns: $this for method chaining

Example:

$topUsers = QueryBuilder::table('users')
    ->orderBy('score', 'DESC')
    ->limit(10)
    ->get();

offset()

public function offset(int $offset): self

Skip a specified number of results.

offset · int · required

Number of results to skip

Returns: $this for method chaining

Example:

$users = QueryBuilder::table('users')
    ->limit(10)
    ->offset(20)
    ->get();

Pagination

paginate()

public function paginate(int $perPage = 15, int $page = 1): array

Paginate query results.

perPage · int · default: "15"

Number of items per page

page · int · default: "1"

Current page number

Returns: Pagination array with data and metadata

Example:

$result = QueryBuilder::table('users')
    ->paginate(15, 2);

// Result structure:
// [
//     'data' => [...],           // Array of results
//     'current_page' => 2,
//     'per_page' => 15,
//     'total' => 100,            // Total number of records
//     'last_page' => 7,          // Total number of pages
//     'from' => 16,              // First item number
//     'to' => 30                 // Last item number
// ]

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

Show Pagination Response Fields

data · array

The paginated results for the current page

current_page · int

The current page number

per_page · int

Number of items per page

total · int

Total number of records in the dataset

last_page · int

The last page number (total pages)

from · int

The starting record number for the current page

to · int

The ending record number for the current page

Joins

join()

public function join(string $table, string $first, string $operator, string $second): self

Add an INNER JOIN clause to the query.

table · string · required

The table to join

first · string · required

First column in the join condition

operator · string · required

Comparison operator (usually ’=’)

second · string · required

Second column in the join condition

Returns: $this for method chaining

Example:

$users = QueryBuilder::table('users')
    ->join('profiles', 'users.id', '=', 'profiles.user_id')
    ->select(['users.*', 'profiles.bio'])
    ->get();

leftJoin()

public function leftJoin(string $table, string $first, string $operator, string $second): self

Add a LEFT JOIN clause to the query.

table · string · required

The table to join

first · string · required

First column in the join condition

operator · string · required

Comparison operator (usually ’=’)

second · string · required

Second column in the join condition

Returns: $this for method chaining

Example:

$users = QueryBuilder::table('users')
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->get();

Aggregates

count()

public function count(): int

Get the count of results matching the query.

Returns: Integer count

Example:

$activeUsers = QueryBuilder::table('users')
    ->where('active', '=', 1)
    ->count();

echo "Active users: {$activeUsers}";

exists()

public function exists(): bool

Check if any records exist matching the query.

Returns: true if records exist, false otherwise

Example:

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

if ($hasAdmin) {
    echo "Admin users exist";
}

Insert, Update, Delete

insert()

public function insert(array $data): bool

Insert a new record into the database.

data · array · required

Associative array of column => value pairs

Returns: true on success, false on failure

Example:

$success = QueryBuilder::table('users')->insert([
    'name' => 'John Doe',
    'email' => '[e-mail]',
    'created_at' => date('Y-m-d H:i:s')
]);

update()

public function update(array $data): int

Update records matching the query.

data · array · required

Associative array of column => value pairs to update

Returns: Number of affected rows

Example:

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

echo "Updated {$affected} rows";

delete()

public function delete(): int

Delete records matching the query.

Returns: Number of deleted rows

Example:

$deleted = QueryBuilder::table('users')
    ->where('active', '=', 0)
    ->whereNull('last_login')
    ->delete();

echo "Deleted {$deleted} inactive users";

Always use WHERE clauses with update() and delete() to avoid accidentally modifying/removing all records!

Usage Examples

Complex Query

$users = QueryBuilder::table('users')
    ->select(['id', 'name', 'email', 'role'])
    ->where('active', '=', 1)
    ->where('age', '>=', 18)
    ->whereIn('role', ['admin', 'moderator'])
    ->whereNotNull('email_verified_at')
    ->orderBy('created_at', 'DESC')
    ->limit(50)
    ->get();

Join with Conditions

$ordersWithUsers = QueryBuilder::table('orders')
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->select(['orders.*', 'users.name', 'users.email'])
    ->where('orders.status', '=', 'completed')
    ->where('orders.total', '>', 100)
    ->orderBy('orders.created_at', 'DESC')
    ->get();

Conditional Updates

// Deactivate users without recent logins
$affected = QueryBuilder::table('users')
    ->whereNotNull('last_login')
    ->where('last_login', '<', date('Y-m-d', strtotime('-6 months')))
    ->update([
        'active' => 0,
        'updated_at' => date('Y-m-d H:i:s')
    ]);

Bulk Operations

// Check if any admin exists
if (!QueryBuilder::table('users')->where('role', 'admin')->exists()) {
    // Create first admin
    QueryBuilder::table('users')->insert([
        'name' => 'Admin',
        'email' => '[e-mail]',
        'role' => 'admin',
        'created_at' => date('Y-m-d H:i:s')
    ]);
}

Best Practices

• Always use parameter binding (automatically handled by QueryBuilder) to prevent SQL injection
• Use limit() with potentially large result sets to control memory usage
• Add indexes to columns frequently used in WHERE and JOIN clauses for better performance
• Use exists() instead of count() > 0 when you only need to check if records exist
• Chain method calls for readable, maintainable query code

Connection Management

The QueryBuilder automatically manages database connections using PDO. It connects to a SQLite database by default, located at database/database.sqlite.

Show Connection Details

• Driver: SQLite (PDO)
• Location: database/database.sqlite
• Error Mode: PDO::ERRMODE_EXCEPTION
• Auto-created: Database directory and file created automatically if they don’t exist

Related

• Model - Eloquent-style ORM models
• Schema - Database schema builder
• Migration - Database migration system