在 Yii 中編寫自定義 SQL 查詢可通過 Yii::$app->db 實現(xiàn)，使用步驟如下：1. 使用 createCommand() 創(chuàng)建查詢命令；2. 通過 bindValue() 或 bindParam() 綁定參數(shù)防止 SQL 注入；3. 調(diào)用 queryAll()、queryOne() 等方法執(zhí)行查詢；4. 對于插入、更新操作，可鏈式調(diào)用 insert()、update() 方法；5. 復雜多表查詢建議直接寫 SQL 并綁定參數(shù)；6. 若結(jié)果需轉(zhuǎn)為模型，可手動實例化并設(shè)置屬性；7. 優(yōu)先使用 Query Builder 構(gòu)建安全查詢，復雜邏輯則使用原生 SQL。

In Yii, writing custom SQL queries is straightforward and gives you more control when dealing with complex database operations that aren't easily handled by Active Record. The key is knowing how to use Yii's database components properly.

Using Yii::$app->db for Custom Queries

The main entry point for executing raw SQL in Yii is through the yii\db\Connection object, which you can access using Yii::$app->db. This allows you to create commands and execute queries safely.

To run a simple SELECT query:

$command = Yii::$app->db->createCommand('SELECT * FROM user WHERE status=:status');
$command->bindValue(':status', 1);
$results = $command->queryAll();

Here’s what’s happening:

• You create a command using createCommand()
• Use bindValue() or bindParam() to prevent SQL injection
• Call queryAll() to fetch multiple rows (or queryOne() for a single row)

You can also perform INSERT, UPDATE, DELETE like this:

Yii::$app->db->createCommand()->insert('user', [
    'name' => 'John',
    'email' => 'john@example.com',
])->execute();

Or update:

Yii::$app->db->createCommand()->update('user', ['status' => 0], 'id = 100')->execute();

These methods are clean and readable, especially for basic data manipulation.

Handling Complex Queries with Multiple Tables

When you're joining tables or pulling data from multiple sources, raw SQL becomes even more useful. For example:

$sql = '
    SELECT u.name, p.title 
    FROM user u
    JOIN post p ON u.id = p.user_id
    WHERE u.status = :status
';

$posts = Yii::$app->db->createCommand($sql)
    ->bindValue(':status', 1)
    ->queryAll();

This kind of query would be messy with Active Record, but with raw SQL it stays clear and efficient. Just remember to:

• Always use parameter binding (:status, :id, etc.)
• Avoid concatenating user input directly into SQL strings

Also, if your query returns model-like data, consider hydrating models manually:

foreach ($results as $row) {
    $model = new User();
    $model->setAttributes($row, false);
    // do something with $model
}

This keeps your code structured even when not using AR directly.

When to Use Query Builder vs Raw SQL

Yii also provides a query builder (yii\db\Query) that helps generate SQL safely without writing everything manually. For example:

$rows = (new \yii\db\Query())
    ->select(['id', 'name'])
    ->from('user')
    ->where(['status' => 1])
    ->all();

This builds the SQL under the hood, still uses parameter binding, and is safer than raw SQL. But sometimes, especially with advanced joins or subqueries, raw SQL ends up being simpler and clearer.

So here’s a quick guide:

• ? Use Query Builder for dynamic conditions and simple joins
• ? Don’t force complicated logic into Query Builder if it gets too messy
• ? Go with raw SQL when performance matters or query complexity is high

If you're unsure, start with Query Builder—it often leads to cleaner, safer code.

That’s the basics of working with custom SQL in Yii. It’s flexible but requires attention to security and readability. Stick to parameterized queries, organize your SQL logically, and you’ll have solid, maintainable code.基本上就這些。

以上是如何在yii中編寫自定義SQL查詢？的詳細內(nèi)容。更多信息請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！