Yii 1.x CDbCriteria 使用大全與實戰技巧

CDbCriteria 簡介

CDbCriteria 是 Yii 1.x ActiveRecord 的查詢條件封裝類，提供流式介面建構複雜 SQL，比手寫 SQL 更安全、可維護。

常用條件方法

$criteria = new CDbCriteria();

// 基本 WHERE
$criteria->addCondition('id = 1');
$criteria->addCondition('status = :status');
$criteria->params[':status'] = 1;

// IN / NOT IN
$criteria->addInCondition('id', [1,2,3,4,5]);
$criteria->addNotInCondition('type', ['A','B']);

// OR 條件
$criteria->addCondition('id = 1', 'OR');

// LIKE 搜尋
$criteria->addSearchCondition('name', '分類'); // name LIKE '%分類%'

// BETWEEN
$criteria->addBetweenCondition('created_at', '2023-01-01', '2023-12-31');

// 智慧比較：自動判斷標量或陣列
$criteria->compare('id', 1);        // id = 1
$criteria->compare('id', [1,2,3]);  // id IN (1,2,3)

屬性設定（SELECT, JOIN, LIMIT 等）

$criteria->select = 'id, username, email';
$criteria->join   = 'LEFT JOIN profile ON profile.user_id = t.id';
$criteria->with   = 'profile';           // 關聯載入
$criteria->limit  = 10;
$criteria->offset = 20;                  // LIMIT 20, 10
$criteria->order  = 'created_at DESC';
$criteria->group  = 'category_id';
$criteria->having = 'COUNT(*) > 5';
$criteria->distinct = true;

實戰範例

分組統計

$criteria = new CDbCriteria();
$criteria->select = 'table_name, model_id, SUM(amount) AS total';
$criteria->group  = 'table_name, model_id';
$criteria->condition = 'idc_id = 4';
$results = AccessoryInfo::model()->findAll($criteria);

多表 JOIN + 動態條件

$c = new CDbCriteria();
$c->select = 't.id, t.created_at, t.outsource_id, t.user_id, t.operate, t.content';
$c->join   = 'LEFT JOIN outsource ON outsource.id = t.outsource_id';
$c->condition = 'outsource.idc_id IN (' . implode(',', $idc_ids) . ')';

if ($last_log_id) {
    $c->condition .= " AND t.id > $last_log_id";
}
$c->limit = 20;
$c->order = 't.id DESC';
$logs = OutsourceProcessLog::model()->findAll($c);

mergeWith：合併條件

// 在 Model 內部擴充預設 scope
$this->getDbCriteria()->mergeWith([
    'condition' => "idc_id IN ($ids)",
]);

注意事項

• 回傳物件：findAll 回傳 AR 物件陣列，非關聯陣列（DAO 回傳陣列）。
• 複雜聚合：如 SELECT AVG(num) ... 仍需手寫 SQL 或用 commandBuilder。
• 參數繫結：習慣用 params 陣列防止 SQL 注入。