Yii's query builder is a powerful tool that allows developers to build secure and readable database queries through PHP methods. 1. It generates SELECT, INSERT, UPDATE and DELETE statements through object-oriented method to reduce the risk of SQL injection. 2. Query construction adopts chain calling methods, such as select(), from(), where() and other methods to dynamically construct query conditions. 3. Supports complex query logic, including dynamic condition filtering, OR logical grouping and nested queries. 4. It not only supports data retrieval, but also supports data writing operations, such as insert(), update() and delete(). 5. It is recommended to use alias to improve code readability, avoid directly splicing user input, and use rawSql to debug generated SQL statements. 6. When it comes to complete models and associations, it is recommended to prioritize Active Record. This approach simplifies database interaction and makes the code safer, clearer and easier to maintain.

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 exactly 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 relationships, 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 readingability.

Basically that's it.

The above is the detailed content of How do I use query builder in Yii?. For more information, please follow other related articles on the PHP Chinese website!