Skip to content

Eloquent Database ORM

Precious Lyson edited this page Nov 13, 2026 · 2 revisions

The Machinjiri ORM is a lightweight ActiveRecord-style ORM built for the Machinjiri PHP framework. It provides an intuitive, expressive API for interacting with your database — inspired by Laravel's Eloquent but designed to be dependency-light and easy to understand.

Key Features

Feature Description
ActiveRecord Models Class-based models with attributes, casting, and lifecycle events
Fluent Query Builder Chainable select, where, join, orderBy, limit API
Attribute Casting Auto-cast to int, float, bool, array, json, datetime
DateTimeHandler Timezone-aware date/time handling with rich helper methods
Soft Deletes Mark records as deleted without physically removing them
Query Caching Cache expensive queries with tags and TTL
Model Events Hook into creating, created, saving, saved, deleting, deleted
Schema Builder Create and alter tables with a Blueprint-style DSL
Aggregates count, sum, avg, min, max built-in
Auto Timestamps Automatically manages created_at and updated_at
CLI Generators Scaffold controllers, models, and middleware via Artisan commands

Architecture Overview

┌─────────────────────────────────────────────────────────────┐
│                     Your Application                         │
└───────────────────────────┬─────────────────────────────────┘
                            │
              ┌─────────────▼─────────────┐
              │      AbstractModel        │  ← Your models extend this
              │  (CRUD, casting, events)  │
              └─────────────┬─────────────┘
                            │
              ┌─────────────▼─────────────┐
              │       QueryBuilder        │  ← Fluent SQL builder
              └─────────────┬─────────────┘
                            │
              ┌─────────────▼─────────────┐
              │    DatabaseConnection     │  ← PDO wrapper
              │         + Grammar         │  ← SQL dialect
              └───────────────────────────┘

Installation

Since Machinjiri is framework-internal, the ORM ships with the core. To use it:

use Mlangeni\Machinjiri\Core\Artisans\Base\AbstractModel;
use Mlangeni\Machinjiri\Core\Database\Builders\QueryBuilder;
use Mlangeni\Machinjiri\Core\Database\DatabaseConnection;

Ensure your DatabaseConnection is configured (host, database, credentials). All models and queries resolve the active connection automatically.


Quick Start

1. Create a Model

php artisan make:model User

This generates app/Models/User.php:

<?php

namespace App\Models;

use Mlangeni\Machinjiri\Core\Artisans\Base\AbstractModel;

class User extends AbstractModel
{
    protected string $table = 'users';

    protected array $fillable = ['name', 'email', 'password'];

    protected array $casts = [
        'created_at' => 'datetime',
        'updated_at' => 'datetime',
        'is_active'  => 'boolean',
    ];
}

2. Create a Record

use App\Models\User;

$user = User::create([
    'name'  => 'Alice',
    'email' => 'alice@example.com',
]);

3. Query Records

// Find by primary key
$user = User::find(1);

// Fluent queries
$users = User::where('is_active', '=', true)
    ->orderBy('name', 'ASC')
    ->limit(10)
    ->get();

// First match
$user = User::where('email', '=', 'alice@example.com')->first();

4. Update & Delete

$user->name = 'Alice Smith';
$user->save();

$user->delete();

Configuration

Model Properties

Property Type Default Purpose
$table string snake_case plural Database table name
$primaryKey string 'id' Primary key column
$keyType string 'int' Primary key type
$incrementing bool true Auto-incrementing PK
$fillable array [] Mass-assignable columns
$guarded array ['*'] Blocked columns (default: all blocked)
$casts array [] Attribute type conversions
$timestamps bool true Auto-manage timestamps
$dateFormat string 'Y-m-d H:i:s' DB date storage format
$timezone string 'UTC' Model timezone
$softDelete bool false Enable soft deletes
$cacheEnabled bool false Enable query caching
$cacheTtl ?int null Cache TTL (seconds)
$cacheTags array [] Cache tags

Mass Assignment

Machinjiri blocks all mass assignment by default ($guarded = ['*']). You must explicitly opt-in:

protected array $fillable = ['name', 'email'];

Or use forceFill() to bypass:

$user->forceFill(['name' => 'Alice', 'role' => 'admin']);

Models

Defining a Model

class Post extends AbstractModel
{
    protected string $table = 'posts';
    protected string $primaryKey = 'post_id';
    protected bool $incrementing = false;
    protected string $keyType = 'string';

    protected array $fillable = ['title', 'body', 'author_id'];
    protected array $casts = [
        'published_at' => 'datetime',
        'metadata'     => 'array',
    ];
}

Magic Accessors

$post = Post::find(1);

echo $post->title;          // __get → getAttribute()
$post->title = 'New Title'; // __set → setAttribute()
isset($post->title);        // __isset
unset($post->title);        // __unset

Serialization

$post->toArray();   // Casted attributes as array
$post->toJson();    // JSON string

Query Builder

The QueryBuilder is the engine behind every model query. You can also use it standalone.

Standalone Usage

use Mlangeni\Machinjiri\Core\Database\Builders\QueryBuilder;

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

SELECT

QueryBuilder::builder('users')
    ->select(['id', 'name', 'email'])
    ->distinct()
    ->get();

WHERE Clauses

// Basic
->where('age', '>', 18)

// Multiple conditions (AND)
->where('age', '>', 18)
->where('country', '=', 'Zimbabwe')

// OR conditions
->where('role', '=', 'admin')
->orWhere('role', '=', 'moderator')

// IN
->whereIn('id', [1, 2, 3])

// IS NULL
->whereNull('deleted_at')

// Nested (grouped) conditions
->where(function ($q) {
    $q->where('status', '=', 'active')
      ->orWhere('status', '=', 'pending');
})

Generated SQL:

SELECT * FROM `users`
WHERE age > ?
  AND country = ?
  AND role = ? OR role = ?
  AND id IN (?, ?, ?)
  AND deleted_at IS NULL
  AND (status = ? OR status = ?)

JOINs

QueryBuilder::builder('users')
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->leftJoin('comments', 'posts.id', '=', 'comments.post_id')
    ->select(['users.name', 'posts.title'])
    ->get();

ORDER BY / LIMIT / OFFSET

->orderBy('created_at', 'DESC')
->orderBy('name', 'ASC')
->limit(20)
->offset(40)

GROUP BY

QueryBuilder::builder('orders')
    ->select(['user_id', 'COUNT(*) as total'])
    ->groupBy('user_id')
    ->get();

CRUD Operations

Create

// Via model
$user = User::create(['name' => 'Alice', 'email' => 'alice@example.com']);

// Via new instance
$user = new User();
$user->name = 'Bob';
$user->email = 'bob@example.com';
$user->save();

// Via query builder
QueryBuilder::builder('users')->insert([
    'name'  => 'Charlie',
    'email' => 'charlie@example.com',
])->execute();

Read

User::find(1);                    // Find by PK
User::all();                       // All records
User::where('role', 'admin')->get();
User::where('email', 'a@b.com')->first();

Update

$user = User::find(1);
$user->name = 'Updated Name';
$user->save();

// Or bulk via builder
QueryBuilder::builder('users')
    ->where('role', 'guest')
    ->update(['is_active' => true])
    ->execute();

Delete

$user = User::find(1);
$user->delete();

// Or builder
QueryBuilder::builder('users')
    ->where('id', '=', 5)
    ->delete()
    ->execute();

firstOrCreate / updateOrCreate

// Find or create with attributes
$user = User::firstOrCreate(
    ['email' => 'alice@example.com'],
    ['name' => 'Alice']
);

// Find and update, or create
$user = User::updateOrCreate(
    ['email' => 'alice@example.com'],
    ['name' => 'Alice Smith', 'role' => 'admin']
);

Attribute Casting

Casting converts DB values into PHP types automatically.

Available Casts

Cast DB Storage PHP Type
int, integer INT int
float, double FLOAT/DECIMAL float
string VARCHAR string
bool, boolean TINYINT(1) bool
array, json TEXT array
object TEXT stdClass
datetime, date, timestamp DATETIME DateTimeHandler

Example

class Product extends AbstractModel
{
    protected array $casts = [
        'price'       => 'float',
        'in_stock'    => 'boolean',
        'metadata'    => 'array',
        'published_at'=> 'datetime',
    ];
}

$product = Product::find(1);

var_dump($product->in_stock);    // bool(true)
var_dump($product->metadata);    // array(...)
echo $product->published_at->format('Y-m-d'); // "2024-01-15"

DateTime Handling

Machinjiri ships with DateTimeHandler — a timezone-aware, immutable-by-convention date wrapper. DateTime-cast attributes are automatically converted.

Creating DateTimeHandler Instances

use Mlangeni\Machinjiri\Core\Date\DateTimeHandler;

$now   = new DateTimeHandler('now', 'Africa/Harare');
$date  = new DateTimeHandler('2024-03-15', 'UTC');
$ts    = DateTimeHandler::fromTimestamp(1710460800, 'UTC');

Common Methods

$date->format('Y-m-d');              // "2024-03-15"
$date->getTimestamp();               // Unix timestamp
$date->addDays(7);
$date->subMonths(1);
$date->isPast();
$date->isFuture();
$date->diffInDays($other);

Using with Models

$user = User::find(1);

// Returns a DateTimeHandler
echo $user->created_at->format('M d, Y');

// Assigning accepts strings, timestamps, or DateTimeHandler
$user->created_at = new DateTimeHandler('2024-01-01');
$user->updated_at = '2024-01-01 12:00:00';
$user->save();

Soft Deletes

Soft deletes mark records as deleted without physically removing them.

Enable Soft Deletes

class Post extends AbstractModel
{
    protected bool $softDelete = true;
}

⚠️ Your posts table must have a deleted_at nullable column.

Usage

// Soft delete
$post->delete();

// Restore
$post->restore();

// Check trashed
$post->trashed();  // bool

// Queries automatically exclude trashed records
Post::all();                             // Only non-deleted

// Include trashed
Post::withTrashed()->get();

// Only trashed
Post::onlyTrashed()->get();

// Force permanent delete
$post->forceDelete();

Model Events

Hook into the model lifecycle:

// Available events
// booting, saving, saved, created, updated, deleting, deleted

Registering Listeners

use Mlangeni\Machinjiri\Core\Artisans\Events\EventListener;

$events = resolve(EventListener::class);

$events->listen('model.saving.App\Models\User', function ($user) {
    if (empty($user->name)) {
        throw new \Exception('Name is required');
    }
});

$events->listen('model.created.App\Models\User', function ($user) {
    // Send welcome email
});

$events->listen('model.deleting.App\Models\User', function ($user) {
    // Return false to cancel the delete
});

Cancellation

Returning false from saving, deleting cancels the operation.

$events->listen('model.deleting.App\Models\Post', function ($post) {
    if ($post->is_published) {
        return false;  // Prevent deletion
    }
});

Caching

Speed up read-heavy workloads with optional query caching.

Enable Caching

class Article extends AbstractModel
{
    protected static bool $cacheEnabled = true;
    protected static ?int $cacheTtl = 300; // 5 minutes
    protected static array $cacheTags = ['articles'];
}

Usage

// Automatically cached
$articles = Article::where('published', '=', true)->get();

// Explicit cache management
Article::cached()->where('views', '>', 1000)->get();

Invalidation

Cache is automatically invalidated on save, update, and delete.

$cache = resolve(CacheManager::class);
$cache->tags(['model.articles'])->clear(); // Manual clear

Timestamps

By default, created_at and updated_at are managed automatically.

Disable

protected bool $timestamps = false;

Custom Columns

protected const CREATED_AT = 'date_created';
protected const UPDATED_AT = 'date_modified';

Touch

$post->touch(); // Update `updated_at` only

Schema Builder

Create and alter tables programmatically.

Create a Table

use Mlangeni\Machinjiri\Core\Database\Builders\QueryBuilder;

$builder = QueryBuilder::builder();

$builder->createTableWithBlueprint('users', function ($table) {
    $table->id();
    $table->string('name', 100);
    $table->string('email')->unique();
    $table->boolean('is_active');
    $table->decimal('balance', 10, 2);
    $table->dateTime('last_login');
    $table->foreignId('role_id');
    $table->timestamps();
});

Alter a Table

$builder->alterTableWithBlueprint('users', function ($table) {
    $table->addColumn('nickname', 'VARCHAR(50)');
    $table->dropColumn('legacy_field');
    $table->renameColumn('old_name', 'new_name');
    $table->addIndex('idx_email', ['email']);
});

Drop a Table

$builder->dropTable('users')->execute();

Column Types

Method DB Type
id() INT PRIMARY KEY AUTO_INCREMENT
string($col, $len) VARCHAR($len)
text($col) TEXT
integer($col) INT
boolean($col) TINYINT(1)
float($col, $p, $s) FLOAT($p,$s)
decimal($col, $p, $s) DECIMAL($p,$s)
date($col) DATE
dateTime($col) DATETIME
timestamp($col) TIMESTAMP
foreignId($col) INT UNSIGNED

Aggregates & Analytics

Basic Aggregates

User::where('is_active', '=', true)->count();
User::where('is_active', '=', true)->count('email', true); // distinct

Order::sum('total');
Order::avg('total');
Order::min('total');
Order::max('total');

Date Range Helpers

Order::whereCurrentWeek('created_at')->sum('total');
Order::whereCurrentMonth('created_at')->get();
Order::whereCurrentYear('created_at')->get();

// Custom range
Order::whereDateRange('created_at', '2024-01-01', '2024-03-31')->get();

Grouped Analytics

QueryBuilder::builder('orders')
    ->select(['user_id', 'SUM(total) as revenue'])
    ->groupBy('user_id')
    ->orderBy('revenue', 'DESC')
    ->limit(10)
    ->get();

Code Generation

The framework ships with Artisan commands to scaffold classes.

Commands

Command Description
make:model <Name> Creates app/Models/<Name>.php
make:controller <Name> Creates app/Controllers/<Name>.php
make:middleware <Name> Creates app/Middleware/<Name>.php
get:env Dumps .env variables

Example

php artisan make:model Invoice

Generates a fully-documented model with $fillable, $casts, $timestamps, and $softDelete stubs pre-configured.


Advanced Patterns

Bulk Insert (Loop Pattern)

$data = [
    ['name' => 'A', 'email' => 'a@x.com'],
    ['name' => 'B', 'email' => 'b@x.com'],
];

foreach ($data as $row) {
    QueryBuilder::builder('users')->insert($row)->execute();
}

Transactions

Wrap your execute() calls in DatabaseConnection transactions:

$db = DatabaseConnection::getInstance();

try {
    $db->beginTransaction();
    $user = User::create(['name' => 'Alice']);
    $profile = Profile::create(['user_id' => $user->id]);
    $db->commit();
} catch (\Throwable $e) {
    $db->rollBack();
    throw $e;
}

Chunked Processing

$offset = 0;
do {
    $batch = User::query()->limit(500)->offset($offset)->get();
    foreach ($batch as $row) {
        // process...
    }
    $offset += 500;
} while (count($batch) === 500);

Repository Pattern

class UserRepository
{
    public function activeAdmins(): array
    {
        return User::where('is_active', '=', true)
            ->where('role', '=', 'admin')
            ->orderBy('name')
            ->get();
    }
}

Inheritance (Base Model per Domain)

abstract class TenantModel extends AbstractModel
{
    public function __construct(array $attributes = [])
    {
        parent::__construct($attributes);
        $this->table = 'tenant_' . $this->table;
    }
}

API Reference

AbstractModel — Public Methods

Static

Method Returns Description
query() QueryBuilder New query builder
cached() CachedQueryBuilder New cached builder
find($id) ?static Find by PK
all() static[] All records
create(array) static Create and save
firstOrCreate($attrs, $vals) static Find or create
firstOrNew($attrs, $vals) static Find or instantiate
updateOrCreate($attrs, $vals) static Update or create
withTrashed() QueryBuilder Include soft-deleted
onlyTrashed() QueryBuilder Only soft-deleted

Instance

Method Returns Description
save() bool Persist changes
delete() bool Delete (soft or hard)
forceDelete() bool Permanent delete
restore() bool Restore soft-deleted
refresh() self Reload from DB
fresh() ?static Fresh instance
trashed() bool Is soft-deleted
exists() bool Persisted in DB
isDirty($attr = null) bool Has unsaved changes
touch() bool Update updated_at
increment($col, $n) int Increment column
decrement($col, $n) int Decrement column
fill(array) self Mass assign fillable
forceFill(array) self Mass assign all
toArray() array Casted attributes
toJson($opts) string JSON string

QueryBuilder — Public Methods

Method Returns Description
builder($table) self Static factory
select(array) self Set columns
distinct() self DISTINCT query
join($t,$f,$o,$s,$type) self JOIN
leftJoin($t,$f,$o,$s) self LEFT JOIN
where($c,$o,$v,$bool) self WHERE
orWhere($c,$o,$v) self OR WHERE
whereIn($c,array,$bool) self WHERE IN
whereNull($c,$bool) self IS NULL
whereDateRange($c,$s,$e) self BETWEEN range
whereCurrentWeek($c) self Current week
whereCurrentMonth($c) self Current month
whereCurrentYear($c) self Current year
orderBy($c,$dir) self ORDER BY
groupBy($c) self GROUP BY
limit($n) self LIMIT
offset($n) self OFFSET
insert(array) self INSERT action
update(array) self UPDATE action
delete() self DELETE action
get() array Execute SELECT
first() ?array First row
execute($sql) array Execute
count($c,$distinct) int COUNT
sum($c) mixed SUM
avg($c) mixed AVG
min($c) mixed MIN
max($c) mixed MAX

Best Practices

✅ Do

  • Explicitly define $fillable for every model that allows mass assignment.
  • Use $casts to keep your domain types clean — never rely on raw DB strings.
  • Cast dates to datetime so you get DateTimeHandler instances.
  • Use soft deletes for user-facing data that may need recovery.
  • Tag your cache with the model/table name for reliable invalidation.
  • Use firstOrCreate to avoid race-condition duplicates on unique columns.
  • Wrap multi-table operations in transactions when consistency matters.

❌ Don't

  • Don't set $guarded = [] — that's an anti-pattern. Use $fillable.
  • Don't forget $softDelete = true and the deleted_at column.
  • Don't call execute() on get() — get() already executes.
  • Don't mix QueryBuilder::builder() state between operations — builders reset after execution.
  • Don't compare casted datetimes with ===; use ->getTimestamp() or isDirty().

Performance Tips

  1. Select only needed columns: ->select(['id', 'name']) reduces transfer overhead.
  2. Use indexed columns in WHERE and JOIN clauses.
  3. Prefer count() over fetching all rows when you only need a total.
  4. Use limit() on list endpoints — never return unbounded sets.
  5. Enable caching on read-heavy models with sensible TTLs.

Troubleshooting

"Class already exists" from generators

ResourceGenerator refuses to overwrite files. Delete the existing file or use a different name.

save() returns false silently

Most likely a model event listener returned false. Check your model.saving or model.creating listeners.

Mass assignment not working

You must define $fillable. With $guarded = ['*'] (default), nothing is mass-assignable. Use forceFill() for bypassing.

deleted_at column missing

If you enable $softDelete = true, run a migration adding:

ALTER TABLE your_table ADD COLUMN deleted_at DATETIME NULL;

find() returns null unexpectedly

  • Confirm the primary key column matches $primaryKey.
  • If soft deletes are on, find() excludes trashed records — use withTrashed()->find($id).

DateTime appears off by hours

Set $timezone correctly on your model. The DB stores Y-m-d H:i:s in $dateFormat, and DateTimeHandler will interpret/return times in $timezone.

Nested where bindings out of order

The current implementation clones the sub-builder and merges bindings after compilation. If you see parameter mismatches, ensure nested closures don't also call where on the parent outside the closure.

QueryBuilder state leaks between queries

Every execute() calls reset() in a finally block. If you clone a builder mid-flight and reuse it, call reset() or construct a fresh one.


Clone this wiki locally