query builder allows you to create sql queries in a programmatic way. query builder helps you write more readable sql-related code.

to use query builder, you should follow these steps −

• build an yii\db\query object.
• execute a query method.

to build an yii\db\query object, you should call different query builder functions to define different parts of an sql query.

step 1 − to show a typical usage of the query builder, modify the actiontestdb method this way.

public function actiontestdb() {
   //generates "select id, name, email from user where name = 'user10';"
   $user = (new \yii\db\query())
      ->select(['id', 'name', 'email'])
      ->from('user')
      ->where(['name' => 'user10'])
      ->one();
   var_dump($user);
}

step 2 − go to http://localhost:8080/index.php?r=site/test-db, you will see the following output.

where() function

the where() function defines the where fragment of a query. to specify a where condition, you can use three formats.

• string format − 'name = user10'

• hash format − ['name' => 'user10', 'email => user10@gmail.com']

• operator format − ['like', 'name', 'user']

example of string format

public function actiontestdb() {
   $user = (new \yii\db\query())
      ->select(['id', 'name', 'email'])
      ->from('user')
      ->where('name = :name', [':name' => 'user11'])
      ->one();
   var_dump($user);
}

following will be the output.

example of hash format

public function actiontestdb() {
   $user = (new \yii\db\query())
      ->select(['id', 'name', 'email'])
      ->from('user')
      ->where([
         'name' => 'user5',
         'email' => 'user5@gmail.com'
      ])
      ->one();
   var_dump($user);
}

following will be the output.

operator format allows you to define arbitrary conditions in the following format −

[operator, operand1, operand2]

the operator can be −

• and − ['and', 'id = 1', 'id = 2'] will generate id = 1 and id = 2 or: similar to the and operator

• between − ['between', 'id', 1, 15] will generate id between 1 and 15

• not between − similar to the between operator, but between is replaced with not between

• in − ['in', 'id', [5,10,15]] will generate id in (5,10,15)

• not in − similar to the in operator, but in is replaced with not in

• like − ['like', 'name', 'user'] will generate name like '%user%'

• or like − similar to the like operator, but or is used to split the like predicates

• not like − similar to the like operator, but like is replaced with not like

• or not like − similar to the not like operator, but or is used to concatenate the not like predicates

• exists − requires one operand which must be an instance of the yii\db\query class

• not exists − similar to the exists operator, but builds a not exists (subquery) expression

• <, <=, >, >=, or any other db operator: ['<', 'id', 10] will generate id<10

example of operator format

public function actiontestdb() {
   $users = (new \yii\db\query())
      ->select(['id', 'name', 'email'])
      ->from('user')
      ->where(['between', 'id', 5, 7])
      ->all();
   var_dump($users);
}

following will be the output.

orderby() function

the orderby() function defines the order by fragment.

example −

public function actiontestdb() {
   $users = (new \yii\db\query())
      ->select(['id', 'name', 'email'])
      ->from('user')
      ->orderby('name desc')
      ->all();
   var_dump($users);
}

following will be the output.

groupby() function

the groupby() function defines the group by fragment, while the having() method specifies the having fragment.

example −

public function actiontestdb() {
   $users = (new \yii\db\query())
      ->select(['id', 'name', 'email'])
      ->from('user')
      ->groupby('name')
      ->having('id < 5')
      ->all();
   var_dump($users);
}

following will be the output.

the limit() and offset() methods defines the limit and offset fragments.

example −

public function actiontestdb() {
   $users = (new \yii\db\query())
      ->select(['id', 'name', 'email'])
      ->from('user')
      ->limit(5)
      ->offset(5)
      ->all();
   var_dump($users);
}

you can see the following output −

the yii\db\query class provides a set of methods for different purposes −

• all() − returns an array of rows of name-value pairs.

• one() − returns the first row.

• column() − returns the first column.

• scalar() − returns a scalar value from the first row and first column of the result.

• exists() − returns a value indicating whether the query contains any result

• count() returns the result of a count query

• other aggregation query methods − includes sum($q), average($q), max($q), min($q). the $q parameter can be either a column name or a db expression.