?php
namespace app\controllers;
 
use YII;
use yii\db\Query;
use yii\web\Controller;
 
class TestController extends Controller
{
  public function actionTest()
  {
    //YII2的Query的使用
    //Query與createCommand的區別是createCommand是直接寫一個SQL來執行。
    //Query是根據參數和數據庫類型生成不同的SQL，提升數據庫可遷移性。
 
    //通過all查詢多條記錄
    //我這里用tb_user表來進行演示
    $data1 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->all();
 
    //指定where條件查詢
    $data2 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where('id=:id', [':id' => '2'])
      ->all();
 
    //通過one查詢單條記錄
    $data3 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where('id=3')
      ->one();
 
    //判斷記錄是否存在
    $exists = (new Query())->from('{{%user}}')
      ->where('name="aaa"')
      ->exists();
 
    if ($exists) {
      echo 'name=aaa 存在';
    }
 
    //定義字段別名
    //注意真實的字段名寫后面，別名寫前面
    $data4 = (new Query())->select(['ids' => 'id', 'names' => 'name'])
      ->from('{{%user}}')
      ->where('1=1')
      ->all();
 
    //通過orderby排序，和limit限制條數
    $data5 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where('1=1')
      ->orderBy('id desc')
      ->limit(3)
      ->all();
 
    //多個and條件
    //參數是數組，一個key對應一個value，默認以and拼接
    $data6 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['id' => 3, 'name' => 'aaa'])
      ->one();
 
    //in條件
    $data7 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['id' => [4, 5, 6]])
      ->all();
 
    //或者如下方式
    $data7_2 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['in', 'id', [4, 5, 6]])
      ->all();
 
    //count統計
    $count = (new Query())->from('{{%user}}')->count();
    echo '總記錄數: ', $count;
 
    //大于，大于等于，小于，小于等于where條件
    $data8 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['>=', 'id', 5])
      ->all();
 
    $data9 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['=', 'id', 3])
      ->all();
 
    //like查詢
    $data10 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['like', 'name', 'dd'])
      ->all();
 
    //between篩選和group by分組
    //查找出age在18到24之間的，并按sex分組
    $data11 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['between', 'age', 18, 24])
      ->groupBy('sex')
      ->all();
 
    //having篩選
    //按sex分組，然后統計人數大于3的
    $data12 = (new Query())->select(['sex', 'cnt' => 'count(*)'])
      ->from('{{%user}}')
      ->groupBy('sex')
      ->having('cnt > 3')
      ->all();
 
    //or邏輯條件
    //查找姓名為aaa或bbb的用戶
    //之前where數組是以key=>value方式傳遞的，如果要表達復雜邏輯關系，
    //數組第一個元素必須聲明是什么邏輯，and還是or
    //第二個元素表示邏輯左邊
    //第三個元素表示邏輯右邊
    $data13 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['or', ['name' => 'aaa'], ['name' => 'bbb']])
      ->all();
 
    //復雜的where條件
    //我這里只是作為演示
    //SELECT `id`, `name` FROM `tb_user` WHERE ((`name`='aaa') OR (`name`='bbb')) OR ((`name`='ccc') OR (`name`='ddd'))
    $data14 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where([
        'or',
        [
          'or',
          ['name' => 'aaa'],
          ['name' => 'bbb'],
        ],
        [
          'or',
          ['name' => 'ccc'],
          ['name' => 'ddd'],
        ],
      ])
      ->all();
 
    //and和or嵌套where條件
    //SELECT `id`, `name` FROM `tb_user` WHERE (`sex`=1) AND ((`name` LIKE '%aa%') OR (`name` LIKE '%bb%'))
    $data15 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where([
        'and',
        ['sex' => 1],
        [
          'or',
          ['like', 'name', 'aa'],
          ['like', 'name', 'bb'],
        ],
      ])
      ->all();
 
    //有些時候我們需要根據用戶傳遞過來的參數追加where條件
    //追加and條件
    $query = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where('sex=1');
    //追加age大于18的條件
    $query->andWhere(['>', 'age', 18]);
    echo $query->createCommand()->getRawSql();
 
    //追加or條件
    $query2 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['like', 'name', 'aa']);
    //追加name相似bb的條件
    $query2->orWhere(['like', 'name', 'bb']);
    echo $query2->createCommand()->getRawSql();
 
    //表別名和連接查詢
    //SELECT `u`.`id`, `u`.`name`, `aa`.`item_name` FROM `tb_user` `u` LEFT JOIN `tb_auth_assignment` `aa` ON aa.user_id = u.id
    $data16 = (new Query())->select(['u.id', 'u.name', 'aa.item_name'])
      ->from(['u' => '{{%user}}'])
      ->leftJoin(['aa' => '{{%auth_assignment}}'], 'aa.user_id = u.id')
      ->all();
  }
}