Query Builder Guide

This guide covers in detail how to interact with the database using the **Hybrid Active Record** pattern (Constitution Section 2) in the phpLiteCore framework.

The mechanism relies on BaseModel.php which utilizes BaseQueryBuilder.php and a set of Traits (QueryBuilderWhereTraits, QueryBuilderLikeTraits) to provide a fluent interface.

1. Retrieving Data (SELECT)

all, find, first

These are the basic methods for fetching records.

// Get all records from the 'users' table
$users = User::all();

// Find a record by its primary key (e.g., 'id')
$user = User::find(1);

// Get the *first* record matching a query
$user = User::where('status', 'active')->first();

Building Queries (Chaining)

You can build complex queries by chaining methods together, terminating with get() (to fetch multiple records) or first() (to fetch a single record).

$activeAdmins = User::where('role', 'admin')
                     ->where('status', 'active')
                     ->orderBy('created_at', 'DESC')
                     ->limit(10)
                     ->get();

foreach ($activeAdmins as $admin) {
    echo $admin->name;
}

Aggregates

The builder currently provides a count() method.

// Get the total count of all users
$totalUsers = User::count();

// Get the count of users matching a WHERE clause
$adminCount = User::where('role', 'admin')->count();

Note: The found() method is deprecated and will be removed in a future release. Please use count() instead.

Pagination (Paginate)

The paginate() method automatically calculates the total, slices the results, and fetches items for the current page.

// Inside PostController::index()
$itemsPerPage = 5;
$currentPage = $_GET['page'] ?? 1;

// This returns a structured array: ['paginator' => Paginator, 'items' => [Post, ...]]
$paginationData = Post::orderBy('created_at', 'DESC')
                      ->paginate($itemsPerPage, $currentPage);

// Get the post objects for the current page
$posts = $paginationData['items'];

// Get the Paginator object to render links
$paginator = $paginationData['paginator'];

2. WHERE Clauses (Full)

The query builder offers a flexible interface for constructing complex WHERE clauses, thanks to QueryBuilderWhereTraits.php.

Where (Basic & Array)

The simplest form of where accepts a column and value (implicitly assuming '='), or a column, operator, and value.

// WHERE `role` = 'admin'
$admins = User::where('role', 'admin')->get();

// WHERE `points` > 100
$highScoreUsers = User::where('points', '>', 100)->get();

You can also pass an array to create multiple AND conditions using the '=' operator.

// WHERE `role` = 'admin' AND `status` = 'active'
$activeAdmin = User::where([
    'role' => 'admin',
    'status' => 'active'
])->first();

orWhere

To chain conditions using OR.

$users = User::where('status', 'active')
                     ->orWhere('role', 'admin')
                     ->get();
// SQL: SELECT * FROM `users` WHERE `status` = ? OR `role` = ?

Where (Nested - Groups)

To group constraints within parentheses (...), pass a Closure to where or orWhere. The Closure will receive a new query builder instance that you can use to add the nested constraints.

// WHERE `status` = 'active' AND (`role` = 'admin' OR `role` = 'moderator')
$users = User::where('status', 'active')
                     ->where(function ($query) {
                         $query->where('role', 'admin')
                               ->orWhere('role', 'moderator');
                     })
                     ->get();

Use orWhere with a Closure to create an OR (...) group.

// WHERE `status` = 'active' OR (`created_at` > '2025-01-01' AND `role` = 'guest')
$users = User::where('status', 'active')
                     ->orWhere(function ($query) {
                         $query->where('created_at', '>', '2025-01-01')
                               ->where('role', 'guest');
                     })
                     ->get();

whereIn / whereNotIn

To check if a column's value is within an array of values.

$ids = [1, 5, 10];

// WHERE `id` IN (1, 5, 10)
$users = User::whereIn('id', $ids)->get();

// WHERE `status` NOT IN ('banned', 'pending')
$users = User::whereNotIn('status', ['banned', 'pending'])->get();

// You can also use orWhereIn / orWhereNotIn
$users = User::where('active', 1)
                     ->orWhereIn('role', ['admin', 'superadmin'])->get();

whereBetween / orWhereBetween

To check if a column's value is within a given range.

// WHERE `points` BETWEEN 100 AND 200
$users = User::whereBetween('points', 100, 200)->get();

// WHERE `level` < 10 OR `points` BETWEEN 500 AND 1000
$users = User::where('level', '<', 10)
                     ->orWhereBetween('points', 500, 1000)->get();

3. LIKE Clauses

QueryBuilderLikeTraits.php provides helper methods for common LIKE operations.

whereStarts

// WHERE `name` LIKE 'Jo%'
$users = User::whereStarts('name', 'Jo')->get();

whereHas (Contains)

// WHERE `email` LIKE '%@example.com%'
$users = User::whereHas('email', '@example.com')->get();

whereEnds

// WHERE `filename` LIKE '%.pdf'
$files = File::whereEnds('filename', '.pdf')->get();

These methods also support passing an array to search for multiple values using OR.

// WHERE (`email` LIKE '%@yahoo.com%' OR `email` LIKE '%@hotmail.com%')
$users = User::whereHas('email', ['@yahoo.com', '@hotmail.com'])->get();

4. Insert, Update, Delete

Creating (Insert)

Create a new Model() instance and call save().

// 1. Create a new Post object
$post = new Post([
    'title'   => 'New Post Title',
    'body'    => 'This is the post body.',
    'user_id' => 1,
]);

// 2. Save (executes INSERT)
$post->save();

// The $post object now contains the new 'id'
echo "New post created with ID: " . $post->id;

Updating (Update)

Use find(), modify properties, then call save().

// 1. Find an existing post
$post = Post::find(10);

if ($post) {
    // 2. Modify properties
    $post->title = 'This is the Updated Title';
    $post->status = 'published';

    // 3. Save (executes UPDATE only for changed fields)
    $post->save();
}

Deleting (Delete)

Use the static delete() method chained after a where() clause.

// Delete a specific post by ID
$affectedRows = Post::where('id', '=', 10)->delete();

// Delete all posts by a specific user
$affectedRows = Post::where('user_id', 5)->delete();

5. Raw Queries

For very complex scenarios not covered by the builder, you can use the Database instance directly for raw queries.

// Use the '$app->db' service instance

// SELECT query with bindings
$sql = "SELECT * FROM users WHERE role = ? AND status = ?";
$bindings = ['admin', 'active'];

$statement = $this->app->db->raw($sql, $bindings);
$results = $statement->fetchAll(\PDO::FETCH_ASSOC);

// INSERT/UPDATE query (returns affected row count)
$sql = "UPDATE users SET last_login = NOW() WHERE id = ?";
$statement = $this->app->db->raw($sql, [1]);
$affected = $statement->rowCount();