Yii的查詢構(gòu)建器是一個(gè)強(qiáng)大的工具，它允許開發(fā)者通過PHP方法構(gòu)建安全且可讀性強(qiáng)的數(shù)據(jù)庫查詢。 1. 它通過對象導(dǎo)向的方式生成SELECT、INSERT、UPDATE和DELETE語句，減少SQL注入風(fēng)險(xiǎn)。 2. 查詢構(gòu)建採用鍊式調(diào)用方式，例如select()、from()、where()等方法動(dòng)態(tài)構(gòu)造查詢條件。 3. 支持複雜的查詢邏輯，包括動(dòng)態(tài)條件過濾、OR邏輯分組以及嵌套查詢。 4. 不僅支持?jǐn)?shù)據(jù)檢索，也支持?jǐn)?shù)據(jù)寫入操作，如insert()、update()和delete()。 5. 推薦使用別名提升代碼可讀性，避免直接拼接用戶輸入，並利用rawSql調(diào)試生成的SQL語句。 6. 在涉及完整模型和關(guān)聯(lián)關(guān)係時(shí)，建議優(yōu)先使用Active Record。這種方法簡化了數(shù)據(jù)庫交互，使代碼更加安全、清晰且易於維護(hù)。

When you're working with Yii and need to build database queries without writing raw SQL, the Query Builder is a powerful tool. It gives you a clean, object-oriented way to construct SELECT, INSERT, UPDATE, and DELETE statements dynamically — especially useful when conditions or user input affect what data you're fetching or modifying.

Understanding the Basics of Yii Query Builder

The Query Builder in Yii (especially Yii2) helps you create SQL queries using PHP methods instead of writing SQL strings manually. This makes your code safer (reducing SQL injection risks) and more readable.

Here's how a basic SELECT query looks:

 use yii\db\Query;

$query = (new Query())
    ->select(['id', 'name'])
    ->from('user')
    ->where(['status' => 1]);

This builds a query like SELECT id, name FROM user WHERE status = 1 . You can then fetch results using all() or one() :

 $users = $query->all();

You don't have to remember exact SQL syntax every time — just chain methods like where() , orderBy() , limit() , etc.

Building Complex Queries Step by Step

Sometimes you need dynamic conditions based on user input or other logic. That's where chaining really helps.

For example, suppose you want to filter users by name or email depending on whether those values are provided:

 $query = (new Query())->from('user');

if (!empty($name)) {
    $query->andWhere(['like', 'name', $name]);
}

if (!empty($email)) {
    $query->andWhere(['like', 'email', $email]);
}

You can also group conditions using arrays or nested queries. Here's an example with OR:

 $query->where([
    'or',
    ['like', 'name', 'John'],
    ['like', 'email', 'john']
]);

This flexibility lets you build complex filtering systems for things like search forms or admin interfaces.

Inserting, Updating, and Deleting Records

Query Builder isn't only for reading data — it also supports writing operations.

To insert a new record:

 Yii::$app->db->createCommand()
    ->insert('user', [
        'name' => 'Jane Doe',
        'email' => 'jane@example.com',
        'status' => 1,
    ])
    ->execute();

Updating records:

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

And deleting:

 Yii::$app->db->createCommand()
    ->delete('user', 'id = 100')
    ->execute();

These commands generate parameterized queries under the hood, which helps prevent SQL injection.

Tips for Working with Query Builder Effectively

• Use Aliases – Especially in JOINs or long queries. It keeps your code cleaner.
• Always Escape User Input – Even though Query Builder helps protect you, avoid directly concatenating user input into query parts.
• Debug Your Queries – Use $query->createCommand()->rawSql to see the actual SQL generated. Very handy during testing.
• Use Active Record When Appropriate – If you're dealing with full models and relations, sometimes ActiveRecord is better than Query Builder.

Using Query Builder in Yii simplifies interacting with databases while keeping your codebase safe and maintainable. Whether you're selecting filtered data or updating multiple records, building queries this way avoids messy SQL strings and improves readability.

基本上就這些。

以上是如何在yii中使用查詢構(gòu)建器？的詳細(xì)內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！