PHP 資料庫 Model 類實作：__call() 魔術方法實現鏈式查詢構建器

設計目標

實現一個輕量級的 Active Record 模式 Model 基類，核心特性：

• 鏈式呼叫：$model->where(...)->order(...)->limit(...)->find()
• 動態方法攔截：利用 __call() 捕獲 where/order/limit 等查詢條件方法
• 自動結構探測：建構時自動 DESC table 獲取欄位列表，驗證查詢欄位合法性
• PDO/MySQLi 雙支援：範例採用 MySQLi，易遷移至 PDO

核心類別實作

<?php
// 配置常數 (實際專案建議用 .env + 設定類)
define('DB_HOST', '127.0.0.1');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'test_db');

class Model {
    /** @var mysqli 資料庫連線 */
    private $link;
    
    /** @var string 資料表名稱 */
    private $tableName;
    
    /** @var array 表欄位列表 (自動獲取) */
    private $columns = [];
    
    /** @var array 查詢條件緩衝區 */
    private $conditions = [
        'where'  => '',
        'order'  => '',
        'limit'  => '',
        'group'  => '',
        'having' => ''
    ];
    
    /** @var array 允許的動態方法映射 */
    private $allowedMethods = ['where', 'order', 'limit', 'group', 'having'];

    public function __construct(string $tableName) {
        $this->tableName = $tableName;
        $this->connect();
        $this->loadColumns();
    }
    
    private function connect(): void {
        $this->link = @mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        if (!$this->link) {
            throw new Exception("資料庫連線失敗: " . mysqli_connect_error());
        }
        mysqli_set_charset($this->link, 'utf8mb4');
    }
    
    /** 自動獲取表欄位 */
    private function loadColumns(): void {
        $sql = "DESC `{$this->tableName}`";
        $result = mysqli_query($this->link, $sql);
        if ($result) {
            while ($row = mysqli_fetch_assoc($result)) {
                $this->columns[] = $row['Field'];
            }
            mysqli_free_result($result);
        }
    }
    
    public function __destruct() {
        if ($this->link) mysqli_close($this->link);
    }

    /**
     * 魔術方法：攔截 where(), order(), limit() 等動態呼叫
     * 返回 $this 實現鏈式呼叫
     */
    public function __call(string $name, array $arguments) {
        $name = strtolower($name);
        
        if (!in_array($name, $this->allowedMethods, true)) {
            throw new BadMethodCallException("不支援的查詢方法: {$name}");
        }
        
        $value = $arguments[0] ?? '';
        
        switch ($name) {
            case 'where':
                $this->conditions['where'] = " WHERE {$value}";
                break;
            case 'order':
                $this->conditions['order'] = " ORDER BY {$value}";
                break;
            case 'limit':
                $this->conditions['limit'] = " LIMIT {$value}";
                break;
            case 'group':
                $this->conditions['group'] = " GROUP BY {$value}";
                break;
            case 'having':
                $this->conditions['having'] = " HAVING {$value}";
                break;
        }
        
        return $this; // 關鍵：返回自身實現鏈式
    }
    
    /** 組裝完整 SQL 條件片段 */
    private function buildConditions(): string {
        return implode(' ', $this->conditions);
    }
    
    /** 重置條件緩衝區 (支援複用 Model 實例) */
    public function reset(): self {
        $this->conditions = array_fill_keys(array_keys($this->conditions), '');
        return $this;
    }

    /**
     * 查詢單筆/多筆記錄
     * @param string|array $columns 欄位，* 或欄位名/陣列
     * @return array 關聯陣列結果集
     */
    public function find($columns = '*') {
        // 欄位驗證
        $colStr = $this->formatColumns($columns);
        
        $sql = "SELECT {$colStr} FROM `{$this->tableName}`" . $this->buildConditions();
        
        $result = mysqli_query($this->link, $sql);
        if (!$result) {
            throw new Exception("查詢失敗: " . mysqli_error($this->link) . " [SQL: {$sql}]");
        }
        
        $data = mysqli_fetch_all($result, MYSQLI_ASSOC);
        mysqli_free_result($result);
        
        // 查詢後自動重置條件，避免汙染下次查詢
        $this->reset();
        
        return $data;
    }
    
    /** 格式化欄位字串 */
    private function formatColumns($columns): string {
        if ($columns === '*') return '*';
        
        $cols = is_array($columns) ? $columns : [$columns];
        $validCols = [];
        
        foreach ($cols as $col) {
            if (in_array($col, $this->columns, true)) {
                $validCols[] = "`{$col}`";
            } else {
                trigger_error("欄位 {$col} 不存在於表 {$this->tableName}", E_USER_WARNING);
            }
        }
        
        return $validCols ? implode(', ', $validCols) : '*';
    }
    
    /** 獲取最後執行的 SQL (除錯用) */
    public function getLastSql(): string {
        return "SELECT * FROM `{$this->tableName}`" . $this->buildConditions();
    }
}

// === 使用範例 ===
try {
    // 1. 實例化 (對應資料表 students)
    $model = new Model('students');
    
    // 2. 鏈式查詢
    $results = $model
        ->where("status = 1 AND age > 18")
        ->order("score DESC, id ASC")
        ->limit("0, 10")
        ->find(['id', 'name', 'age', 'score']);
    
    echo "執行 SQL: {$model->getLastSql()}\n";
    print_r($results);
    
    // 3. 同一實例複用 (自動重置條件)
    $topStudent = $model
        ->where("class_id = 3")
        ->order("score DESC")
        ->limit(1)
        ->find('*');
    
} catch (Exception $e) {
    echo "錯誤: " . $e->getMessage();
}
?>

核心設計模式解析

1. __call() 實現動態查詢構建器

// 傳統寫法：每個條件一個方法
$model->setWhere(...)->setOrder(...)->setLimit(...)

// __call() 魔術方法：攔截不存在的方法呼叫
public function __call($name, $args) {
    if (in_array($name, $this->allowedMethods)) {
        $this->conditions[$name] = " {$name} " . $args[0];
        return $this; // 鏈式關鍵
    }
    throw new BadMethodCallException(...);
}

2. 條件緩衝區模式

• 各條件獨立儲存於 $this->conditions 陣列
• buildConditions() 組裝時按 SQL 語法順序拼接
• find() 執行後 自動重置，防止條件殘留汙染後續查詢
• reset() 公開方法支援手動重置

3. 欄位白名單驗證

// 建構時自動 DESC 表結構
$this->columns = ['id', 'name', 'age', 'score', 'class_id', 'status'];

// 查詢時驗證
private function formatColumns($cols) {
    return array_filter($cols, fn($c) => in_array($c, $this->columns));
}
// 防止 SQL 注入、拼寫錯誤、敏感欄位洩露 (如 password_hash)

進階擴展功能

新增/修改/刪除方法

// 新增
public function insert(array $data): int {
    $fields = implode('`, `', array_keys($data));
    $placeholders = implode(', ', array_fill(0, count($data), '?'));
    $sql = "INSERT INTO `{$this->tableName}` (`{$fields}`) VALUES ({$placeholders})";
    
    $stmt = mysqli_prepare($this->link, $sql);
    mysqli_stmt_bind_param($stmt, str_repeat('s', count($data)), ...array_values($data));
    mysqli_stmt_execute($stmt);
    
    $insertId = mysqli_insert_id($this->link);
    mysqli_stmt_close($stmt);
    return $insertId;
}

// 修改
public function update(array $data, string $where): int {
    $set = implode(', ', array_map(fn($k) => "`{$k}` = ?", array_keys($data)));
    $sql = "UPDATE `{$this->tableName}` SET {$set} WHERE {$where}";
    
    $stmt = mysqli_prepare($this->link, $sql);
    mysqli_stmt_bind_param($stmt, str_repeat('s', count($data)), ...array_values($data));
    mysqli_stmt_execute($stmt);
    
    $affected = mysqli_affected_rows($this->link);
    mysqli_stmt_close($stmt);
    return $affected;
}

// 刪除
public function delete(string $where): int {
    $sql = "DELETE FROM `{$this->tableName}` WHERE {$where}";
    mysqli_query($this->link, $sql);
    return mysqli_affected_rows($this->link);
}

支援關聯查詢 (Join)

// 擴展 conditions 陣列
private $conditions = [
    // ...
    'join' => ''
];

// __call 攔截 join
case 'join':
    $this->conditions['join'] = " {$value}"; // 如 "LEFT JOIN orders ON users.id = orders.user_id"
    break;

// buildConditions 調整順序
return $this->conditions['join'] . ' ' . implode(' ', [
    $this->conditions['where'],
    $this->conditions['group'],
    $this->conditions['having'],
    $this->conditions['order'],
    $this->conditions['limit']
]);

分頁封裝

public function paginate(int $page = 1, int $perPage = 15): array {
    $total = $this->count(); // SELECT COUNT(*)
    $this->limit(($page - 1) * $perPage . ", {$perPage}");
    $data = $this->find();
    
    return [
        'data' => $data,
        'total' => $total,
        'per_page' => $perPage,
        'current_page' => $page,
        'last_page' => ceil($total / $perPage)
    ];
}

private function count(): int {
    $sql = "SELECT COUNT(*) as cnt FROM `{$this->tableName}`" . $this->conditions['where'];
    $result = mysqli_query($this->link, $sql);
    $row = mysqli_fetch_assoc($result);
    return (int)$row['cnt'];
}

遷移至 PDO 版本 (關鍵差異)

總結

本 Model 基類展示了 PHP 魔術方法 __call() 構建流暢介面的經典模式。核心優勢：

• API 直觀：where()->order()->limit()->find() 符合自然語言閱讀習慣
• 安全預設：欄位白名單、自動重置條件、預處理語句防注入
• 輕量無依賴：純 PHP + MySQLi/PDO，適合中小專案或教學
• 易於擴展：條件緩衝區模式輕鬆支援 Join、Group、Having、Union 等

生產環境建議：引入連線池、查詢日誌、慢查詢監控、讀寫分離、模型關聯定義、資料驗證層、快取策略。或直接採用成熟 ORM (Eloquent, Doctrine, Cycle ORM) 獲得完整企業級功能。