Yii 2 Database SQL Queries

Yii2 Query Builder

Debugging
$query = (new \yii\db\Query())
    ->select(['e1.*])
    ->from('entry e1')
    ->where(['e1.created_at' => null])
    ->andWhere(['and', 'e1.brand=\'Acme\''])
    ->orderBy(['e1.product_code' => SORT_ASC, 'e1.serial_number' => SORT_ASC]);
 
$command = $query->createCommand();
$rows    = $command->queryAll();                     // get array with data
$SQL     = $command->sql;
Yii::$app->getSession()->setFlash('debug', $SQL);    // display query command
 
// show the SQL statement and parameters
echo "SQL: " . $command->sql . 
  "\n\nParams: " . print_r($command->params, true) . 
  "\n\nResult row count: " . count($rows);
Query Using Models
$customer_id = 12345;
 
$customer = app\models\Customer::find()->where(['id'=>$customer_id])->one();
 
$prices = app\models\Price::find()
            ->where(['customer_id' => $customer_id])
            ->orderBy(['type' => SORT_ASC, 'description' => SORT_ASC])
            ->all();
Methods for Querying

findBySql

Sample 1: 

$sql = 'SELECT * FROM tbl_user';
$model = User::findBySql($sql)->all();

Sample 2: 

$sql = 'SELECT * FROM tbl_user';
$model = User::findBySql($sql)->one();

queryAll

$connection = \Yii::$app->db;
$model = $connection->createCommand('SELECT * FROM tbl_user');
$users = $model->queryAll();

queryOne

$connection = \Yii::$app->db;
$model = $connection->createCommand("SELECT * FROM tbl_user where userid=$id");
$users = $model->queryOne();

queryColumn

$connection = \Yii::$app->db;
$model = $connection->createCommand('SELECT username FROM tbl_user');
$users = $model->queryColumn();

queryScalar

$connection = \Yii::$app->db;
$model = $connection->createCommand('SELECT COUNT(*) FROM tbl_user');
$users_count = $model->queryScalar();

Query With Prepared Statements()

$connection = \Yii::$app->db;
$user = $connection->createCommand('SELECT * FROM tbl_user WHERE userid=:userid');
$user->bindValue(':userid', $_GET['userid']);
$model = $user->query();

Query Class

Sample 1: 

$query = new yii\db\Query;
$query->select('userid, username')
    ->from('tbl_user')
    ->limit(10);
$command = $query->createCommand();
$data = $command->queryAll();

Sample 2: 

$columns = [];
$columns[] = 'userid';
$columns[] = 'username';
 
$query = new yii\db\Query;
$query->select($columns)
    ->from('tbl_user')
    ->limit(10);
$command = $query->createCommand();
$data = $command->queryAll();
SQL Update

Converting Raw SQL to Query

This complex UPDATE query cannot be code in Yii2 using the standar QueryBuilder. 

UPDATE `pay_audit`
JOIN `invoice_items`
  ON `invoice_items`.`mdn` = `pay_audit`.`account_id` 
  AND `invoice_items`.`unitprice` = `pay_audit`.`payment`
  AND `invoice_items`.`producttype_name` LIKE 'PAYMENT'
  AND DATE_FORMAT(`invoice_items`.`created`, '%Y-%m-%d') = '2015-02-21'
SET `pay_audit`.`invoice_item_id` = `invoice_items`.`id`
WHERE `pay_audit`.`report_date` = '2015-02-21'

Use the createCommand() instead, as follows: 

$today = date('Y-m-d');
$sql = "";
$sql .= "UPDATE `pay_audit` ";
$sql .= "JOIN `invoice_items` ";
$sql .= "  ON `invoice_items`.`mdn` = `pay_audit`.`account_id` ";
$sql .= "  AND `invoice_items`.`unitprice` = `qpay_audit`.`payment` ";
$sql .= "  AND `invoice_items`.`producttype_name` LIKE 'PAYMENT' ";
$sql .= "  AND DATE_FORMAT(`invoice_items`.`created`, '%Y-%m-%d') = '$today' ";
$sql .= "SET `pay_audit`.`invoice_item_id` = `invoice_items`.`id` ";
$sql .= "WHERE `pay_audit`.`report_date` = '$today'";
 
$command = \Yii::$app->db->createCommand($sql);
$command->execute();

Other Examples

Raw SQL: 

\Yii::$app->db->createCommand('update user set status = 1 where age > 30')->execute();

Raw SQL with placeholders (to prevent SQL injection) 

\Yii::$app->db->createCommand('update user set status = :status where age > 30')->bindValue(':status','1')->execute();

update() method 

// update user set status = 1 where age > 30
\Yii::$app->db->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();

References: https://stackoverflow.com/questions/28651195/yii2-querybuilder-update-with-join

References