Yii2 Query Builder & DAO (Direct SQL Queries)

1. Yii2 Query Builder

Yii2 Query Builder provides an elegant and safe way to build and execute database queries using a fluent interface. It helps avoid raw SQL queries while still allowing flexibility.

1.1. Basic Query Builder Methods

To use Query Builder, call Yii::$app->db->createCommand() with appropriate query methods.

1.1.1. SELECT Query

Fetching all rows from the master_state table:

$states = (new \yii\db\Query())
    ->select(['state_code', 'state_name'])
    ->from('master_state')
    ->where(['status' => 1])
    ->all();

Fetching a single row:

$state = (new \yii\db\Query())
    ->select(['state_code', 'state_name'])
    ->from('master_state')
    ->where(['state_code' => 101])
    ->one();

Fetching a single column:

$stateNames = (new \yii\db\Query())
    ->select('state_name')
    ->from('master_state')
    ->column();

1.1.2. INSERT Query

Inserting a new record into master_state:

Yii::$app->db->createCommand()
    ->insert('master_state', [
        'state_code' => 105,
        'state_name' => 'New State',
        'status' => 1
    ])
    ->execute();

1.1.3. UPDATE Query

Updating the name of a state:

Yii::$app->db->createCommand()
    ->update('master_state', ['state_name' => 'Updated State'], ['state_code' => 105])
    ->execute();

1.1.4. DELETE Query

Deleting a record from master_state:

Yii::$app->db->createCommand()
    ->delete('master_state', ['state_code' => 105])
    ->execute();

1.1.5. ORDER, LIMIT & JOINS

Sorting results, limiting, and using joins:

$states = (new \yii\db\Query())
    ->select(['s.state_code', 's.state_name', 'z.zone_name'])
    ->from('master_state s')
    ->leftJoin('master_zone z', 's.zone_code = z.zone_code')
    ->where(['s.status' => 1])
    ->orderBy(['s.state_name' => SORT_ASC])
    ->limit(10)
    ->all();

2. DAO (Direct SQL Queries in Yii2)

DAO (Data Access Object) allows executing raw SQL queries directly. It is useful when dealing with complex queries.

2.1. Fetching Data Using Direct SQL

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

Fetching a single row:

$row = Yii::$app->db->createCommand('SELECT * FROM master_state WHERE state_code = :code')
    ->bindValue(':code', 101)
    ->queryOne();

Fetching a single column:

$stateNames = Yii::$app->db->createCommand('SELECT state_name FROM master_state')
    ->queryColumn();

Fetching a scalar value:

$count = Yii::$app->db->createCommand('SELECT COUNT(*) FROM master_state')
    ->queryScalar();

2.2. Executing INSERT, UPDATE, DELETE Queries

2.2.1. INSERT Query

Yii::$app->db->createCommand('INSERT INTO master_state (state_code, state_name, status) VALUES (:code, :name, :status)')
    ->bindValues([':code' => 106, ':name' => 'Test State', ':status' => 1])
    ->execute();

2.2.2. UPDATE Query

Yii::$app->db->createCommand('UPDATE master_state SET state_name = :name WHERE state_code = :code')
    ->bindValues([':name' => 'Modified State', ':code' => 106])
    ->execute();

2.2.3. DELETE Query

Yii::$app->db->createCommand('DELETE FROM master_state WHERE state_code = :code')
    ->bindValue(':code', 106)
    ->execute();

3. Recommended Approach: When to Use What?

• Use Query Builder when working with Yii2 models to ensure abstraction, security, and ease of use.
• Use DAO (Raw SQL) for highly complex queries or when performance optimizations are needed.

4. Real-World Example (MasterState CRUD Using Query Builder & DAO)

Fetching All Active States (Query Builder)

$states = (new \yii\db\Query())
    ->select(['state_code', 'state_name'])
    ->from('master_state')
    ->where(['status' => 1])
    ->orderBy(['state_name' => SORT_ASC])
    ->all();

Adding a New State (DAO - Direct SQL)

Yii::$app->db->createCommand('INSERT INTO master_state (state_code, state_name, status) VALUES (:code, :name, :status)')
    ->bindValues([':code' => 110, ':name' => 'Real State', ':status' => 1])
    ->execute();

Updating a State (Query Builder)

Yii::$app->db->createCommand()
    ->update('master_state', ['state_name' => 'Updated Real State'], ['state_code' => 110])
    ->execute();

Deleting a State (DAO - Direct SQL)

Yii::$app->db->createCommand('DELETE FROM master_state WHERE state_code = :code')
    ->bindValue(':code', 110)
    ->execute();