Database

Name: Slenix Framework
Author: Slenix

Introduction

Slenix provides a fluent, expressive interface for interacting with databases using raw SQL queries or the built-in ORM — a full Active Record implementation. All database access goes through PDO, with prepared statements used throughout to prevent SQL injection.

Slenix supports two database drivers out of the box:

MySQL (5.7+, 8.0+)
PostgreSQL (13+)

Configuration

Database configuration is stored in Config/app.php under the db_connect key:

php

'db_connect' => [
    'drive'    => env('DB_CONNECTION'),
    'hostname' => env('DB_HOSTNAME'),
    'port'     => env('DB_PORT'),
    'dbname'   => env('DB_NAME'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    'charset'  => env('DB_CHARSET'),
],

dotenv

DB_CONNECTION=mysql
DB_HOSTNAME=localhost
DB_PORT=3306
DB_NAME=my_database
DB_USERNAME=root
DB_PASSWORD=secret
DB_CHARSET=utf8mb4

For PostgreSQL, change the driver and port:

dotenv

DB_CONNECTION=pgsql
DB_PORT=5432

Connections

Connections are managed by Slenix\Supports\Database\Connection, which implements the Singleton pattern — a single PDO instance is reused across the entire request.

php

use Slenix\Supports\Database\Connection;

$pdo   = Connection::getInstance();
$users = Connection::raw('SELECT * FROM users WHERE active = :active', ['active' => 1]);

The PDO instance is configured with ERRMODE_EXCEPTION, FETCH_OBJ, and native prepared statements (EMULATE_PREPARES = false).

Running Queries

Selecting Columns

php

User::all();
User::select(['id', 'name', 'email'])->get();
User::select('id')->addSelect('name')->get();
User::select('email')->distinct()->get();

Where Clauses

php

User::where('active', 1)->get();
User::where('age', '>=', 18)->get();
User::where('role', 'admin')->orWhere('role', 'moderator')->get();

// Grouped conditions
User::where(function ($q) {
    $q->where('role', 'admin')->orWhere('role', 'moderator');
})->where('active', 1)->get();

User::whereIn('role', ['admin', 'editor'])->get();
User::whereNotIn('status', ['banned', 'suspended'])->get();
User::whereBetween('age', 18, 65)->get();
User::whereNotBetween('score', 0, 10)->get();
User::whereNull('deleted_at')->get();
User::whereNotNull('verified_at')->get();
User::whereLike('name', '%João%')->get();
User::whereRaw('YEAR(created_at) = :year', ['year' => 2024])->get();
User::whereColumn('updated_at', '>', 'created_at')->get();

Existence Filters

php

// Users who have at least one published post
User::whereHas('posts', fn($q) => $q->where('status', 'published'))->get();

// Users who have no posts
User::whereDoesntHave('posts')->get();

// OR variant
User::orWhereHas('posts')->get();

// Users with 3 or more posts
User::whereHasCount('posts', '>=', 3)->get();

Ordering, Grouping, and Limiting

php

User::orderBy('name', 'ASC')->get();
User::orderByDesc('created_at')->get();
User::latest()->get();           // ORDER BY created_at DESC
User::oldest()->get();           // ORDER BY created_at ASC
User::inRandomOrder()->get();

User::groupBy('country')->having('count', '>', 5)->get();

User::limit(10)->offset(20)->get();
User::take(10, 3)->get();        // 10 per page, page 3
User::forPage(3, 10)->get();

Joins

php

User::join('profiles', 'users.id', '=', 'profiles.user_id')->get();
User::leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->select(['users.*', 'orders.total'])
    ->get();
User::rightJoin('orders', 'users.id', '=', 'orders.user_id')->get();

Aggregates

php

User::count();
User::where('active', 1)->avg('age');
User::max('score');
User::min('score');
User::where('role', 'customer')->sum('balance');
User::where('email', 'a@b.com')->exists();
User::where('email', 'a@b.com')->doesntExist();

Retrieving Results

php

User::where('active', 1)->get();            // Collection
User::where('email', 'a@b.com')->first();   // Model or null
User::where('email', 'a@b.com')->firstOrFail(); // Model or RuntimeException
User::where('email', 'a@b.com')->sole();    // Exactly one or RuntimeException
User::where('id', 1)->value('name');
User::pluck('name');
User::pluck('name', 'id');                  // ['1' => 'Alice', '2' => 'Bob']
User::allArray();
User::firstArray();

Conditional Clauses

Apply constraints only when a condition is true, keeping controller code clean:

php

User::newQuery()
    ->when($request->has('search'), fn($q) => $q->whereLike('name', "%{$search}%"))
    ->when($request->has('role'),   fn($q) => $q->where('role', $request->get('role')))
    ->get();

// unless() is the inverse of when()
User::newQuery()
    ->unless($showAll, fn($q) => $q->where('active', 1))
    ->get();

Pagination

php

$result = User::where('active', 1)->paginate(15, $page);

// $result contains:
// data, current_page, per_page, total, last_page, from, to, has_more

Query Caching

Cache query results directly on the query builder, integrated with the Cache class:

php

// Cache for 5 minutes
$users = User::where('active', 1)->remember(300)->get();

// Cache indefinitely
$settings = Setting::all()->rememberForever()->get();

// Custom cache key
$users = User::latest()->remember(60, 'recent_users')->get();

// Invalidate cached query
User::where('active', 1)->remember(300)->flushCache();

Large Datasets

php

// Generator — one model at a time, minimal memory
foreach (User::where('active', 1)->cursor() as $user) {
    // process $user
}

// Lazy — yields Collection chunks
foreach (User::lazy(200) as $chunk) {
    $chunk->each(fn($user) => processUser($user));
}

// Chunk by PK range — efficient on large tables (no OFFSET scan)
User::chunkById(500, function (Collection $chunk) {
    $chunk->each(fn($u) => sendEmail($u));
});

// Iterate via chunkById per model
User::where('active', 1)->eachById(function (User $user) {
    $user->update(['score' => computeScore($user)]);
});

Debugging Queries

php

User::where('active', 1)->toSql();          // Generated SQL string
User::where('active', 1)->getBindings();    // Bound parameters array
User::where('active', 1)->toRawSql();       // SQL with values interpolated (display only)
User::where('active', 1)->explainSql();     // EXPLAIN output
User::where('active', 1)->log('MyQuery');   // Write to Log at DEBUG level
User::where('active', 1)->dump();           // var_dump SQL + bindings, continue
User::where('active', 1)->dd();             // var_dump SQL + bindings, die

Models

Generating a Model

bash

php celestial make:model User

Table names are auto-inferred in snake_case plural:

Model	Table
`User`	`users`
`BlogPost`	`blog_posts`
`OrderItem`	`order_items`

Defining a Model

php

namespace App\Models;

use Slenix\Supports\Database\Model;

class User extends Model
{
    protected string $table      = 'users';
    protected string $primaryKey = 'id';
    protected bool   $incrementing = false;   // for UUID keys
    protected string $keyType    = 'string';
    protected bool   $timestamps = true;
    protected bool   $softDelete = false;

    protected array $hidden   = ['password', 'remember_token'];
    protected array $fillable = ['name', 'email', 'password'];
    protected array $guarded  = ['is_admin'];
    protected array $appends  = ['full_name'];

    protected array $casts = [
        'is_active'  => 'boolean',
        'settings'   => 'json',
        'score'      => 'float',
        'born_at'    => 'date',
        'created_at' => 'datetime',
    ];

    public function getFullNameAttribute(): string
    {
        return $this->attributes['first_name'] . ' ' . $this->attributes['last_name'];
    }

    public function setPasswordAttribute(string $value): void
    {
        $this->attributes['password'] = password_hash($value, PASSWORD_BCRYPT);
    }
}

Available Cast Types

Cast	PHP Type	Notes
`int` / `integer`	`int`
`float` / `double`	`float`
`bool` / `boolean`	`bool`	Accepts `1`, `'true'`, `'yes'`, `'on'`
`string`	`string`
`json` / `array`	`array`	JSON decoded on read, re-encoded on save
`collection`	`Collection`	JSON decoded into a Collection
`datetime`	`DateTime`	Full datetime
`date`	`DateTime`	Date only — serialized as `Y-m-d`
`money`	`float`	Integer cents → decimal (e.g. `1099` → `10.99`)

Retrieving Models

php

User::all();
User::find(1);
User::findOrFail(1);
User::findMany([1, 2, 3]);
User::firstWhere('email', 'a@b.com');
User::first();
User::last();
User::count();
User::exists();

Inserting and Updating

php

// Create
$user = User::create(['name' => 'Cláudio', 'email' => 'c@example.com']);

// Save
$user = new User();
$user->name = 'Cláudio';
$user->save();

// Update
User::find(1)->update(['name' => 'Novo Nome']);

// updateOrCreate / firstOrCreate / firstOrNew
User::updateOrCreate(['email' => 'a@b.com'], ['name' => 'Cláudio']);
User::firstOrCreate(['email' => 'a@b.com'], ['name' => 'Cláudio']);
User::firstOrNew(['email' => 'a@b.com'], ['name' => 'Cláudio']);

Atomic Column Operations

Increment and decrement columns directly in the database without loading the model:

php

$post->increment('views');          // +1
$post->increment('score', 5);       // +5
$product->decrement('stock');       // -1
$product->decrement('stock', 2, floor: true); // -2, never below 0

Touch

Updates only the updated_at timestamp without triggering the full save lifecycle:

php

$post->touch();

Deleting

php

User::find(1)->delete();
User::where('active', 0)->delete();
User::truncate();

Soft Deletes

php

class Post extends Model
{
    protected bool $softDelete = true; // column: deleted_at
}

Post::withTrashed()->where('user_id', 1)->get();
Post::onlyTrashed()->get();
Post::withTrashed()->find(1)->restore();
Post::withTrashed()->find(1)->forceDelete();

Checking State

php

$user->isDirty();            // any field changed
$user->isDirty('name');      // specific field changed
$user->isClean('email');     // specific field not changed
$user->getDirty();           // array of changed fields
$user->wasChanged('name');   // changed since last save()
$user->original('name');     // value before last change
$user->isNew();              // not yet saved (no PK)
$user->refresh();            // reload from database
$user->replicate(['email']); // copy without PK and excluded fields

// Snapshot and diff
$snap = $user->snapshot();
$user->name = 'New Name';
$diff = $user->diff($snap);
// ['name' => ['from' => 'Old', 'to' => 'New Name']]

Serialization

php

$user->toArray();    // respects $hidden, $appends, and loaded relations
$user->toJson();
json_encode($user);  // JsonSerializable

$user->only(['name', 'email']);
$user->except(['password']);

User::all()->toArray();
User::all()->toJson();

Lazy Loading Relations

php

// Load only if not already loaded
$user->loadMissing(['profile', 'posts']);

// Append a count without loading the related models
$user->loadCount('posts');         // → $user->posts_count
$user->loadCount(['posts', 'comments']);

Mass Processing

php

User::chunk(100, function (Collection $chunk) {
    $chunk->each(fn($u) => processUser($u));
});

Query Scopes

Local Scopes

php

class User extends Model
{
    public function scopeActive(QueryBuilder $query): void
    {
        $query->where('active', 1);
    }

    public function scopeOlderThan(QueryBuilder $query, int $age): void
    {
        $query->where('age', '>', $age);
    }
}

User::scope('active')->get();
User::scope('olderThan', 18)->get();

Global Scopes

Global scopes are applied automatically to every query for the model:

php

// Register (e.g. in a ServiceProvider or bootstrap file)
User::addGlobalScope('active', fn($q) => $q->where('active', 1));

// Remove globally
User::removeGlobalScope('active');

// Bypass for a single query
User::withoutGlobalScope('active')->get();
User::withoutGlobalScope(['active', 'verified'])->get();

Hooks and Observers

php

User::creating(fn($u) => $u->uuid = Uuid::uuid4()->toString());
User::created(fn($u)  => sendWelcomeEmail($u->email));
User::updating(fn($u) => /* ... */);
User::deleting(fn($u) => $u->posts()->get()->each->delete());

Available events: creating, created, updating, updated, saving, saved, deleting, deleted.

Observer Classes

php

class UserObserver
{
    public function creating(User $user): void { /* ... */ }
    public function deleted(User $user): void  { /* ... */ }
}

User::observe(UserObserver::class);

Automatic Slug Generation

php

class Post extends Model
{
    protected ?string $slugFrom   = 'title';
    protected string  $slugField  = 'slug';
    protected bool    $slugUnique = true;
}

$post = Post::create(['title' => 'Hello World']);
echo $post->slug; // 'hello-world'

$post = Post::findBySlug('hello-world');

Relationships

One to One — `hasOne`

php

class User extends Model
{
    public function profile(): HasOne
    {
        return $this->hasOne(Profile::class);
        // hasOne(Profile::class, 'user_id', 'id')
    }
}

$profile = User::find(1)->profile;
$users   = User::with('profile')->get();

One to Many — `hasMany`

php

class User extends Model
{
    public function posts(): HasMany
    {
        return $this->hasMany(Post::class);
    }
}

$posts     = User::find(1)->posts;
$published = User::find(1)->posts()->where('status', 'published')->get();
$users     = User::with('posts')->get();

$post = $user->posts()->create(['title' => 'My Post', 'body' => '...']);
$user->posts()->count();

Inverse — `belongsTo`

php

class Post extends Model
{
    public function author(): BelongsTo
    {
        return $this->belongsTo(User::class, 'user_id', 'id');
    }
}

$author = Post::find(1)->author;
$posts  = Post::with('author')->get();

$post->author()->associate($user);
$post->save();

$post->author()->dissociate();
$post->save();

Many to Many — `belongsToMany`

php

class User extends Model
{
    public function roles(): BelongsToMany
    {
        return $this->belongsToMany(Role::class)
                    ->withPivot('expires_at', 'granted_by')
                    ->withTimestamps();
    }
}

$role = User::find(1)->roles->first();
echo $role->pivot_expires_at;

// Managing the pivot
$user->roles()->attach([1, 2, 3]);
$user->roles()->attach(4, ['expires_at' => '2026-12-31']);
$user->roles()->detach([2, 3]);
$user->roles()->detach();
$user->roles()->sync([1, 3, 5]);
$user->roles()->syncWithoutDetaching([4, 5]);
$user->roles()->toggle([1, 2]);
$user->roles()->updateExistingPivot(1, ['expires_at' => '2027-01-01']);

Has Many Through — `hasManyThrough`

Access distant models via an intermediate model. A Country has many Posts through Users:

php

class Country extends Model
{
    public function posts(): HasManyThrough
    {
        return $this->hasManyThrough(
            Post::class,   // distant model
            User::class,   // intermediate model
            'country_id',  // FK on users → countries
            'user_id',     // FK on posts → users
            'id',          // PK of countries
            'id'           // PK of users
        );
    }
}

// Lazy load
$posts = Country::find(1)->posts;

// Eager load
$countries = Country::with('posts')->get();

The relationship generates a single JOIN query covering three tables and is fully compatible with eager loading.

Polymorphic Relationships

Polymorphic relationships allow a single model to belong to more than one type of model using a shared pair of columns: a type discriminator (morphable_type) and a foreign key (morphable_id).

Polymorphic Table Structure

sql

CREATE TABLE images (
    id             INT PRIMARY KEY AUTO_INCREMENT,
    url            VARCHAR(500),
    imageable_id   INT,
    imageable_type VARCHAR(255)  -- stores the full class name e.g. App\Models\User
);

CREATE TABLE comments (
    id               INT PRIMARY KEY AUTO_INCREMENT,
    body             TEXT,
    commentable_id   INT,
    commentable_type VARCHAR(255)
);

One-to-One Polymorphic — `morphOne`

A User and a Post each have one Image:

php

class User extends Model
{
    public function image(): MorphOne
    {
        return $this->morphOne(Image::class, 'imageable');
        // morphOne(Image::class, 'imageable', 'imageable_type', 'imageable_id', 'id')
    }
}

class Post extends Model
{
    public function image(): MorphOne
    {
        return $this->morphOne(Image::class, 'imageable');
    }
}

The Image model needs no special configuration — the imageable_type column stores the parent class name automatically.

Usage:

php

// Lazy load
$image = User::find(1)->image;

// Eager load
$users = User::with('image')->get();
foreach ($users as $user) {
    echo $user->image?->url;
}

// Create a related image for a user
User::find(1)->image()->create(['url' => '/avatars/user_1.jpg']);

One-to-Many Polymorphic — `morphMany`

A Post and a Video can each have many Comments:

php

class Post extends Model
{
    public function comments(): MorphMany
    {
        return $this->morphMany(Comment::class, 'commentable');
        // morphMany(Comment::class, 'commentable', 'commentable_type', 'commentable_id', 'id')
    }
}

class Video extends Model
{
    public function comments(): MorphMany
    {
        return $this->morphMany(Comment::class, 'commentable');
    }
}

Usage:

php

// Lazy load
$comments = Post::find(1)->comments;

// With constraints
$recent = Post::find(1)->comments()->orderByDesc('created_at')->take(10)->get();

// Eager load multiple morph targets
$posts  = Post::with('comments')->get();
$videos = Video::with('comments')->get();

// Create a comment directly on the relation
$post = Post::find(1);
$comment = $post->comments()->create(['body' => 'Great post!']);
// Sets commentable_id = $post->id  AND  commentable_type = 'App\Models\Post' automatically

Defining `morphOne` and `morphMany` on the Model

php

protected function morphOne(
    string  $related,
    string  $morphName,
    ?string $morphType = null,
    ?string $morphId   = null,
    ?string $localKey  = null
): MorphOne

protected function morphMany(
    string  $related,
    string  $morphName,
    ?string $morphType = null,
    ?string $morphId   = null,
    ?string $localKey  = null
): MorphMany

When $morphType and $morphId are omitted they default to {morphName}_type and {morphName}_id respectively.

Eager Loading

php

// Single relation
User::with('profile')->get();

// Multiple relations
Post::with(['author', 'comments'])->get();

// Select specific columns from the relation
User::with(['posts:title,created_at'])->get();

// Nested relations (dot notation)
Post::with(['author.profile', 'comments'])->get();

// Nested with column selection
Post::with(['author.profile:avatar,bio'])->get();

After eager loading, accessing a relation reads from the in-memory cache — no extra queries:

php

$users = User::with('posts')->get();

foreach ($users as $user) {
    echo $user->posts->count(); // no query
}

if ($user->relationLoaded('posts')) {
    // already in memory
}

Aggregate Eager Loads

Append count/sum/avg/min/max as virtual attributes without loading the related models:

php

// comments_count attribute on each post
Post::withCount('comments')->get();

// likes_votes_sum attribute on each post
Post::withSum('likes', 'votes')->get();

// Combined
Post::withCount('comments')->withAvg('ratings', 'score')->get();

foreach ($posts as $post) {
    echo $post->comments_count;
    echo $post->ratings_score_avg;
}

Collections

All multi-row results are returned as Slenix\Supports\Database\Collection:

php

$users = User::all();

// Filtering
$active  = $users->where('active', 1);
$admins  = $users->whereIn('role', ['admin', 'moderator']);

// Transformation
$names   = $users->pluck('name');
$mapped  = $users->map(fn($u) => "{$u->name} <{$u->email}>");
$grouped = $users->groupBy('country');
$sorted  = $users->sortBy('name');
$unique  = $users->unique('email');

// Slicing
$first5  = $users->take(5);
$chunks  = $users->chunk(10);

// Aggregation
$count   = $users->count();
$avg     = $users->avg('age');
$total   = $users->sum('balance');

// Verification
$isEmpty = $users->isEmpty();
$hasAdm  = $users->contains('role', 'admin');
$allActv = $users->every(fn($u) => $u->active);

// Conversion
$array   = $users->toArray();
$json    = $users->toJson();
$keyed   = $users->keyBy('id');

Raw Queries

php

// Returns array of stdObjects
$rows = Connection::raw(
    'SELECT u.*, COUNT(p.id) as post_count FROM users u LEFT JOIN posts p ON p.user_id = u.id GROUP BY u.id'
);

// Returns hydrated model Collection
$users = User::query(
    'SELECT * FROM users WHERE YEAR(created_at) = :year',
    ['year' => 2024]
);

Always use parameter binding — never interpolate user input directly into SQL.

Query Builder Reference

Method	Returns	Description
`select(columns)`	`self`	SET columns to SELECT
`addSelect(columns)`	`self`	Add columns to existing SELECT
`distinct()`	`self`	Add DISTINCT
`where(col, op, val)`	`self`	Basic WHERE
`orWhere(col, op, val)`	`self`	OR WHERE
`whereIn / whereNotIn`	`self`	WHERE IN / NOT IN
`whereBetween / whereNotBetween`	`self`	WHERE BETWEEN
`whereNull / whereNotNull`	`self`	WHERE IS NULL / IS NOT NULL
`whereLike / whereNotLike`	`self`	WHERE LIKE / NOT LIKE
`whereRaw(sql, bindings)`	`self`	Raw WHERE fragment
`whereColumn(a, op, b)`	`self`	Compare two columns
`whereHas(relation, cb?)`	`self`	WHERE EXISTS subquery
`orWhereHas(relation, cb?)`	`self`	OR WHERE EXISTS
`whereDoesntHave(relation, cb?)`	`self`	WHERE NOT EXISTS
`whereHasCount(relation, op, n)`	`self`	WHERE COUNT subquery
`join / leftJoin / rightJoin`	`self`	JOINs
`orderBy / orderByDesc`	`self`	ORDER BY
`latest / oldest`	`self`	ORDER BY created_at
`inRandomOrder()`	`self`	ORDER BY RAND()
`groupBy / having`	`self`	GROUP BY / HAVING
`limit / offset`	`self`	LIMIT / OFFSET
`take(perPage, page)`	`self`	Paginated LIMIT + OFFSET
`forPage(page, perPage)`	`self`	Alias for take
`when(condition, cb)`	`self`	Conditional clause
`unless(condition, cb)`	`self`	Inverse of when
`tap(cb)`	`self`	Side-effect without breaking chain
`withRelations(relations)`	`self`	Eager load relations
`withCount / withSum / withAvg / withMin / withMax`	`self`	Aggregate eager loads
`remember(seconds, key?)`	`self`	Cache results
`rememberForever(key?)`	`self`	Cache indefinitely
`flushCache()`	`self`	Invalidate cached query
`get()`	`Collection`	Execute and return Collection
`first()`	`?Model`	First result or null
`firstOrFail()`	`Model`	First result or exception
`sole()`	`Model`	Exactly one result or exception
`find(id)`	`?Model`	Find by primary key
`findOrFail(id)`	`Model`	Find by PK or exception
`value(col)`	`mixed`	Single column from first row
`pluck(col, key?)`	`Collection`	Values of a column
`count / sum / avg / max / min`	`int/float`	Aggregates
`exists / doesntExist`	`bool`	Whether any row exists
`paginate(perPage, page)`	`array`	Paginated result with metadata
`cursor()`	`Generator`	Yield one model at a time
`lazy(chunkSize)`	`Generator`	Yield Collection chunks
`chunkById(size, cb)`	`void`	PK-range chunking
`eachById(cb, size)`	`void`	Iterate via chunkById
`toSql()`	`string`	Generated SQL
`toRawSql()`	`string`	SQL with values interpolated (display only)
`getBindings()`	`array`	Bound parameters
`explainSql()`	`array`	EXPLAIN output
`log(label)`	`self`	Write SQL to log
`dump()`	`self`	var_dump SQL + continue
`dd()`	`never`	var_dump SQL + die
`clone()`	`self`	Clone the builder
`reset()`	`self`	Reset to initial state

Complete Example

php

use App\Models\Post;
use App\Models\Video;
use App\Models\User;
use App\Models\Country;

// -------------------------------------------------------
// Polymorphic: User with one avatar image
// -------------------------------------------------------

class User extends Model
{
    public function image(): MorphOne
    {
        return $this->morphOne(Image::class, 'imageable');
    }

    public function posts(): HasMany
    {
        return $this->hasMany(Post::class);
    }

    public function roles(): BelongsToMany
    {
        return $this->belongsToMany(Role::class)->withPivot('expires_at');
    }
}

// Upload avatar — sets imageable_type and imageable_id automatically
$user = User::findOrFail(1);
$user->image()->create(['url' => Storage::url("avatars/{$user->id}.jpg")]);

// -------------------------------------------------------
// Polymorphic: Post and Video with many comments
// -------------------------------------------------------

class Post extends Model
{
    public function comments(): MorphMany
    {
        return $this->morphMany(Comment::class, 'commentable');
    }
}

class Video extends Model
{
    public function comments(): MorphMany
    {
        return $this->morphMany(Comment::class, 'commentable');
    }
}

// Add a comment to a post
$post    = Post::find(1);
$comment = $post->comments()->create(['body' => 'Great post!', 'user_id' => 42]);

// Add a comment to a video
$video = Video::find(1);
$video->comments()->create(['body' => 'Loved this video!', 'user_id' => 42]);

// Eager load comments on both content types
$posts  = Post::with('comments')->withCount('comments')->get();
$videos = Video::with('comments')->withCount('comments')->get();

foreach ($posts as $post) {
    echo "{$post->title}: {$post->comments_count} comments";
}

// -------------------------------------------------------
// Has Many Through: Country → Users → Posts
// -------------------------------------------------------

class Country extends Model
{
    public function posts(): HasManyThrough
    {
        return $this->hasManyThrough(Post::class, User::class, 'country_id', 'user_id');
    }
}

$angolaPosts = Country::where('code', 'AO')->first()->posts;

// -------------------------------------------------------
// Advanced query with when(), caching, and aggregate loads
// -------------------------------------------------------

$page   = (int) ($request->get('page', 1));
$search = $request->get('search');
$role   = $request->get('role');

$result = User::withCount('posts')
    ->withAvg('ratings', 'score')
    ->when($search, fn($q) => $q->whereLike('name', "%{$search}%"))
    ->when($role,   fn($q) => $q->where('role', $role))
    ->whereHas('posts', fn($q) => $q->where('status', 'published'))
    ->orderByDesc('created_at')
    ->remember(120)
    ->paginate(20, $page);

foreach ($result['data'] as $user) {
    echo "{$user->name} — {$user->posts_count} posts — avg rating: {$user->ratings_score_avg}";
}

#Database

#Introduction

#Configuration

#Connections

#Running Queries

#Selecting Columns

#Where Clauses

#Existence Filters

#Ordering, Grouping, and Limiting

#Joins

#Aggregates

#Retrieving Results

#Conditional Clauses

#Pagination

#Query Caching

#Large Datasets

#Debugging Queries

#Models

#Generating a Model

#Defining a Model

#Available Cast Types

#Retrieving Models

#Inserting and Updating

#Atomic Column Operations

#Touch

#Deleting

#Soft Deletes

#Checking State

#Serialization

#Lazy Loading Relations

#Mass Processing

#Query Scopes

#Local Scopes

#Global Scopes

#Hooks and Observers

#Observer Classes

#Automatic Slug Generation

#Relationships

#One to One — hasOne

#One to Many — hasMany

#Inverse — belongsTo

#Many to Many — belongsToMany

#Has Many Through — hasManyThrough

#Polymorphic Relationships

#Polymorphic Table Structure

#One-to-One Polymorphic — morphOne

#One-to-Many Polymorphic — morphMany

#Defining morphOne and morphMany on the Model

#Eager Loading

#Aggregate Eager Loads

#Collections

#Raw Queries

#Query Builder Reference

#Complete Example