Yii 2 實戰：資料庫配置、Gii 生成 Model/CRUD 與關聯查詢完整指南

資料庫連線配置詳解

Yii 2 使用 yii\db\Connection 元件管理資料庫連線。配置檔位置：

• Basic 模板：config/db.php
• Advanced 模板：common/config/main-local.php (建議放在 local 不進版控)

<?php
return [
    'class' => 'yii\db\Connection',
    'dsn' => 'mysql:host=127.0.0.1;dbname=yii2_demo;charset=utf8mb4',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8mb4',
    // 資料表前綴，ActiveRecord 自動處理 {{%table_name}}
    'tablePrefix' => 'tbl_',
    // 生產環境效能優化
    'enableSchemaCache' => true,
    'schemaCacheDuration' => 3600,
    'schemaCache' => 'cache', // 需在 components 配置 cache 元件 (Redis/APCu/File)
    // PDO 屬性
    'attributes' => [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false, // 使用原生預處理
        PDO::MYSQL_ATTR_SSL_CA => '/path/to/ca.pem', // 若需 SSL
    ],
    // 驅動選項 (MySQL 8.0+ 建議)
    'driverOptions' => [
        PDO::MYSQL_ATTR_COMPRESS => true, // 壓縮協議傳輸
    ],
];

啟用 Gii 代碼生成器

開發環境 config/web.php (Basic) 或 frontend/config/main-local.php (Advanced)：

$config['modules']['gii'] = [
    'class' => 'yii\gii\Module',
    'allowedIPs' => ['127.0.0.1', '::1', '192.168.*', '10.*'], // 按需放寬
    'password' => 'your_secure_password', // 設定密碼保護
    'generators' => [
        'model' => ['class' => 'yii\gii\generators\model\Generator'],
        'crud' => ['class' => 'yii\gii\generators\crud\Generator'],
    ],
];

存取網址：https://your-domain/index.php?r=gii (Basic) 或 https://frontend.local/gii (Advanced)。

使用 Gii 生成 Model (以 tbl_user, tbl_profile 為例)

1. 進入 **Model Generator**
2. Table Name: tbl_user (或 tbl_* 批次)
3. Model Class: User
4. Namespace: app\models / common\models
5. 勾選：
   • Generate Relations (根據 FK 自動生成 hasOne/hasMany)
   • Generate Labels from Comments (欄位註釋作為 attributeLabels)
   • Enable I18N (多語言專案)
6. Preview → Generate

產出 User.php 並包含關聯方法：

<?php
namespace app\models;
use yii\db\ActiveRecord;

class User extends ActiveRecord {
    public static function tableName() { return '{{%user}}'; }
    public function rules() { return [['username','email','required'], ['email','email']]; }
    public function attributeLabels() { return ['id'=>'ID','username'=>'使用者名稱','email'=>'Email']; }
    
    // 關聯：一個 User 擁有一個 Profile
    public function getProfile() {
        return $this->hasOne(Profile::class, ['user_id' => 'id']);
    }
    // 關聯：一個 User 擁有多個 Post
    public function getPosts() {
        return $this->hasMany(Post::class, ['author_id' => 'id']);
    }
}
?>

使用 Gii 生成 CRUD

1. 進入 **CRUD Generator**
2. Model Class: app\models\User
3. Search Model Class: app\models\UserSearch (自動生成搜尋/過濾邏輯)
4. Controller Class: app\controllers\UserController
5. View Path: @app/views/user
6. Widget: yii\grid\GridView (列表) / yii\widgets\DetailView (詳情) / yii\widgets\ActiveForm (表單)
7. Preview → Generate

現在可存取：

• 列表：/index.php?r=user/index (支援排序、分頁、搜尋欄)
• 新增：/index.php?r=user/create
• 檢視：/index.php?r=user/view&id=1
• 更新：/index.php?r=user/update&id=1
• 刪除：POST /index.php?r=user/delete&id=1

關聯查詢實戰：Eager Loading 避免 N+1 問題

<?php
// 控制器動作
public function actionIndex() {
    $searchModel = new UserSearch();
    $dataProvider = $searchModel->search($this->request->queryParams);
    
    // 關鍵：with() 預載入關聯資料，單次查詢取得所有關聯
    $dataProvider->query->with(['profile', 'posts' => function($q) {
        $q->orderBy(['created_at' => SORT_DESC])->limit(3); // 僅取最新 3 篇
    }]);
    
    return $this->render('index', [
        'searchModel' => $searchModel,
        'dataProvider' => $dataProvider,
    ]);
}

// 視圖 GridView 欄位顯示關聯資料
echo GridView::widget([
    'dataProvider' => $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        'id',
        'username',
        'email',
        [
            'attribute' => 'profile.real_name',
            'label' => '真實姓名',
            'value' => fn($model) => $model->profile?->real_name ?? '-',
        ],
        [
            'attribute' => 'post_count',
            'label' => '文章數',
            'value' => fn($model) => $model->getPosts()->count(),
        ],
        ['class' => 'yii\grid\ActionColumn'],
    ],
]);

Search Model 進階過濾與關聯搜尋

<?php
namespace app\models;
use yii\data\ActiveDataProvider;
use yii\db\ActiveQuery;

class UserSearch extends User {
    public $profile_real_name; // 關聯欄位搜尋用虛擬屬性
    
    public function rules() {
        return array_merge(parent::rules(), [
            [['profile_real_name'], 'string'],
        ]);
    }
    
    public function search($params) {
        $query = self::find()->with(['profile']); // 預載入關聯
        
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => ['pageSize' => 20],
            'sort' => ['defaultOrder' => ['id' => SORT_DESC]],
        ]);
        
        $this->load($params);
        if (!$this->validate()) return $dataProvider;
        
        // 基礎欄位過濾
        $query->andFilterWhere(['id' => $this->id, 'status' => $this->status]);
        $query->andFilterWhere(['like', 'username', $this->username])
              ->andFilterWhere(['like', 'email', $this->email]);
        
        // 關聯表欄位過濾：需 joinWith
        if ($this->profile_real_name) {
            $query->joinWith('profile')
                  ->andFilterWhere(['like', '{{%profile}}.real_name', $this->profile_real_name]);
        }
        
        return $dataProvider;
    }
}
?>

資料庫遷移 管理結構版本

# 建立遷移檔
php yii migrate/create create_user_table

# 編輯 migrations/m240101_000001_create_user_table.php
public function up() {
    $this->createTable('{{%user}}', [
        'id' => $this->primaryKey(),
        'username' => $this->string(50)->notNull()->unique(),
        'email' => $this->string(100)->notNull(),
        'password_hash' => $this->string(255)->notNull(),
        'status' => $this->smallInteger()->notNull()->defaultValue(10),
        'created_at' => $this->integer()->notNull(),
        'updated_at' => $this->integer()->notNull(),
    ]);
    $this->createIndex('idx_user_email', '{{%user}}', 'email');
}

public function down() {
    $this->dropTable('{{%user}}');
}

# 執行遷移
php yii migrate/up
# 回滾
php yii migrate/down

常見問題快速排查

結語

掌握「配置 DB → Gii 生成 Model/CRUD → 關聯查詢優化 → Search Model 進階過濾 → Migration 版本控制」這套標準流程，即可在 Yii 2 中高效、安全地處理複雜業務資料操作。建議搭配 yii2-debug 工具列監控 SQL 執行狀況，持續優化查詢效能。