Yii2 数据操作之Query Builder

Query Builder

1 $rows = (new yiidbQuery())  
2     ->select(['dyn_id', 'dyn_name'])  
3     ->from('zs_dynasty')  
4     ->where(['between','dyn_id', 1,30])  
5     ->limit(10)  
6     ->all();  
7 print_r($rows);

1 use yiidbQuery;  
2 $query = (new Query())  
3     ->from('user')  
4     ->orderBy('id');

SELECT

1 $query->select('*')->  
2     select('dyn_id as id, dynasty.dyn_name')->  
3     $query->select(['dyn_id as id', "CONCAT(dyn_name,'a')"])->  
4     $query->select('user_id')->distinct()->

FORM

 1 $query->from('user');  
 2 $query->from(['public.user u', 'public.post p']);  
 3 $query->from('public.user u, public.post p');  
 4 $query->from(['u' => 'public.user', 'p' => 'public.post']);  
 5 
 6 
 7 $subQuery = (new Query())->select('id')->from('user')-
 8     >where('status=1');  
 9 // SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u   
10 $query->from(['u' => $subQuery]);

WHERE

 1 where('status=1')->  
 2 where('status=:status', [':status' => $status])->  
 3 where([  
 4     'status' => 10,  
 5     'type' => null,  
 6     'id' => [4, 8, 15],  
 7 ])->  
 8 -------  
 9 $userQuery = (new Query())->select('id')->from('user');  
10 // ...WHERE `id` IN (SELECT `id` FROM `user`)  
11 $query->...->where(['id' => $userQuery])->...  
12 --------  
13 ['and', 'id=1', 'id=2'] //id=1 AND id=2  
14 ['and', 'type=1', ['or', 'id=1', 'id=2']] //type=1 AND (id=1 OR id=2)  
15 ['between', 'id', 1, 10] //id BETWEEN 1 AND 10  
16 ['not between', 'id', 1, 10] //not id BETWEEN 1 AND 10  
17 ['in', 'id', [1, 2, 3]] //id IN (1, 2, 3)  
18 ['not in', 'id', [1, 2, 3]] //not id IN (1, 2, 3)  
19 ['like', 'name', 'tester'] //name LIKE '%tester%'  
20 ['like', 'name', ['test', 'sample']] //name LIKE '%test%' AND name LIKE '%sample%'  
21 ['not like', 'name', ['or', 'test', 'sample']] //not name LIKE '%test%' OR not name LIKE '%sample%'  
22 ['exists','id', $userQuery] //EXISTS (sub-query) | not exists  
23 ['>', 'age', 10] //age>10

ADD WHERE

1 $status = 10;  
2 $search = 'yii';  
3 $query->where(['status' => $status]);  
4 if (!empty($search)) {  
5     $query->andWhere(['like', 'title', $search]);  
6 }  
7 //WHERE (`status` = 10) AND (`title` LIKE '%yii%')  
8 //andWhere() or orWhere()

FILTER WHERE

$query->filterWhere([  
    'username' => $username,  
    'email' => $email,  
]);  
//如果email为空,则 WHERE username=:username

ORDER BY

1 $query->orderBy([  
2     'id' => SORT_ASC,  
3     'name' => SORT_DESC,  
4 ]);  
5 //orderBy , addOrderBy

GROUP BY

1 $query->groupBy('id, status');  
2 $query->addGroupBy(['created_at', 'updated_at']);

HAVING

1 $query->having(['status' => $status]);  
2 //having,andHaving,orHaving

LIMIT OR OFFSET

1 $query->limit(10);  
2 $query->offset(10);

JOIN

innerJoin()
leftJoin()
rightJoin()

1 $query->select(['user.name AS author', 'post.title as title'])  
2     ->from('user')  
3     ->leftJoin('post', 'post.user_id = user.id');  
4 $query->join('FULL OUTER JOIN', 'post', 'post.user_id = user.id');  
5 $query->leftJoin(['u' => $subQuery], 'u.id=author_id');

UNION

1 $query = new Query();  
2 $query->select("id, category_id as type, name")->from('post')->limit(10);  
3 $anotherQuery = new Query();  
4 $anotherQuery->select('id, type, name')->from('user')->limit(10);  
5 $query->union($anotherQuery);

QUERY METHODS

all() //所有行列
one() //第一行
column() //第一列
scalar() //第一行第一列
exists() //是否有结果存在
count() //记录数量
sum($q), average($q), max($q), min($q) //$q 为字段或表达式

 1 $count = (new yiidbQuery())  
 2     ->from('user')  
 3     ->where(['last_name' => 'Smith'])  
 4     ->count();  
 5 //SELECT COUNT(*) FROM `user` WHERE `last_name`=:last_name  
 6   
 7   
 8 $command = (new yiidbQuery())  
 9     ->select(['id', 'email'])  
10     ->from('user')  
11     ->where(['last_name' => 'Smith'])  
12     ->limit(10)  
13     ->createCommand();  
14       
15 // show the SQL statement  
16 echo $command->sql;  
17 // show the parameters to be bound  
18 print_r($command->params);  
19   
20   
21 // returns all rows of the query result  
22 $rows = $command->queryAll();

QUERY RESULTS

1 use yiidbQuery;  
2 $query = (new Query())  
3     ->from('user')  
4     ->indexBy('username');  
5 foreach ($query->batch() as $users) {  
6     // $users is indexed by the "username" column  
7 }  
8 foreach ($query->each() as $username => $user) {  
9 }

INDEXING

 1 use yiidbQuery;  
 2 $query = (new Query())  
 3     ->from('user')  
 4     ->orderBy('id');  
 5 foreach ($query->batch() as $users) {  
 6     // batch( $batchSize = 100, $db = null )  
 7     // 一个批次取100行  
 8 }  
 9 foreach ($query->each() as $user) {  
10     // 一行一行取  
11 }