StackCTL -Query, Lady of the Database

Query

Query is StackCTL's built-in database layer. It gives you a clean, fluent API for reading and writing to your database — no raw SQL in controllers, no ORM magic, no hidden behavior. Every method is explicit and traceable.

Query uses the shared PDO connection established during bootstrap, so no second connection is opened and no configuration is required beyond config/database.php.

Every query starts with:

Query::table('your_table')

From there, you chain the methods you need and end with a terminal call like get(), first(), insert(), etc.

Reading Data

get() — Fetch all matching rows

Returns an array of result objects. Returns an empty array if nothing is found.

$articles = Query::table('articles')->get();

// With filters and sorting
$articles = Query::table('articles')
    ->where('status', 'published')
    ->orderBy('created_at', 'DESC')
    ->get();

first() — Fetch the first matching row

Returns a single object, or null if nothing matches. Always check before using.

$user = Query::table('users')
    ->where('email', $email)
    ->first();

if (!$user) {
    abort(404);
}

find() — Fetch by primary key

Shorthand for ->where('id', $id)->first().

$article = Query::table('articles')->find(5);

count() — Count matching rows

Runs COUNT(*) efficiently without fetching full rows.

$total = Query::table('articles')->count();

$published = Query::table('articles')
    ->where('status', 'published')
    ->count();

Filtering

where()

Filter rows by a column value. The default operator is =. Chain multiple where() calls — they are combined with AND.

// Simple equality
->where('role', 'admin')

// Custom operator
->where('created_at', '>', '2024-01-01')
->where('price', '<=', 100)
->where('name', 'LIKE', '%john%')

// NULL checks
->where('verified_at', '!=', null)  // IS NOT NULL
->where('deleted_at', null)          // IS NULL

// Chained (AND logic)
$users = Query::table('users')
    ->where('role', 'editor')
    ->where('is_active', 1)
    ->get();

whereIn()

Filter rows where a column matches any value in a list.

$articles = Query::table('articles')
    ->whereIn('status', ['published', 'featured'])
    ->get();

Sorting, Limiting & Offsetting

// Sort newest first
->orderBy('created_at', 'DESC')

// Alphabetical (ASC is the default)
->orderBy('name')

// Limit results
->limit(10)

// Skip rows (for manual pagination)
->limit(10)->offset(20)  // page 3 of 10-per-page

Selecting Specific Columns

By default all columns are returned. Use select() to limit what comes back — useful for large tables or when you don't need every field.

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

Writing Data

insert() — Create a new row

Pass an associative array of column/value pairs. Returns the ID of the newly created row.

$id = Query::table('articles')->insert([
    'title'      => 'My First Article',
    'slug'       => 'my-first-article',
    'status'     => 'draft',
    'created_by' => auth('id'),
]);

update() — Update matching rows

Always chain a where() first. Calling update() without a condition will throw an exception to prevent accidentally updating every row.

Query::table('articles')
    ->where('id', $id)
    ->update([
        'title'  => 'Updated Title',
        'status' => 'published',
    ]);

delete() — Delete matching rows

Always chain a where() first. Same protection applies — a missing condition throws an exception.

Query::table('articles')
    ->where('id', $id)
    ->delete();

Pagination

paginate() reads the current page from ?page= in the URL and automatically applies the correct LIMIT and OFFSET. It returns a pagination object instead of a plain array.

// In your controller
$results = Query::table('articles')
    ->where('status', 'published')
    ->orderBy('created_at', 'DESC')
    ->paginate(15);  // 15 records per page

return $this->view('app.articles.index', [
    'articles' => $results,
], 'layouts.app');

In your view, loop through $articles->data and render the pagination links with the paginate() helper:

<?php foreach ($articles->data as $article): ?>
    <!-- render each article -->
<?php endforeach; ?>

<?= paginate($articles) ?>

The pagination object also exposes metadata if you need it:

$results->total        // total record count
$results->perPage      // records per page
$results->currentPage  // current page number
$results->lastPage     // total number of pages
$results->hasNext      // bool — is there a next page?
$results->hasPrev      // bool — is there a previous page?

Relationships

user() — Attach a related user

The most common relationship. Use when a table has a column referencing the users table (like created_by). Runs as a LEFT JOIN so rows are still returned if the foreign key is null.

// Attach a single field from the users table
$articles = Query::table('articles')
    ->user('created_by', 'display_name')
    ->get();

// Access the joined value on each result:
echo $article->created_by_display_name;

// Attach the full user row (runs a separate query per row)
->user('created_by', '*');
echo $article->created_by_user->email;

with() — Attach other related tables

For relationships to tables other than users, pass a map of foreign_key => 'table.column'.

$articles = Query::table('articles')
    ->with([
        'category_id' => 'categories.name',
        'location_id' => 'locations.city',
    ])
    ->get();

echo $article->category_id_name;
echo $article->location_id_city;

Transactions

Use transactions when multiple writes must all succeed or all fail together. Always wrap in a try/catch and call rollback() if anything throws.

try {
    Query::begin();

    $userId = Query::table('users')->insert([
        'email' => $email,
        'role'  => 'contributor',
    ]);

    Query::table('email_verifications')->insert([
        'user_id' => $userId,
        'token'   => bin2hex(random_bytes(32)),
    ]);

    Query::commit();

} catch (\Throwable $e) {
    Query::rollback();
    throw $e;
}

Query, Lady of the Database