Query Builder

Build SQL queries with a fluent, expressive interface

Introduction

The Lyger Query Builder provides a fluent interface for building and executing database queries. It’s inspired by Laravel’s Query Builder and offers a clean, readable syntax for working with databases.

Basic Usage

Creating a Query Builder Instance

You can create a QueryBuilder instance in two ways:

use Lyger\Database\QueryBuilder;

// Using the static table method
$users = QueryBuilder::table('users')->get();

// Using the constructor
$query = new QueryBuilder('users');
$users = $query->get();

Select Queries

Retrieving All Records

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

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

Selecting Specific Columns

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

Getting a Single Record

// Get first matching record
$user = QueryBuilder::table('users')
    ->where('id', '=', 1)
    ->first();

// Get a single column value
$email = QueryBuilder::table('users')
    ->where('id', '=', 1)
    ->value('email');

The first() method returns null if no record is found, while value() returns null if the column doesn’t exist.

Where Clauses

Basic Where Clauses

// Basic where with operator
$users = QueryBuilder::table('users')
    ->where('status', '=', 'active')
    ->get();

// Shorthand (defaults to '=' operator)
$users = QueryBuilder::table('users')
    ->where('status', 'active')
    ->get();

// Multiple where conditions
$users = QueryBuilder::table('users')
    ->where('status', '=', 'active')
    ->where('role', '=', 'admin')
    ->get();

Or Where Clauses

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

Where In Clauses

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

Null Checks

// Where column is NULL
$users = QueryBuilder::table('users')
    ->whereNull('deleted_at')
    ->get();

// Where column is NOT NULL
$users = QueryBuilder::table('users')
    ->whereNotNull('email_verified_at')
    ->get();

Ordering

Order By

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

// Descending order
$users = QueryBuilder::table('users')
    ->orderBy('created_at', 'DESC')
    ->get();

Latest and Oldest

Convenient methods for ordering by timestamps:

// Order by created_at DESC
$users = QueryBuilder::table('users')
    ->latest()
    ->get();

// Order by created_at ASC
$users = QueryBuilder::table('users')
    ->oldest()
    ->get();

// Use a different column
$users = QueryBuilder::table('posts')
    ->latest('published_at')
    ->get();

Limiting and Offset

Limit

// Get first 10 records
$users = QueryBuilder::table('users')
    ->limit(10)
    ->get();

Offset

// Skip first 10 records, get next 10
$users = QueryBuilder::table('users')
    ->offset(10)
    ->limit(10)
    ->get();

Pagination

The paginate() method provides automatic pagination:

$result = QueryBuilder::table('users')
    ->where('status', '=', 'active')
    ->paginate(15, 1); // 15 per page, page 1

// Result structure:
// [
//     'data' => [...],           // Array of records
//     'current_page' => 1,
//     'per_page' => 15,
//     'total' => 150,
//     'last_page' => 10,
//     'from' => 1,
//     'to' => 15
// ]

perPage · int · default: "15"

Number of records per page

page · int · default: "1"

Current page number

Joins

Inner Join

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

Left Join

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

Aggregates

Count

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

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

Exists

$hasUsers = QueryBuilder::table('users')
    ->where('role', '=', 'admin')
    ->exists(); // Returns bool

Insert Queries

Inserting Records

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

// Returns: true on success, false on failure

The insert() method returns a boolean indicating success. The last insert ID is managed automatically by the underlying PDO connection.

Update Queries

Updating Records

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

// Returns: number of affected rows

Update with Limit

$affected = QueryBuilder::table('users')
    ->where('status', '=', 'pending')
    ->limit(10)
    ->update(['status' => 'processed']);

Delete Queries

Deleting Records

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

// Returns: number of affected rows

Delete with Limit

$affected = QueryBuilder::table('logs')
    ->where('level', '=', 'debug')
    ->limit(100)
    ->delete();

Be careful when using delete() without a where() clause, as it will delete all records from the table.

Method Chaining

All Query Builder methods support fluent chaining:

$users = QueryBuilder::table('users')
    ->select(['id', 'name', 'email', 'created_at'])
    ->where('status', '=', 'active')
    ->whereNotNull('email_verified_at')
    ->orderBy('created_at', 'DESC')
    ->limit(20)
    ->offset(0)
    ->get();

Parameter Binding

The Query Builder automatically handles parameter binding to prevent SQL injection:

// Parameters are safely bound
$users = QueryBuilder::table('users')
    ->where('email', '=', $userInput)
    ->where('status', '=', $statusInput)
    ->get();

// Even with whereIn
$users = QueryBuilder::table('users')
    ->whereIn('id', $userProvidedIds)
    ->get();

All user-provided values are automatically escaped and bound as parameters. You never need to manually escape values.

Complete Example

Here’s a comprehensive example combining multiple Query Builder features:

use Lyger\Database\QueryBuilder;

// Complex query with multiple conditions
$posts = QueryBuilder::table('posts')
    ->select(['posts.*', 'users.name as author_name'])
    ->join('users', 'posts.user_id', '=', 'users.id')
    ->where('posts.status', '=', 'published')
    ->whereNotNull('posts.published_at')
    ->whereIn('posts.category_id', [1, 2, 3])
    ->orderBy('posts.published_at', 'DESC')
    ->limit(10)
    ->get();

// Pagination example
$result = QueryBuilder::table('products')
    ->where('stock', '>', 0)
    ->where('active', '=', true)
    ->orderBy('name', 'ASC')
    ->paginate(20, $_GET['page'] ?? 1);

// Insert with error handling
try {
    $success = QueryBuilder::table('orders')->insert([
        'user_id' => $userId,
        'total' => $total,
        'status' => 'pending',
        'created_at' => date('Y-m-d H:i:s')
    ]);
    
    if ($success) {
        echo "Order created successfully";
    }
} catch (\PDOException $e) {
    echo "Failed to create order: " . $e->getMessage();
}

Next Steps

Eloquent Models

Learn about the Eloquent ORM layer built on top of Query Builder

Migrations

Manage your database schema with migrations