Zend Framework 1.x 中 Zend_Db_Table_Abstract 是資料表抽象層,配合 Zend_Db_Select 可構建複雜的多表關聯查詢。本文通過實戰範例詳細講解 Join 操作與查詢構建技巧。
-- 充值日誌表
CREATE TABLE `charge_logs` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`charge_id` INT(11) NOT NULL,
`title` TEXT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 用戶充值記錄表
CREATE TABLE `user_charges` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT(10) NOT NULL,
`charge_type` INT(3) NOT NULL,
`charge_subtype` INT(3) NOT NULL,
`charge_credits` INT(3) NOT NULL,
`buy_date` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`valid_to` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`next_charge_date` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`payment` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
// 在 Table 類別內部
$select = $this->getAdapter()->select();
$select->from(['uc' => 'user_charges'])
->joinLeft(['cl' => 'charge_logs'], 'uc.id = cl.charge_id', ['title'])
->where('uc.user_id = ?', $userId);
return $this->getAdapter()->fetchAll($select);
// 在 Data Mapper 類別中
$select = $this->getDbTable()->getAdapter()->select();
$select->from(['uc' => 'user_charges'])
->joinLeft(['cl' => 'charge_logs'], 'uc.id = cl.charge_id', ['title'])
->where('uc.user_id = ?', $userId);
return $this->getDbTable()->getAdapter()->fetchAll($select);
| 方法 | SQL 對應 | 語義 | 適用場景 |
|---|---|---|---|
join() / joinInner() | INNER JOIN | 雙方都必須有匹配行 | 主從表強關聯,如訂單-訂單項 |
joinLeft() | LEFT JOIN | 左表全保留,右表無匹配則 NULL | 主表帶從屬資訊,如用戶-資料 |
joinRight() | RIGHT JOIN | 右表全保留,左表無匹配則 NULL | 少用,可改寫為 LEFT JOIN |
joinFull() | FULL JOIN | 雙表全保留(MySQL 不支援,需 UNION 模擬) | 雙向完整保留場景 |
joinCross() | CROSS JOIN | 笛卡爾積 | 報表矩陣、測試資料生成 |
$select = $this->getAdapter()->select()
->from(['u' => 'users'], ['id', 'username', 'email'])
->joinLeft(['p' => 'profiles'], 'u.id = p.user_id', ['avatar', 'bio'])
->joinLeft(['o' => 'orders'], 'u.id = o.user_id', ['COUNT(*) as order_count'])
->joinLeft(['oi' => 'order_items'], 'o.id = oi.order_id', ['SUM(oi.price) as total_spent'])
->where('u.status = ?', 'active')
->group('u.id')
->order('total_spent DESC')
->limit(20);
$select = $this->getAdapter()->select()->from(['u' => 'users']);
if ($includeProfile) {
$select->joinLeft(['p' => 'profiles'], 'u.id = p.user_id', ['avatar']);
}
if ($includeOrders) {
$select->joinLeft(['o' => 'orders'], 'u.id = o.user_id AND o.status = "completed"', []);
}
$select->where('u.id = ?', $userId);
// 明確指定欄位並重命名避免衝突
$select->from(['u' => 'users'], ['user_id' => 'id', 'user_name' => 'name'])
->joinLeft(['p' => 'posts'], 'u.id = p.author_id',
['post_id' => 'id', 'post_title' => 'title', 'post_created' => 'created_at']);
$select->from(['u' => 'users'], [
'id',
'full_name' => new Zend_Db_Expr("CONCAT(first_name, ' ', last_name)"),
'age' => new Zend_Db_Expr("TIMESTAMPDIFF(YEAR, birth_date, CURDATE())"),
'is_active' => new Zend_Db_Expr("CASE WHEN status='active' THEN 1 ELSE 0 END")
]);
Array
(
[0] => Array
(
[id] => 1
[user_id] => 1
[charge_type] => 1
[charge_subtype] => 1
[charge_credits] => 100
[buy_date] => 2011-09-09 00:00:00
[valid_to] => 2011-09-16 00:00:00
[next_charge_date] => 0000-00-00 00:00:00
[payment] => paypal
[title] => XXXXXXX
)
)
SELECT *,第三參數陣列精確指定charge_logs.charge_id、user_charges.user_id 必須有索引limitPage() 或手動 LIMIT/OFFSET,避免記憶體溢出?,絕不拼接變數echo $select->__toString(); 或 (string)$select 輸出實際 SQLEXPLAIN 分析索引使用情況Zend_Db_Select 提供了流暢介面構建複雜查詢,配合 Zend_Db_Table_Abstract 的資料映射能力,能優雅地處理企業級應用的多表關聯查詢需求。掌握其 Join 機制與查詢構建模式,是 ZF1 開發者的核心技能。