= Yii 2 Database SQL Queries =

[[https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder|Yii2 Query Builder]]

== Debugging ==

<code php>
$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);

</code>        

== Query Using Models ==

<code php>
$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();
</code>

== Methods for Querying ==


=== findBySql ===

Sample 1:
<code php>
$sql = 'SELECT * FROM tbl_user';
$model = User::findBySql($sql)->all();  
</code>

Sample 2:
<code php>
$sql = 'SELECT * FROM tbl_user';
$model = User::findBySql($sql)->one();  
</code>

=== queryAll ===

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

=== queryOne ===

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

=== queryColumn ===

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

=== queryScalar ===

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

=== Query With Prepared Statements() ===

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

=== Query Class ===

Sample 1:
<code php>
$query = new yii\db\Query;
$query->select('userid, username')
    ->from('tbl_user')
    ->limit(10);
$command = $query->createCommand();
$data = $command->queryAll();   
</code>

Sample 2:
<code php>
$columns = [];
$columns[] = 'userid';
$columns[] = 'username';
 
$query = new yii\db\Query;
$query->select($columns)
    ->from('tbl_user')
    ->limit(10);
$command = $query->createCommand();
$data = $command->queryAll();  
</code>

== SQL Update ==

=== Converting Raw SQL to Query ===

This complex UPDATE query cannot be code in Yii2 using the standar QueryBuilder.
<code sql>
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'
</code>

Use the ''createCommand()'' instead, as follows:
<code php>
$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();
</code>

=== 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 ==
  * [[http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html|Yii 2 Guide: DB Query Builder]]
  * [[http://www.yiiframework.com/doc-2.0/guide-db-active-record.html|Yii 2 Guide: DB ActiveRecord]]