Writing a custom SQL query in Yii can be implemented through Yii::$app->db. The steps are as follows: 1. Create a query command using createCommand(); 2. Binding parameters through bindValue() or bindParam() to prevent SQL injection; 3. Call queryAll(), queryOne() and other methods to execute queries; 4. For insert and update operations, you can chain call insert() and update() methods; 5. It is recommended to write SQL directly and bind parameters for complex multi-table queries; 6. If the result needs to be converted into a model, you can instantiate and set properties manually; 7. Use Query Builder to build secure queries first, and use native SQL for complex logic.

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.

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