YII2 Data Operations Query Builder detailed

Query Builder

$rows = (new \yii\db\query ())

->select ([' dyn_id ', ' dyn_name '])

->from (' Zs_dynasty ')

->where ([' Between ', ' dyn_id ', 1,30])

->limit (10)

->all ();

Print_r ($rows);

View a code slice from my Code chip

Use Yii\db\query;

$query = (new query ())

->from (' user ')

->orderby (' id ');

SELECT

View a code slice from my Code chip

$query->select (' * ')->

Select (' dyn_id as ID, dynasty.dyn_name ')->

$query->select ([' dyn_id as ID ', CONCAT (Dyn_name, ' a ')])->

$query->select (' user_id ')->distinct ()->

FORM

View a code slice from my Code chip

$query->from (' user ');

$query->from ([' Public.user u ', ' Public.post P ']);

$query->from (' Public.user u, public.post P ');

$query->from ([' U ' => ' public.user ', ' P ' => ' public.post ')];

----------

$subQuery = (new Query ())->select (' id ')->from (' user ')->where (' Status=1 ');

SELECT * FROM (SELECT ' id ' from ' WHERE Status=1 ') u

$query->from ([' U ' => $subQuery]);

WHERE

View a code slice from my Code chip

Where (' Status=1 ')->

Where (' Status=:status ', [': Status ' => $status])->

where ([

' Status ' => 10,

' type ' => null,

' ID ' => [4, 8, 15],

])->

-------

$userQuery = (new Query ())->select (' id ')->from (' user ');

// ... WHERE ' id ' in (SELECT ' id ' from ' user ')

$query->...->where ([' ID ' => $userQuery]) .....

--------

[' and ', ' id=1 ', ' id=2 ']//id=1 and id=2

[' and ', ' type=1 ', [' or ', ' id=1 ', ' id=2 ']]//type=1 and (id=1 or id=2)

[' Between ', ' ID ', 1]//id between 1 and 10

[' Not between ', ' id ', 1, ten]//not ID between 1 and 10

[' In ', ' IDs ', [1, 2, 3]]//id in (1, 2, 3)

[' Not in ', ' IDs ', [1, 2, 3]]//not ID in (1, 2, 3)

[' Like ', ' name ', ' tester ']//name like '%tester% '

[' Like ', ' name ', [' Test ', ' sample ']]]//name like '%test% ' and name like '%sample% '

[' Not like ', ' name ', [' or ', ' test ', ' sample ']]//not name like '%test% ' or ' is ' like '%sample% '

[' exists ', ' id ', $userQuery]//exists (sub-query) | NOT EXISTS

[' > ', ' Age ', ten]//age>10

ADD WHERE

View a code slice from my Code chip

$status = 10;

$search = ' Yii ';

$query->where ([' Status ' => $status]);

if (!empty ($search)) {

$query->andwhere ([' Like ', ' title ', $search]);

}

WHERE (' status ' = Ten) and (' title ' Like '%yii% ')

Andwhere () or Orwhere ()

FILTER WHERE

View a code slice from my Code chip

$query->filterwhere ([

' username ' => $username,

' Email ' => $email,

]);

If the email is empty, the WHERE username=:username

ORDER BY

View a code slice from my Code chip

$query->orderby ([

' id ' => SORT_ASC,

' Name ' => Sort_desc,

]);

By, Addorderby

GROUP by

View a code slice from my Code chip

$query->groupby (' ID, status ');

$query->addgroupby ([' Created_at ', ' updated_at ']);

Having

View a code slice from my Code chip

$query->having ([' Status ' => $status]);

Having,andhaving,orhaving

LIMIT OR OFFSET

View a code slice from my Code chip

$query->limit (10);

$query->offset (10);

JOIN

Innerjoin ()

Leftjoin ()

Rightjoin ()

View a code slice from my Code chip

$query->select ([' User.Name as author ', ' Post.title as title '])

->from (' user ')

->leftjoin (' Post ', ' post.user_id = User.ID ');

$query->join (' Full OUTER join ', ' post ', ' post.user_id = User.ID ');

$query->leftjoin ([' U ' => $subQuery], ' u.id=author_id ');

UNION

View a code slice from my Code chip

$query = new query ();

$query->select ("id, category_id as type, name")->from (' Post ')->limit (10);

$anotherQuery = new Query ();

$anotherQuery->select (' ID, type, name ')->from (' user ')->limit (10);

$query->union ($anotherQuery);

QUERY METHODS

All ()//all Ranks

One ()//First line

Column ()//First columns

Scalar ()//First Row first column

exists ()//whether there is a result

COUNT ()//number of records

SUM ($q), average ($q), Max ($q), min ($q)//$q as a field or an expression

View a code slice from my Code chip

$count = (new \yii\db\query ())

->from (' user ')

->where ([' last_name ' => ' Smith '])

->count ();

SELECT COUNT (*) from ' user ' WHERE ' last_name ' =:last_name

$command = (new \yii\db\query ())

->select ([' id ', ' email '])

->from (' user ')

->where ([' last_name ' => ' Smith '])

->limit (10)

->createcommand ();

Show the SQL statement

Echo $command->sql;

Show the parameters to be bound

Print_r ($command->params);

Returns all rows to the query result

$rows = $command->queryall ();

QUERY RESULTS

View a code slice from my Code chip

Use Yii\db\query;

$query = (new query ())

->from (' user ')

->indexby (' username ');

foreach ($query->batch () as $users) {

$users is indexed by the "username" column

}

foreach ($query->each () as $username => $user) {

}

Indexing

View a code slice from my Code chip

Use Yii\db\query;

$query = (new query ())

->from (' user ')

->orderby (' id ');

foreach ($query->batch () as $users) {

Batch ($batchSize = +, $db = null)

One batch to 100 lines

}

foreach ($query->each () as $user) {

Row by line

}