Bài 26: Model – Làm việc với Database

Bài 25 xây dựng Controller điều phối logic. Bài này đi sâu vào Model — nơi tương tác với database, xử lý business logic, validate dữ liệu. Model tốt phải độc lập với framework, dễ test, tái sử dụng. Bạn sẽ xây dựng Base Model với query builder đơn giản, relationships và các pattern thực tế.

1. Vai trò của Model trong MVC

💡 Fat Model, Thin Controller: Logic nghiệp vụ phức tạp (tính điểm, kiểm tra điều kiện, format dữ liệu) nằm trong Model. Controller chỉ gọi Model và truyền kết quả cho View.

2. Base Model — Foundation

<?php
// File: app/Models/BaseModel.php
namespace App\Models;

use PDO;

abstract class BaseModel {
    protected PDO $db;
    protected string $table;
    protected string $primaryKey = 'id';
    protected array $fillable = [];
    protected array $guarded = ['id', 'created_at', 'updated_at'];
    protected bool $timestamps = true;
    
    public function __construct() {
        $this->db = db();
    }
    
    /**
     * Find record by ID
     */
    public function find(int $id): ?array {
        $stmt = $this->db->prepare("SELECT * FROM {$this->table} WHERE {$this->primaryKey} = ?");
        $stmt->execute([$id]);
        $result = $stmt->fetch();
        return $result ?: null;
    }
    
    /**
     * Find or fail
     */
    public function findOrFail(int $id): array {
        $result = $this->find($id);
        if (!$result) {
            throw new \RuntimeException("Record not found with ID: {$id}");
        }
        return $result;
    }
    
    /**
     * Get all records
     */
    public function all(): array {
        $stmt = $this->db->query("SELECT * FROM {$this->table}");
        return $stmt->fetchAll();
    }
    
    /**
     * Paginate records
     */
    public function paginate(int $page = 1, int $perPage = 15): array {
        $offset = ($page - 1) * $perPage;
        
        // Get total
        $total = $this->count();
        
        // Get items
        $stmt = $this->db->prepare("SELECT * FROM {$this->table} LIMIT ? OFFSET ?");
        $stmt->execute([$perPage, $offset]);
        $items = $stmt->fetchAll();
        
        return [
            'items' => $items,
            'total' => $total,
            'per_page' => $perPage,
            'current_page' => $page,
            'last_page' => ceil($total / $perPage),
            'from' => $offset + 1,
            'to' => min($offset + $perPage, $total),
        ];
    }
    
    /**
     * Count all records
     */
    public function count(): int {
        $stmt = $this->db->query("SELECT COUNT(*) FROM {$this->table}");
        return (int)$stmt->fetchColumn();
    }
    
    /**
     * Create new record
     */
    public function create(array $data): int {
        // Filter only fillable fields
        $data = $this->filterFillable($data);
        
        // Add timestamps
        if ($this->timestamps) {
            $data['created_at'] = date('Y-m-d H:i:s');
            $data['updated_at'] = date('Y-m-d H:i:s');
        }
        
        // Build query
        $fields = array_keys($data);
        $placeholders = array_fill(0, count($fields), '?');
        
        $sql = sprintf(
            "INSERT INTO %s (%s) VALUES (%s)",
            $this->table,
            implode(', ', $fields),
            implode(', ', $placeholders)
        );
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute(array_values($data));
        
        return (int)$this->db->lastInsertId();
    }
    
    /**
     * Update record
     */
    public function update(int $id, array $data): bool {
        // Filter fillable
        $data = $this->filterFillable($data);
        
        // Add updated_at
        if ($this->timestamps) {
            $data['updated_at'] = date('Y-m-d H:i:s');
        }
        
        // Build query
        $fields = array_keys($data);
        $sets = array_map(fn($f) => "{$f} = ?", $fields);
        
        $sql = sprintf(
            "UPDATE %s SET %s WHERE %s = ?",
            $this->table,
            implode(', ', $sets),
            $this->primaryKey
        );
        
        $values = array_values($data);
        $values[] = $id;
        
        $stmt = $this->db->prepare($sql);
        return $stmt->execute($values);
    }
    
    /**
     * Delete record
     */
    public function delete(int $id): bool {
        $stmt = $this->db->prepare("DELETE FROM {$this->table} WHERE {$this->primaryKey} = ?");
        return $stmt->execute([$id]);
    }
    
    /**
     * Filter only fillable fields
     */
    protected function filterFillable(array $data): array {
        // If fillable is defined, use it
        if (!empty($this->fillable)) {
            return array_intersect_key($data, array_flip($this->fillable));
        }
        
        // Otherwise, exclude guarded
        return array_diff_key($data, array_flip($this->guarded));
    }
    
    /**
     * Where clause
     */
    public function where(string $column, $operator, $value = null): self {
        // Handle 2-parameter version: where('name', 'John')
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }
        
        // Store for chaining (simplified - full implementation would need QueryBuilder)
        return $this;
    }
    
    /**
     * First matching record
     */
    public function first(): ?array {
        $stmt = $this->db->prepare("SELECT * FROM {$this->table} LIMIT 1");
        $stmt->execute();
        $result = $stmt->fetch();
        return $result ?: null;
    }
    
    /**
     * Check if record exists
     */
    public function exists(string $column, $value): bool {
        $stmt = $this->db->prepare("SELECT COUNT(*) FROM {$this->table} WHERE {$column} = ?");
        $stmt->execute([$value]);
        return (int)$stmt->fetchColumn() > 0;
    }
}
?>

3. Concrete Model — SinhVien

<?php
// File: app/Models/SinhVienModel.php
namespace App\Models;

class SinhVienModel extends BaseModel {
    protected string $table = 'sinhvien';
    protected array $fillable = ['mssv', 'ho_ten', 'email', 'lop', 'diem_tb', 'ghi_chu'];
    
    /**
     * Search students
     */
    public function search(string $keyword, int $page = 1, int $perPage = 15): array {
        $offset = ($page - 1) * $perPage;
        $like = "%{$keyword}%";
        
        // Count matching records
        $stmt = $this->db->prepare(
            "SELECT COUNT(*) FROM {$this->table}
             WHERE mssv LIKE ? OR ho_ten LIKE ? OR email LIKE ? OR lop LIKE ?"
        );
        $stmt->execute([$like, $like, $like, $like]);
        $total = (int)$stmt->fetchColumn();
        
        // Get matching records
        $stmt = $this->db->prepare(
            "SELECT * FROM {$this->table}
             WHERE mssv LIKE ? OR ho_ten LIKE ? OR email LIKE ? OR lop LIKE ?
             ORDER BY ho_ten LIMIT ? OFFSET ?"
        );
        $stmt->execute([$like, $like, $like, $like, $perPage, $offset]);
        $items = $stmt->fetchAll();
        
        return [
            'items' => $items,
            'total' => $total,
            'per_page' => $perPage,
            'current_page' => $page,
            'last_page' => ceil($total / $perPage),
            'keyword' => $keyword,
        ];
    }
    
    /**
     * Find by MSSV
     */
    public function findByMSSV(string $mssv): ?array {
        $stmt = $this->db->prepare("SELECT * FROM {$this->table} WHERE mssv = ?");
        $stmt->execute([strtoupper($mssv)]);
        $result = $stmt->fetch();
        return $result ?: null;
    }
    
    /**
     * Get students by class
     */
    public function getByClass(string $lop): array {
        $stmt = $this->db->prepare("SELECT * FROM {$this->table} WHERE lop = ? ORDER BY ho_ten");
        $stmt->execute([$lop]);
        return $stmt->fetchAll();
    }
    
    /**
     * Scope: Excellent students (diem_tb >= 9.0)
     */
    public function scopeExcellent(): array {
        $stmt = $this->db->query(
            "SELECT * FROM {$this->table} WHERE diem_tb >= 9.0 ORDER BY diem_tb DESC"
        );
        return $stmt->fetchAll();
    }
    
    /**
     * Scope: Good students (diem_tb >= 8.0)
     */
    public function scopeGood(): array {
        $stmt = $this->db->query(
            "SELECT * FROM {$this->table} WHERE diem_tb >= 8.0 ORDER BY diem_tb DESC"
        );
        return $stmt->fetchAll();
    }
    
    /**
     * Scope: Failed students (diem_tb < 5.0)
     */
    public function scopeFailed(): array {
        $stmt = $this->db->query(
            "SELECT * FROM {$this->table} WHERE diem_tb < 5.0 ORDER BY diem_tb ASC"
        );
        return $stmt->fetchAll();
    }
    
    /**
     * Statistics by classification
     */
    public function getStatsByClassification(): array {
        $sql = "
            SELECT 
                CASE 
                    WHEN diem_tb >= 9.0 THEN 'Xuất sắc'
                    WHEN diem_tb >= 8.0 THEN 'Giỏi'
                    WHEN diem_tb >= 6.5 THEN 'Khá'
                    WHEN diem_tb >= 5.0 THEN 'Trung bình'
                    ELSE 'Yếu'
                END as xep_loai,
                COUNT(*) as so_luong,
                ROUND(AVG(diem_tb), 2) as diem_tb_trung_binh
            FROM {$this->table}
            GROUP BY xep_loai
            ORDER BY 
                CASE xep_loai
                    WHEN 'Xuất sắc' THEN 1
                    WHEN 'Giỏi' THEN 2
                    WHEN 'Khá' THEN 3
                    WHEN 'Trung bình' THEN 4
                    WHEN 'Yếu' THEN 5
                END
        ";
        
        $stmt = $this->db->query($sql);
        return $stmt->fetchAll();
    }
    
    /**
     * Get all classes
     */
    public function getAllClasses(): array {
        $stmt = $this->db->query(
            "SELECT DISTINCT lop FROM {$this->table} ORDER BY lop"
        );
        return array_column($stmt->fetchAll(), 'lop');
    }
    
    /**
     * Validate student data
     */
    public function validate(array $data, ?int $excludeId = null): array {
        $errors = [];
        
        // MSSV
        if (empty($data['mssv'])) {
            $errors['mssv'] = 'MSSV không được để trống';
        } elseif (!preg_match('/^[A-Z]{2}[0-9]{7}$/', strtoupper($data['mssv']))) {
            $errors['mssv'] = 'MSSV phải có dạng XX0000000';
        } else {
            // Check unique
            $stmt = $this->db->prepare(
                "SELECT COUNT(*) FROM {$this->table} WHERE mssv = ?" . 
                ($excludeId ? " AND id != ?" : "")
            );
            $params = [strtoupper($data['mssv'])];
            if ($excludeId) $params[] = $excludeId;
            $stmt->execute($params);
            
            if ((int)$stmt->fetchColumn() > 0) {
                $errors['mssv'] = 'MSSV đã tồn tại';
            }
        }
        
        // Ho ten
        if (empty($data['ho_ten'])) {
            $errors['ho_ten'] = 'Họ tên không được để trống';
        } elseif (mb_strlen($data['ho_ten'], 'UTF-8') < 2) {
            $errors['ho_ten'] = 'Họ tên phải có ít nhất 2 ký tự';
        }
        
        // Email
        if (empty($data['email'])) {
            $errors['email'] = 'Email không được để trống';
        } elseif (!filter_var($data['email'], FILTER_VALIDATE_EMAIL)) {
            $errors['email'] = 'Email không đúng định dạng';
        } else {
            // Check unique
            $stmt = $this->db->prepare(
                "SELECT COUNT(*) FROM {$this->table} WHERE email = ?" . 
                ($excludeId ? " AND id != ?" : "")
            );
            $params = [strtolower($data['email'])];
            if ($excludeId) $params[] = $excludeId;
            $stmt->execute($params);
            
            if ((int)$stmt->fetchColumn() > 0) {
                $errors['email'] = 'Email đã tồn tại';
            }
        }
        
        // Lop
        if (empty($data['lop'])) {
            $errors['lop'] = 'Lớp không được để trống';
        }
        
        // Diem TB
        if (isset($data['diem_tb']) && $data['diem_tb'] !== '') {
            $diem = (float)$data['diem_tb'];
            if ($diem < 0 || $diem > 10) {
                $errors['diem_tb'] = 'Điểm TB phải từ 0 đến 10';
            }
        }
        
        return $errors;
    }
    
    /**
     * Compute classification from diem_tb
     */
    public function getClassification(float $diemTB): string {
        return match(true) {
            $diemTB >= 9.0 => 'Xuất sắc',
            $diemTB >= 8.0 => 'Giỏi',
            $diemTB >= 6.5 => 'Khá',
            $diemTB >= 5.0 => 'Trung bình',
            default => 'Yếu',
        };
    }
    
    /**
     * Normalize data before saving
     */
    public function normalizeData(array $data): array {
        if (isset($data['mssv'])) {
            $data['mssv'] = strtoupper(trim($data['mssv']));
        }
        
        if (isset($data['ho_ten'])) {
            // Normalize name: trim, single space, title case
            $name = trim(preg_replace('/\s+/', ' ', $data['ho_ten']));
            $data['ho_ten'] = mb_convert_case($name, MB_CASE_TITLE, 'UTF-8');
        }
        
        if (isset($data['email'])) {
            $data['email'] = strtolower(trim($data['email']));
        }
        
        if (isset($data['lop'])) {
            $data['lop'] = strtoupper(trim($data['lop']));
        }
        
        return $data;
    }
}
?>

4. Query Builder — Chainable Queries

<?php
// File: app/Database/QueryBuilder.php
namespace App\Database;

use PDO;

class QueryBuilder {
    protected PDO $db;
    protected string $table;
    protected array $wheres = [];
    protected array $bindings = [];
    protected ?string $orderBy = null;
    protected ?int $limit = null;
    protected ?int $offset = null;
    
    public function __construct(PDO $db, string $table) {
        $this->db = $db;
        $this->table = $table;
    }
    
    /**
     * Add WHERE clause
     */
    public function where(string $column, $operator, $value = null): self {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }
        
        $this->wheres[] = "{$column} {$operator} ?";
        $this->bindings[] = $value;
        
        return $this;
    }
    
    /**
     * Add WHERE LIKE clause
     */
    public function whereLike(string $column, string $value): self {
        $this->wheres[] = "{$column} LIKE ?";
        $this->bindings[] = "%{$value}%";
        return $this;
    }
    
    /**
     * Add WHERE IN clause
     */
    public function whereIn(string $column, array $values): self {
        $placeholders = implode(',', array_fill(0, count($values), '?'));
        $this->wheres[] = "{$column} IN ({$placeholders})";
        $this->bindings = array_merge($this->bindings, $values);
        return $this;
    }
    
    /**
     * Add WHERE NULL
     */
    public function whereNull(string $column): self {
        $this->wheres[] = "{$column} IS NULL";
        return $this;
    }
    
    /**
     * Add WHERE NOT NULL
     */
    public function whereNotNull(string $column): self {
        $this->wheres[] = "{$column} IS NOT NULL";
        return $this;
    }
    
    /**
     * Set ORDER BY
     */
    public function orderBy(string $column, string $direction = 'ASC'): self {
        $direction = strtoupper($direction);
        $this->orderBy = "{$column} {$direction}";
        return $this;
    }
    
    /**
     * Set LIMIT
     */
    public function limit(int $limit): self {
        $this->limit = $limit;
        return $this;
    }
    
    /**
     * Set OFFSET
     */
    public function offset(int $offset): self {
        $this->offset = $offset;
        return $this;
    }
    
    /**
     * Build SQL query
     */
    protected function buildQuery(): string {
        $sql = "SELECT * FROM {$this->table}";
        
        if (!empty($this->wheres)) {
            $sql .= " WHERE " . implode(' AND ', $this->wheres);
        }
        
        if ($this->orderBy) {
            $sql .= " ORDER BY {$this->orderBy}";
        }
        
        if ($this->limit !== null) {
            $sql .= " LIMIT {$this->limit}";
        }
        
        if ($this->offset !== null) {
            $sql .= " OFFSET {$this->offset}";
        }
        
        return $sql;
    }
    
    /**
     * Execute and get all results
     */
    public function get(): array {
        $sql = $this->buildQuery();
        $stmt = $this->db->prepare($sql);
        $stmt->execute($this->bindings);
        return $stmt->fetchAll();
    }
    
    /**
     * Get first result
     */
    public function first(): ?array {
        $this->limit(1);
        $results = $this->get();
        return $results[0] ?? null;
    }
    
    /**
     * Count results
     */
    public function count(): int {
        $sql = "SELECT COUNT(*) FROM {$this->table}";
        
        if (!empty($this->wheres)) {
            $sql .= " WHERE " . implode(' AND ', $this->wheres);
        }
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($this->bindings);
        return (int)$stmt->fetchColumn();
    }
}

// Sử dụng:
$builder = new QueryBuilder($pdo, 'sinhvien');
$students = $builder
    ->where('lop', 'CNTT01')
    ->where('diem_tb', '>=', 8.0)
    ->orderBy('diem_tb', 'DESC')
    ->limit(10)
    ->get();
?>

5. Relationships — Liên kết bảng

<?php
// File: app/Models/SinhVienModel.php (bổ sung)

/**
 * Get student's grades (one-to-many)
 */
public function getDiem(int $sinhVienId): array {
    $stmt = $this->db->prepare(
        "SELECT d.*, m.ten_mon 
         FROM diem d
         INNER JOIN monhoc m ON d.mon_hoc_id = m.id
         WHERE d.sinh_vien_id = ?
         ORDER BY m.ten_mon"
    );
    $stmt->execute([$sinhVienId]);
    return $stmt->fetchAll();
}

/**
 * Get student with all grades
 */
public function findWithGrades(int $id): ?array {
    $student = $this->find($id);
    if (!$student) return null;
    
    $student['diem'] = $this->getDiem($id);
    return $student;
}

// ---

// File: app/Models/DiemModel.php
namespace App\Models;

class DiemModel extends BaseModel {
    protected string $table = 'diem';
    protected array $fillable = ['sinh_vien_id', 'mon_hoc_id', 'diem_giua_ky', 'diem_cuoi_ky'];
    
    /**
     * Get grade with student info (belongs-to)
     */
    public function findWithStudent(int $id): ?array {
        $stmt = $this->db->prepare(
            "SELECT d.*, s.ho_ten, s.mssv, s.lop, m.ten_mon
             FROM diem d
             INNER JOIN sinhvien s ON d.sinh_vien_id = s.id
             INNER JOIN monhoc m ON d.mon_hoc_id = m.id
             WHERE d.id = ?"
        );
        $stmt->execute([$id]);
        $result = $stmt->fetch();
        return $result ?: null;
    }
    
    /**
     * Calculate final grade
     */
    public function calculateFinal(array $diem, float $heSoGK = 0.4): float {
        $heSoCK = 1 - $heSoGK;
        return round(
            $diem['diem_giua_ky'] * $heSoGK + $diem['diem_cuoi_ky'] * $heSoCK,
            2
        );
    }
}
?>

6. Accessors & Mutators

<?php
// File: app/Models/SinhVienModel.php (bổ sung)

/**
 * Accessor: Get formatted name
 */
public function getFormattedName(array $student): string {
    return "[{$student['mssv']}] {$student['ho_ten']}";
}

/**
 * Accessor: Get classification
 */
public function getXepLoaiAttribute(array $student): string {
    return $this->getClassification($student['diem_tb'] ?? 0);
}

/**
 * Accessor: Get full info as array
 */
public function toArray(array $student): array {
    return [
        'id' => $student['id'],
        'mssv' => $student['mssv'],
        'ho_ten' => $student['ho_ten'],
        'email' => $student['email'],
        'lop' => $student['lop'],
        'diem_tb' => $student['diem_tb'] ?? null,
        'xep_loai' => $this->getXepLoaiAttribute($student),
        'formatted_name' => $this->getFormattedName($student),
    ];
}

/**
 * Mutator: Set MSSV (always uppercase)
 */
public function setMSSV(string $mssv): string {
    return strtoupper(trim($mssv));
}

/**
 * Mutator: Set email (always lowercase)
 */
public function setEmail(string $email): string {
    return strtolower(trim($email));
}
?>

7. Soft Deletes

<?php
// File: app/Models/BaseModel.php (bổ sung)

protected bool $softDeletes = false;

/**
 * Soft delete record
 */
public function softDelete(int $id): bool {
    if (!$this->softDeletes) {
        throw new \RuntimeException("Soft deletes not enabled on this model");
    }
    
    $stmt = $this->db->prepare(
        "UPDATE {$this->table} SET deleted_at = NOW() WHERE {$this->primaryKey} = ?"
    );
    return $stmt->execute([$id]);
}

/**
 * Restore soft deleted record
 */
public function restore(int $id): bool {
    if (!$this->softDeletes) {
        throw new \RuntimeException("Soft deletes not enabled on this model");
    }
    
    $stmt = $this->db->prepare(
        "UPDATE {$this->table} SET deleted_at = NULL WHERE {$this->primaryKey} = ?"
    );
    return $stmt->execute([$id]);
}

/**
 * Get only non-deleted records
 */
public function allActive(): array {
    $where = $this->softDeletes ? "WHERE deleted_at IS NULL" : "";
    $stmt = $this->db->query("SELECT * FROM {$this->table} {$where}");
    return $stmt->fetchAll();
}
?>

Tóm tắt

• ✅ Base Model: CRUD cơ bản, fillable/guarded, timestamps
• ✅ Query Builder: chainable methods (where, orderBy, limit)
• ✅ Relationships: one-to-many, belongs-to với JOIN
• ✅ Scopes: methods tái sử dụng query (scopeExcellent, scopeGood)
• ✅ Accessors: computed attributes (getXepLoaiAttribute)
• ✅ Mutators: transform data khi set (setMSSV, setEmail)
• ✅ Validation: validate() method trong Model
• ✅ Normalization: chuẩn hóa dữ liệu trước khi lưu
• ✅ Soft Deletes: xóa mềm với deleted_at

🎯 Bài tập thực hành

1. Xây dựng MonHocModel: Tạo MonHocModel extends BaseModel với bảng monhoc (id, ma_mon, ten_mon, so_tin_chi). Thêm methods: findByMaMon(), getByTinChi(), validate(). Implement relationship với DiemModel.
2. Query Builder nâng cao: Bổ sung QueryBuilder với: whereNotIn(), whereBetween(), orWhere(), groupBy(), having(). Test với query phức tạp: lấy sinh viên lớp CNTT01 hoặc CNTT02, điểm từ 7-9, sắp xếp theo tên.
3. Statistics Dashboard: Trong SinhVienModel, thêm methods: getTopStudents($limit = 10), getAverageByClass(), getMonthlyRegistrations(), getGenderRatio(). Tạo trang dashboard hiển thị các thống kê này.