Query Builder - Lyger PHP 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.

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.

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.

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', '=', 'john@example.com')
    ->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.

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.

string

required

The column name

string

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

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', 'john@example.com')
    ->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.

string

required

The column name

string

Comparison operator

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.

string

required

The column name

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.

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.

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.

string

required

The column to order by

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.

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.

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.

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.

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.

int

default:"15"

Number of items per 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

array

The paginated results for the current page

int

The current page number

int

Number of items per page

int

Total number of records in the dataset

int

The last page number (total pages)

int

The starting record number for the current page

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.

string

required

The table to join

string

required

First column in the join condition

string

required

Comparison operator (usually ’=’)

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.

string

required

The table to join

string

required

First column in the join condition

string

required

Comparison operator (usually ’=’)

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.

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' => 'john@example.com',
    'created_at' => date('Y-m-d H:i:s')
]);

update()

public function update(array $data): int

Update records matching the query.

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' => 'admin@example.com',
        '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

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

​Overview

​Class Reference

​Lyger\Database\QueryBuilder

​Creating Query Builders

​Constructor

​table()

​Select Methods

​select()

​get()

​first()

​value()

​Where Clauses

​where()

​orWhere()

​whereIn()

​whereNull()

​whereNotNull()

​Ordering and Limiting

​orderBy()

​latest()

​oldest()

​limit()

​offset()

​Pagination

​paginate()

​Joins

​join()

​leftJoin()

​Aggregates

​count()

​exists()

​Insert, Update, Delete

​insert()

​update()

​delete()

​Usage Examples

​Complex Query

​Join with Conditions

​Conditional Updates

​Bulk Operations

​Best Practices

​Connection Management

​Related

Overview

Class Reference

Lyger\Database\QueryBuilder

Creating Query Builders

Constructor

table()

Select Methods

select()

get()

first()

value()

Where Clauses

where()

orWhere()

whereIn()

whereNull()

whereNotNull()

Ordering and Limiting

orderBy()

latest()

oldest()

limit()

offset()

Pagination

paginate()

Joins

join()

leftJoin()

Aggregates

count()

exists()

Insert, Update, Delete

insert()

update()

delete()

Usage Examples

Complex Query

Join with Conditions

Conditional Updates

Bulk Operations

Best Practices

Connection Management

Related