Example of using query generator query() in yii2 framework

Time:2021-4-3

This article describes the use of query generator query () in yii2 framework. The details are as follows:

Yii / db / query in yii2 provides us with rich methods to build complex SQL statements.

The biggest difference between query () and create command is that the latter directly uses the SQL statements we have written. The former generates different SQL through parameters and database types, so it has better migration.

<?php
namespace app\controllers;
 
use YII;
use yii\db\Query;
use yii\web\Controller;
 
class TestController extends Controller
{
  public function actionTest()
  {
    //The use of query in yii2
    //The difference between query and create command is that create command writes a SQL directly to execute.
    //Query is to generate different SQL according to parameters and database types to improve database migration.
 
    //Query multiple records through all
    //I use TB here_ User table to demonstrate
    $data1 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->all();
 
    //Specify where conditional query
    $data2 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where('id=:id', [':id' => '2'])
      ->all();
 
    //Query single record through one
    $data3 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where('id=3')
      ->one();
 
    //Judge whether the record exists
    $exists = (new Query())->from('{{%user}}')
      ->where('name="aaa"')
      ->exists();
 
    if ($exists) {
      Echo 'name = AAA exists';
    }
 
    //Define field alias
    //Note that the real field name is written after the alias
    $data4 = (new Query())->select(['ids' => 'id', 'names' => 'name'])
      ->from('{{%user}}')
      ->where('1=1')
      ->all();
 
    //Order by and limit the number of items
    $data5 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where('1=1')
      ->orderBy('id desc')
      ->limit(3)
      ->all();
 
    //Multiple and conditions
    //The parameter is an array, one key corresponds to one value, and is spliced by default
    $data6 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['id' => 3, 'name' => 'aaa'])
      ->one();
 
    //In condition
    $data7 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['id' => [4, 5, 6]])
      ->all();
 
    //Or as follows
    $data7_2 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['in', 'id', [4, 5, 6]])
      ->all();
 
    //Count statistics
    $count = (new Query())->from('{{%user}}')->count();
    Echo 'total records:', $count;
 
    //Greater than, greater than or equal to, less than, less than or equal to where condition
    $data8 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['>=', 'id', 5])
      ->all();
 
    $data9 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['<=', 'id', 3])
      ->all();
 
    //Query like
    $data10 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['like', 'name', 'dd'])
      ->all();
 
    //Between screening and group by grouping
    //Find out the age between 18 and 24, and group by sex
    $data11 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['between', 'age', 18, 24])
      ->groupBy('sex')
      ->all();
 
    //Having screening
    //Group by sex, then count the number of people more than 3
    $data12 = (new Query())->select(['sex', 'cnt' => 'count(*)'])
      ->from('{{%user}}')
      ->groupBy('sex')
      ->having('cnt > 3')
      ->all();
 
    //Or logic condition
    //Find users with the name AAA or BBB
    //Previously, the where array was passed in the form of key = > value. If you want to express complex logical relations,
    //The first element of an array must declare what logic it is, and or
    //The second element represents the left side of the logic
    //The third element represents the logical right
    $data13 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['or', ['name' => 'aaa'], ['name' => 'bbb']])
      ->all();
 
    //Complex where conditions
    //I'm just here as a demonstration
    //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 and or nested where conditions
    //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();
 
    //Sometimes we need to add the where condition according to the parameters passed by the user
    //Add and condition
    $query = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where('sex=1');
    //Add the condition that age is greater than 18
    $query->andWhere(['>', 'age', 18]);
    echo $query->createCommand()->getRawSql();
 
    //Add or condition
    $query2 = (new Query())->select(['id', 'name'])
      ->from('{{%user}}')
      ->where(['like', 'name', 'aa']);
    //Conditions for appending name similar to BB
    $query2->orWhere(['like', 'name', 'bb']);
    echo $query2->createCommand()->getRawSql();
 
    //Table aliases and join queries
    //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();
  }
}

For more information about Yii, readers who are interested in it can see the following topics: Yii framework introduction and common skills summary, PHP excellent development framework summary, smart template introduction basic course, PHP object-oriented programming introduction course, PHP string usage summary, PHP + MySQL database operation introduction course and PHP common database operation introduction course Summary of writing skills

I hope this article will be helpful to the PHP Programming Based on Yii framework.

Recommended Today

Third party calls wechat payment interface

Step one: preparation 1. Wechat payment interface can only be called if the developer qualification has been authenticated on wechat open platform, so the first thing is to authenticate. It’s very simple, but wechat will charge 300 yuan for audit 2. Set payment directory Login wechat payment merchant platform( pay.weixin.qq . com) — > Product […]