<?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的區(qū)別是createCommand是直接寫(xiě)一個(gè)SQL來(lái)執(zhí)行。
    //Query是根據(jù)參數(shù)和數(shù)據(jù)庫(kù)類(lèi)型生成不同的SQL，提升數(shù)據(jù)庫(kù)可遷移性。
 
    //通過(guò)all查詢(xún)多條記錄
    //我這里用tb_user表來(lái)進(jìn)行演示
    $data1 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->all();
 
    //指定where條件查詢(xún)
    $data2 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where('id=:id', [':id' => '2'])
      ->all();
 
    //通過(guò)one查詢(xún)單條記錄
    $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 存在';
    }
 
    //定義字段別名
    //注意真實(shí)的字段名寫(xiě)后面，別名寫(xiě)前面
    $data4 = (new Query())->select(['ids' => 'id', 'names' => 'name'])
      ->from('{{%user}}')
      ->where('1=1')
      ->all();
 
    //通過(guò)orderby排序，和limit限制條數(shù)
    $data5 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where('1=1')
      ->orderBy('id desc')
      ->limit(3)
      ->all();
 
    //多個(gè)and條件
    //參數(shù)是數(shù)組，一個(gè)key對(duì)應(yīng)一個(gè)value，默認(rèn)以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統(tǒng)計(jì)
    $count = (new Query())->from('{{%user}}')->count();
    echo '總記錄數(shù): ', $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查詢(xún)
    $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分組，然后統(tǒng)計(jì)人數(shù)大于3的
    $data12 = (new Query())->select(['sex', 'cnt' => 'count(*)'])
      ->from('{{%user}}')
      ->groupBy('sex')
      ->having('cnt > 3')
      ->all();
 
    //or邏輯條件
    //查找姓名為aaa或bbb的用戶(hù)
    //之前where數(shù)組是以key=>value方式傳遞的，如果要表達(dá)復(fù)雜邏輯關(guān)系，
    //數(shù)組第一個(gè)元素必須聲明是什么邏輯，and還是or
    //第二個(gè)元素表示邏輯左邊
    //第三個(gè)元素表示邏輯右邊
    $data13 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['or', ['name' => 'aaa'], ['name' => 'bbb']])
      ->all();
 
    //復(fù)雜的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();
 
    //有些時(shí)候我們需要根據(jù)用戶(hù)傳遞過(guò)來(lái)的參數(shù)追加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();
 
    //表別名和連接查詢(xún)
    //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();
  }
}