資料庫關聯類型對應 AR 關聯
| 資料庫關聯 | AR 關聯類型 | 語意 | 範例 |
|---|
| 一對多 (1:N) | HAS_MANY | 擁有多個關聯物件 | User HAS_MANY Post |
| 多對一 (N:1) | BELONGS_TO | 屬於某個關聯物件 | Post BELONGS_TO User |
| 一對一 (1:1) | HAS_ONE | 最多擁有一個關聯物件 | User HAS_ONE Profile |
| 多對多 (M:N) | MANY_MANY | 透過中間表關聯 | Post MANY_MANY Tag (tbl_post_tag) |
關聯定義語法
public function relations() {
return [
'關聯名稱' => [關聯類型, '關聯類別名', '外鍵欄位', '額外選項...'],
];
}
實戰範例：Employee (1) → Customer (N)
Chinook 資料庫：Customer.SupportRepId 參考 Employee.EmployeeId。
1. 定義關聯
// models/Employee.php
class Employee extends CActiveRecord {
// ... model(), tableName()
public function relations() {
return [
'customers' => [self::HAS_MANY, 'Customer', 'SupportRepId'],
];
}
}
// models/Customer.php
class Customer extends CActiveRecord {
// ... model(), tableName()
public function relations() {
return [
'supportRep' => [self::BELONGS_TO, 'Employee', 'SupportRepId'],
];
}
}
2. 關聯查詢與導航
// Controller: 查詢 EmployeeId=4 的員工及其負責客戶
public function actionIndex() {
// 方式一：預載入 (Eager Loading) - 推薦，單次查詢
$employee = Employee::model()->with('customers')->findByPk(4);
// 方式二：延遲載入 - 存取 $employee->customers 時自動查詢 (N+1 風險)
// $employee = Employee::model()->findByPk(4);
$this->render('index', ['model' => $employee->customers]);
}
// View: 迭代關聯集合
FirstName . ' ' . $customer->LastName; ?>
Address; ?>
Email; ?>
關聯查詢進階選項
| 選項 | 說明 | 範例 |
|---|
| select | 指定關聯表查詢欄位 | 'select'=>'FirstName, LastName, Email' |
| condition | 額外 WHERE 條件 | 'condition'=>'Country="USA"' |
| order | 排序 | 'order'=>'LastName ASC' |
| with | 遞迴預載入關聯的關聯 | 'with'=>'orders' |
| joinType | JOIN 類型 (預設 LEFT JOIN) | 'joinType'=>'INNER JOIN' |
| alias | 表別名 (避免欄位衝突) | 'alias'=>'cust' |
| params | 參數綁定 | 'params'=>[':country'=>'USA'] |
| on | 自定義 ON 條件 (覆蓋外鍵) | 'on'=>'cust.IsActive=1' |
| index | 陣列索引鍵 (預設 0,1,2...) | 'index'=>'CustomerId' |
| scopes | 套用關聯模型的 named scopes | 'scopes'=>'recent' |
完整關聯定義範例
public function relations() {
return [
'customers' => [
self::HAS_MANY,
'Customer',
'SupportRepId',
'order' => 'LastName, FirstName',
'condition' => 'Country="USA"',
'with' => 'invoices', // Customer 進一步關聯 Invoice
],
'manager' => [
self::BELONGS_TO,
'Employee',
'ReportsTo',
'alias' => 'mgr',
],
];
}
命名範圍 簡化常用查詢
class Customer extends CActiveRecord {
public function scopes() {
return [
'active' => ['condition' => 'IsActive=1'],
'recent' => ['order' => 'CreateDate DESC', 'limit' => 10],
'byCountry' => function($country) {
return ['condition' => 'Country=:country', 'params' => [':country'=>$country]];
},
];
}
}
// 使用
$customers = Customer::model()->active()->recent()->findAll();
$usaCustomers = Customer::model()->byCountry('USA')->findAll();
統計查詢 (Statistical Query)
class Employee extends CActiveRecord {
public function relations() {
return [
'customers' => [self::HAS_MANY, 'Customer', 'SupportRepId'],
'customerCount' => [self::STAT, 'Customer', 'SupportRepId'], // COUNT(*)
'totalSales' => [self::STAT, 'Invoice', 'Customer.SupportRepId', 'type'=>'sum', 'attribute'=>'Total'], // SUM(Total)
];
}
}
// 使用
$emp = Employee::model()->findByPk(4);
echo $emp->customerCount; // 自動執行 COUNT 查詢
echo $emp->totalSales; // 自動執行 SUM 查詢 (需透過關聯路徑)
HAS_ONE vs BELONGS_TO 判斷口訣
「外鍵在誰身上，誰就 BELONGS_TO 另一方」
Customer 表有 SupportRepId → Customer BELONGS_TO Employee
Employee 表無外鍵 → Employee HAS_MANY Customer
常見錯誤與解決
- 關聯名稱與屬性名稱衝突：關聯名稱勿與表欄位同名，建議關聯用複數 (customers)、欄位用單數 (supportRepId)。
- 預載入失效：
with('customers') 必須與關聯名稱完全一致 (大小寫敏感)。
- MANY_MANY 中間表未指定：
'tags' => [self::MANY_MANY, 'Tag', 'tbl_post_tag(post_id, tag_id)'] 第三參數必須包含中間表名與兩側外鍵。
- 統計查詢路徑錯誤：
STAT 類型第三參數為關聯路徑，如 'Invoice', 'Customer.SupportRepId' 表示 Employee → Customer → Invoice。
