1. php
  2. /database
  3. /orm-activerecord

ORM and ActiveRecord in PHP

Introduction to ORM and ActiveRecord

Object-Relational Mapping (ORM) is a programming technique that lets you query and manipulate data from a database using an object-oriented paradigm. ActiveRecord is a specific ORM pattern where a database record is wrapped into an object, and the object contains both data and behavior.

Benefits of ORM/ActiveRecord

  1. Object-Oriented Interface: Work with objects instead of raw SQL
  2. Reduced Boilerplate: Less repetitive database code
  3. Type Safety: Better IDE support and error catching
  4. Relationships: Easy handling of table relationships
  5. Portability: Database-agnostic code
  6. Security: Built-in protection against SQL injection

Simple ActiveRecord Implementation

Base ActiveRecord Class

<?php
abstract class ActiveRecord
{
    protected static $table;
    protected static $primaryKey = 'id';
    protected static $connection;
    
    protected $attributes = [];
    protected $original = [];
    protected $exists = false;
    
    public function __construct(array $attributes = [])
    {
        $this->fill($attributes);
        $this->syncOriginal();
    }
    
    public static function setConnection(PDO $connection): void
    {
        static::$connection = $connection;
    }
    
    public static function getConnection(): PDO
    {
        if (!static::$connection) {
            throw new Exception('Database connection not set');
        }
        return static::$connection;
    }
    
    public function fill(array $attributes): self
    {
        foreach ($attributes as $key => $value) {
            $this->setAttribute($key, $value);
        }
        return $this;
    }
    
    public function setAttribute(string $key, $value): void
    {
        $this->attributes[$key] = $value;
    }
    
    public function getAttribute(string $key)
    {
        return $this->attributes[$key] ?? null;
    }
    
    public function __get(string $key)
    {
        return $this->getAttribute($key);
    }
    
    public function __set(string $key, $value)
    {
        $this->setAttribute($key, $value);
    }
    
    public function __isset(string $key): bool
    {
        return isset($this->attributes[$key]);
    }
    
    public static function find($id): ?static
    {
        $instance = new static();
        $table = static::getTable();
        $primaryKey = static::$primaryKey;
        
        $stmt = static::getConnection()->prepare("SELECT * FROM {$table} WHERE {$primaryKey} = ?");
        $stmt->execute([$id]);
        
        $data = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$data) {
            return null;
        }
        
        return $instance->newFromBuilder($data);
    }
    
    public static function all(): array
    {
        $table = static::getTable();
        $stmt = static::getConnection()->query("SELECT * FROM {$table}");
        
        $results = [];
        while ($data = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $instance = new static();
            $results[] = $instance->newFromBuilder($data);
        }
        
        return $results;
    }
    
    public static function where(string $column, string $operator, $value): QueryBuilder
    {
        return (new QueryBuilder(static::class))->where($column, $operator, $value);
    }
    
    public function save(): bool
    {
        if ($this->exists) {
            return $this->performUpdate();
        } else {
            return $this->performInsert();
        }
    }
    
    public function delete(): bool
    {
        if (!$this->exists) {
            return false;
        }
        
        $table = static::getTable();
        $primaryKey = static::$primaryKey;
        $id = $this->getAttribute($primaryKey);
        
        $stmt = static::getConnection()->prepare("DELETE FROM {$table} WHERE {$primaryKey} = ?");
        return $stmt->execute([$id]);
    }
    
    protected function performInsert(): bool
    {
        $table = static::getTable();
        $attributes = $this->getAttributesForInsert();
        
        $columns = implode(', ', array_keys($attributes));
        $placeholders = ':' . implode(', :', array_keys($attributes));
        
        $sql = "INSERT INTO {$table} ({$columns}) VALUES ({$placeholders})";
        $stmt = static::getConnection()->prepare($sql);
        
        $result = $stmt->execute($attributes);
        
        if ($result) {
            $this->setAttribute(static::$primaryKey, static::getConnection()->lastInsertId());
            $this->exists = true;
            $this->syncOriginal();
        }
        
        return $result;
    }
    
    protected function performUpdate(): bool
    {
        $dirty = $this->getDirty();
        
        if (empty($dirty)) {
            return true; // No changes to save
        }
        
        $table = static::getTable();
        $primaryKey = static::$primaryKey;
        $id = $this->getAttribute($primaryKey);
        
        $setParts = [];
        foreach (array_keys($dirty) as $column) {
            $setParts[] = "{$column} = :{$column}";
        }
        
        $sql = "UPDATE {$table} SET " . implode(', ', $setParts) . " WHERE {$primaryKey} = :id";
        $stmt = static::getConnection()->prepare($sql);
        
        $bindings = array_merge($dirty, ['id' => $id]);
        $result = $stmt->execute($bindings);
        
        if ($result) {
            $this->syncOriginal();
        }
        
        return $result;
    }
    
    protected function newFromBuilder(array $attributes): static
    {
        $instance = new static();
        $instance->fill($attributes);
        $instance->syncOriginal();
        $instance->exists = true;
        
        return $instance;
    }
    
    protected function syncOriginal(): void
    {
        $this->original = $this->attributes;
    }
    
    protected function getDirty(): array
    {
        $dirty = [];
        
        foreach ($this->attributes as $key => $value) {
            if (!array_key_exists($key, $this->original) || $this->original[$key] !== $value) {
                $dirty[$key] = $value;
            }
        }
        
        return $dirty;
    }
    
    protected function getAttributesForInsert(): array
    {
        $attributes = $this->attributes;
        
        // Remove primary key if it's auto-increment
        if (isset($attributes[static::$primaryKey]) && $attributes[static::$primaryKey] === null) {
            unset($attributes[static::$primaryKey]);
        }
        
        return $attributes;
    }
    
    protected static function getTable(): string
    {
        if (static::$table) {
            return static::$table;
        }
        
        // Auto-generate table name from class name
        $className = (new ReflectionClass(static::class))->getShortName();
        return strtolower($className) . 's';
    }
    
    public function toArray(): array
    {
        return $this->attributes;
    }
    
    public function toJson(): string
    {
        return json_encode($this->toArray());
    }
}
?>

Query Builder for ActiveRecord

<?php
class QueryBuilder
{
    private $model;
    private $wheres = [];
    private $orders = [];
    private $limit;
    private $offset;
    private $joins = [];
    
    public function __construct(string $model)
    {
        $this->model = $model;
    }
    
    public function where(string $column, string $operator, $value): self
    {
        $this->wheres[] = compact('column', 'operator', 'value');
        return $this;
    }
    
    public function orderBy(string $column, string $direction = 'ASC'): self
    {
        $this->orders[] = compact('column', 'direction');
        return $this;
    }
    
    public function limit(int $limit): self
    {
        $this->limit = $limit;
        return $this;
    }
    
    public function offset(int $offset): self
    {
        $this->offset = $offset;
        return $this;
    }
    
    public function join(string $table, string $first, string $operator, string $second): self
    {
        $this->joins[] = compact('table', 'first', 'operator', 'second');
        return $this;
    }
    
    public function get(): array
    {
        $sql = $this->toSql();
        $bindings = $this->getBindings();
        
        $connection = $this->model::getConnection();
        $stmt = $connection->prepare($sql);
        $stmt->execute($bindings);
        
        $results = [];
        while ($data = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $instance = new $this->model();
            $results[] = $instance->newFromBuilder($data);
        }
        
        return $results;
    }
    
    public function first()
    {
        $this->limit(1);
        $results = $this->get();
        return $results[0] ?? null;
    }
    
    public function count(): int
    {
        $table = $this->model::getTable();
        $sql = "SELECT COUNT(*) FROM {$table}";
        
        if (!empty($this->wheres)) {
            $sql .= ' WHERE ' . $this->buildWhereClause();
        }
        
        $connection = $this->model::getConnection();
        $stmt = $connection->prepare($sql);
        $stmt->execute($this->getBindings());
        
        return (int) $stmt->fetchColumn();
    }
    
    private function toSql(): string
    {
        $table = $this->model::getTable();
        $sql = "SELECT * FROM {$table}";
        
        // Add joins
        foreach ($this->joins as $join) {
            $sql .= " JOIN {$join['table']} ON {$join['first']} {$join['operator']} {$join['second']}";
        }
        
        // Add where clauses
        if (!empty($this->wheres)) {
            $sql .= ' WHERE ' . $this->buildWhereClause();
        }
        
        // Add order by
        if (!empty($this->orders)) {
            $orderClauses = [];
            foreach ($this->orders as $order) {
                $orderClauses[] = "{$order['column']} {$order['direction']}";
            }
            $sql .= ' ORDER BY ' . implode(', ', $orderClauses);
        }
        
        // Add limit and offset
        if ($this->limit) {
            $sql .= " LIMIT {$this->limit}";
        }
        
        if ($this->offset) {
            $sql .= " OFFSET {$this->offset}";
        }
        
        return $sql;
    }
    
    private function buildWhereClause(): string
    {
        $clauses = [];
        foreach ($this->wheres as $where) {
            $clauses[] = "{$where['column']} {$where['operator']} ?";
        }
        return implode(' AND ', $clauses);
    }
    
    private function getBindings(): array
    {
        $bindings = [];
        foreach ($this->wheres as $where) {
            $bindings[] = $where['value'];
        }
        return $bindings;
    }
}
?>

Model Examples

User Model

<?php
class User extends ActiveRecord
{
    protected static $table = 'users';
    
    // Define fillable attributes for mass assignment protection
    protected $fillable = ['name', 'email', 'password'];
    
    // Define hidden attributes (won't be included in toArray/toJson)
    protected $hidden = ['password'];
    
    public function setPasswordAttribute(string $value): void
    {
        $this->attributes['password'] = password_hash($value, PASSWORD_DEFAULT);
    }
    
    public function getFullNameAttribute(): string
    {
        return $this->first_name . ' ' . $this->last_name;
    }
    
    // Relationships
    public function posts(): HasMany
    {
        return $this->hasMany(Post::class, 'user_id');
    }
    
    public function profile(): HasOne
    {
        return $this->hasOne(Profile::class, 'user_id');
    }
    
    // Scopes
    public static function active(): QueryBuilder
    {
        return static::where('status', '=', 'active');
    }
    
    public static function findByEmail(string $email): ?static
    {
        return static::where('email', '=', $email)->first();
    }
}

// Usage
$user = new User([
    'name' => 'John Doe',
    'email' => '[email protected]',
    'password' => 'secret123'
]);
$user->save();

// Find users
$user = User::find(1);
$users = User::where('status', '=', 'active')->get();
$user = User::findByEmail('[email protected]');
?>

Post Model with Relationships

<?php
class Post extends ActiveRecord
{
    protected static $table = 'posts';
    
    protected $fillable = ['title', 'content', 'user_id', 'published_at'];
    
    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class, 'user_id');
    }
    
    public function comments(): HasMany
    {
        return $this->hasMany(Comment::class, 'post_id');
    }
    
    public function tags(): BelongsToMany
    {
        return $this->belongsToMany(Tag::class, 'post_tags', 'post_id', 'tag_id');
    }
    
    // Scopes
    public static function published(): QueryBuilder
    {
        return static::where('published_at', 'IS NOT', null);
    }
    
    public static function byUser(int $userId): QueryBuilder
    {
        return static::where('user_id', '=', $userId);
    }
}
?>

Relationship Implementation

HasOne Relationship

<?php
class HasOne
{
    private $parent;
    private $related;
    private $foreignKey;
    private $localKey;
    
    public function __construct(ActiveRecord $parent, string $related, string $foreignKey, string $localKey = 'id')
    {
        $this->parent = $parent;
        $this->related = $related;
        $this->foreignKey = $foreignKey;
        $this->localKey = $localKey;
    }
    
    public function get()
    {
        $parentKey = $this->parent->getAttribute($this->localKey);
        return $this->related::where($this->foreignKey, '=', $parentKey)->first();
    }
    
    public function create(array $attributes): ActiveRecord
    {
        $attributes[$this->foreignKey] = $this->parent->getAttribute($this->localKey);
        $instance = new $this->related($attributes);
        $instance->save();
        return $instance;
    }
}
?>

HasMany Relationship

<?php
class HasMany
{
    private $parent;
    private $related;
    private $foreignKey;
    private $localKey;
    
    public function __construct(ActiveRecord $parent, string $related, string $foreignKey, string $localKey = 'id')
    {
        $this->parent = $parent;
        $this->related = $related;
        $this->foreignKey = $foreignKey;
        $this->localKey = $localKey;
    }
    
    public function get(): array
    {
        $parentKey = $this->parent->getAttribute($this->localKey);
        return $this->related::where($this->foreignKey, '=', $parentKey)->get();
    }
    
    public function create(array $attributes): ActiveRecord
    {
        $attributes[$this->foreignKey] = $this->parent->getAttribute($this->localKey);
        $instance = new $this->related($attributes);
        $instance->save();
        return $instance;
    }
    
    public function count(): int
    {
        $parentKey = $this->parent->getAttribute($this->localKey);
        return $this->related::where($this->foreignKey, '=', $parentKey)->count();
    }
}
?>

BelongsTo Relationship

<?php
class BelongsTo
{
    private $child;
    private $related;
    private $foreignKey;
    private $ownerKey;
    
    public function __construct(ActiveRecord $child, string $related, string $foreignKey, string $ownerKey = 'id')
    {
        $this->child = $child;
        $this->related = $related;
        $this->foreignKey = $foreignKey;
        $this->ownerKey = $ownerKey;
    }
    
    public function get()
    {
        $foreignKeyValue = $this->child->getAttribute($this->foreignKey);
        if (!$foreignKeyValue) {
            return null;
        }
        return $this->related::where($this->ownerKey, '=', $foreignKeyValue)->first();
    }
    
    public function associate(ActiveRecord $model): void
    {
        $this->child->setAttribute($this->foreignKey, $model->getAttribute($this->ownerKey));
    }
    
    public function dissociate(): void
    {
        $this->child->setAttribute($this->foreignKey, null);
    }
}
?>

Many-to-Many Relationship

<?php
class BelongsToMany
{
    private $parent;
    private $related;
    private $pivotTable;
    private $foreignPivotKey;
    private $relatedPivotKey;
    
    public function __construct(
        ActiveRecord $parent,
        string $related,
        string $pivotTable,
        string $foreignPivotKey,
        string $relatedPivotKey
    ) {
        $this->parent = $parent;
        $this->related = $related;
        $this->pivotTable = $pivotTable;
        $this->foreignPivotKey = $foreignPivotKey;
        $this->relatedPivotKey = $relatedPivotKey;
    }
    
    public function get(): array
    {
        $parentKey = $this->parent->getAttribute('id');
        $relatedTable = $this->related::getTable();
        
        $sql = "
            SELECT r.* 
            FROM {$relatedTable} r
            INNER JOIN {$this->pivotTable} p ON r.id = p.{$this->relatedPivotKey}
            WHERE p.{$this->foreignPivotKey} = ?
        ";
        
        $connection = $this->parent::getConnection();
        $stmt = $connection->prepare($sql);
        $stmt->execute([$parentKey]);
        
        $results = [];
        while ($data = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $instance = new $this->related();
            $results[] = $instance->newFromBuilder($data);
        }
        
        return $results;
    }
    
    public function attach($id): void
    {
        $parentKey = $this->parent->getAttribute('id');
        
        $sql = "INSERT INTO {$this->pivotTable} ({$this->foreignPivotKey}, {$this->relatedPivotKey}) VALUES (?, ?)";
        $connection = $this->parent::getConnection();
        $stmt = $connection->prepare($sql);
        $stmt->execute([$parentKey, $id]);
    }
    
    public function detach($id): void
    {
        $parentKey = $this->parent->getAttribute('id');
        
        $sql = "DELETE FROM {$this->pivotTable} WHERE {$this->foreignPivotKey} = ? AND {$this->relatedPivotKey} = ?";
        $connection = $this->parent::getConnection();
        $stmt = $connection->prepare($sql);
        $stmt->execute([$parentKey, $id]);
    }
    
    public function sync(array $ids): void
    {
        $parentKey = $this->parent->getAttribute('id');
        $connection = $this->parent::getConnection();
        
        // Remove all existing relationships
        $sql = "DELETE FROM {$this->pivotTable} WHERE {$this->foreignPivotKey} = ?";
        $stmt = $connection->prepare($sql);
        $stmt->execute([$parentKey]);
        
        // Add new relationships
        foreach ($ids as $id) {
            $this->attach($id);
        }
    }
}
?>

Advanced Features

Model Events and Hooks

<?php
abstract class ActiveRecord
{
    // ... previous code ...
    
    protected static $booted = [];
    
    public static function boot(): void
    {
        if (isset(static::$booted[static::class])) {
            return;
        }
        
        static::$booted[static::class] = true;
        static::bootIfNotBooted();
    }
    
    protected static function bootIfNotBooted(): void
    {
        // Override in subclasses
    }
    
    protected function fireModelEvent(string $event): void
    {
        $method = 'on' . ucfirst($event);
        if (method_exists($this, $method)) {
            $this->$method();
        }
    }
    
    public function save(): bool
    {
        $this->fireModelEvent('saving');
        
        if ($this->exists) {
            $this->fireModelEvent('updating');
            $result = $this->performUpdate();
            if ($result) {
                $this->fireModelEvent('updated');
            }
        } else {
            $this->fireModelEvent('creating');
            $result = $this->performInsert();
            if ($result) {
                $this->fireModelEvent('created');
            }
        }
        
        if ($result) {
            $this->fireModelEvent('saved');
        }
        
        return $result;
    }
    
    public function delete(): bool
    {
        $this->fireModelEvent('deleting');
        $result = parent::delete();
        if ($result) {
            $this->fireModelEvent('deleted');
        }
        return $result;
    }
}

// Usage in model
class User extends ActiveRecord
{
    protected function onCreating(): void
    {
        $this->created_at = date('Y-m-d H:i:s');
    }
    
    protected function onUpdating(): void
    {
        $this->updated_at = date('Y-m-d H:i:s');
    }
    
    protected function onDeleting(): void
    {
        // Soft delete instead
        $this->deleted_at = date('Y-m-d H:i:s');
        $this->save();
        return false; // Prevent actual deletion
    }
}
?>

Validation

<?php
trait Validatable
{
    protected $rules = [];
    protected $errors = [];
    
    public function validate(): bool
    {
        $this->errors = [];
        
        foreach ($this->rules as $field => $rules) {
            $value = $this->getAttribute($field);
            $fieldRules = explode('|', $rules);
            
            foreach ($fieldRules as $rule) {
                if (!$this->validateRule($field, $value, $rule)) {
                    break; // Stop on first error for this field
                }
            }
        }
        
        return empty($this->errors);
    }
    
    private function validateRule(string $field, $value, string $rule): bool
    {
        if ($rule === 'required' && empty($value)) {
            $this->errors[$field][] = "{$field} is required";
            return false;
        }
        
        if (strpos($rule, 'min:') === 0) {
            $min = (int) substr($rule, 4);
            if (strlen($value) < $min) {
                $this->errors[$field][] = "{$field} must be at least {$min} characters";
                return false;
            }
        }
        
        if (strpos($rule, 'max:') === 0) {
            $max = (int) substr($rule, 4);
            if (strlen($value) > $max) {
                $this->errors[$field][] = "{$field} must not exceed {$max} characters";
                return false;
            }
        }
        
        if ($rule === 'email' && !filter_var($value, FILTER_VALIDATE_EMAIL)) {
            $this->errors[$field][] = "{$field} must be a valid email address";
            return false;
        }
        
        return true;
    }
    
    public function getErrors(): array
    {
        return $this->errors;
    }
    
    public function save(): bool
    {
        if (!$this->validate()) {
            return false;
        }
        
        return parent::save();
    }
}

// Usage
class User extends ActiveRecord
{
    use Validatable;
    
    protected $rules = [
        'name' => 'required|min:2|max:50',
        'email' => 'required|email',
        'password' => 'required|min:8'
    ];
}
?>

Migration Integration

<?php
class Migration
{
    protected $connection;
    
    public function __construct(PDO $connection)
    {
        $this->connection = $connection;
    }
    
    public function createUsersTable(): void
    {
        $sql = "
            CREATE TABLE users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                email VARCHAR(255) NOT NULL UNIQUE,
                password VARCHAR(255) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            )
        ";
        $this->connection->exec($sql);
    }
    
    public function createPostsTable(): void
    {
        $sql = "
            CREATE TABLE posts (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                title VARCHAR(255) NOT NULL,
                content TEXT,
                published_at TIMESTAMP NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            )
        ";
        $this->connection->exec($sql);
    }
}
?>

Usage Examples

Basic CRUD Operations

<?php
// Setup connection
$pdo = new PDO('mysql:host=localhost;dbname=myapp', $username, $password);
ActiveRecord::setConnection($pdo);

// Create
$user = new User([
    'name' => 'John Doe',
    'email' => '[email protected]',
    'password' => 'secret123'
]);
$user->save();

// Read
$user = User::find(1);
$users = User::where('status', '=', 'active')->get();
$user = User::findByEmail('[email protected]');

// Update
$user = User::find(1);
$user->name = 'Jane Doe';
$user->save();

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

Working with Relationships

<?php
// Get user's posts
$user = User::find(1);
$posts = $user->posts()->get();

// Create a post for user
$post = $user->posts()->create([
    'title' => 'My First Post',
    'content' => 'This is my first blog post.'
]);

// Get post's author
$post = Post::find(1);
$author = $post->user()->get();

// Many-to-many relationships
$post = Post::find(1);
$tags = $post->tags()->get();

// Attach tags to post
$post->tags()->attach([1, 2, 3]);

// Sync tags (replace all existing)
$post->tags()->sync([2, 3, 4]);
?>

Advanced Querying

<?php
// Chaining query methods
$users = User::where('status', '=', 'active')
    ->where('created_at', '>', '2023-01-01')
    ->orderBy('name', 'ASC')
    ->limit(10)
    ->get();

// Using scopes
$activeUsers = User::active()->get();
$userPosts = Post::byUser(1)->published()->get();

// Counting relationships
$user = User::find(1);
$postCount = $user->posts()->count();

// Complex queries with joins
$users = User::where('posts.published_at', 'IS NOT', null)
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->get();
?>

Best Practices

  1. Use Relationships: Define relationships in models for clean code
  2. Validate Data: Always validate before saving
  3. Use Scopes: Create reusable query scopes
  4. Handle Events: Use model events for automatic behavior
  5. Mass Assignment Protection: Use fillable/guarded properties
  6. Eager Loading: Load relationships efficiently to avoid N+1 queries
  7. Database Indexes: Ensure proper indexing for relationship keys
  8. Transaction Management: Use transactions for complex operations

ORM and ActiveRecord patterns provide elegant database interactions while maintaining object-oriented principles. They reduce boilerplate code and make database operations more intuitive and maintainable.