YII2 database query practice

This article mainly introduces relevant information on YII2 database query practice. Friends who need it can refer to

Initial exploration of the yii2 framework, simple practice of basic database operations such as addition, deletion, modification, and related query.

Database configuration.

/config/db.php Configure database

During practice, there is a test library - "test table -" two records are as follows

mysql> select * from test;
+----+---- ----+
| id | name |
+----+--------+
| 1 | zhuai |
| 2 | heng |
+----+--------+
18 rows in set (0.00 sec)

sql query method

yii2 provides the original database query method findBySql; at the same time, through placeholder This method automatically performs basic SQL injection defense. Code

// The most basic query method
$sql = "select * from test where 1";
$res = Test::findBySql($sql)->all();
var_dump(count($res )); // res->2
// findbysql prevents sql injection
$id = '1 or 1=1';
$sql = "select * from test where id = " . $id;
$res = Test::findBySql($sql)- >all();
var_dump(count($res)); // res-> 2
$sql = "select * from test where id = :id";
// The locator will automatically prevent sql injection
$res = Test::findBySql($sql,array(":id"=>$id))->all();
var_dump(count($res)); // res->1

activeRecord query method

In addition to the original sql method, each framework will provide corresponding encapsulated query methods, and the same is true for yii2.

The basic method of creating a model

yii is as follows. The code is not described in detail below.

namespace appmodels;
use Yii;
use yiidbActiveRecord;
class Test extends ActiveRecord
{
// Optional, corresponding table: If the default class name and table name match, this function is not needed
public static function tableName ()
{
return 'test';
}
// Optional, validator: mainly used to verify each field
public function rules(){
return [
['id', 'integer'],
['name', 'string', 'length' => [0, 100]],
];
}
}

You need to introduce model when using it

use appmodelsTest;
Add operation
//add Operation
$test = new Test();
$test->name = 'test';
// Legality verification
$test->validate();
if($test->hasErrors() ){
echo "Data is illegal";
die;
}
$test->save();

Query operation

Query operation first go to the official document

activeRecord doc

where doc

Need to emphasize The thing is: Yii query provides a lot of rich libraries, such as batch query processing in the code, etc. For details, please see the documentation.

// select
// id = 1
$res = Test::find()->where(['id' => 1])->all();
var_dump(count($res )); //1
// id > 0
$res = Test::find()->where(['>','id',0])->all();
var_dump (count($res)); //2
// id > =1 id <=2
$res = Test::find()->where(['between','id',1, 2])->all();
var_dump(count($res)); //2
// name field like
$res = Test::find()->where(['like', ' name', 'cuihuan'])->all();
var_dump(count($res)); //2
// Query usage obj->array
$res = Test::find()- >where(['between','id',1,2])->asArray()->all();
var_dump($res[0]['id']); //2
// Batch query, for batch query of large memory operation
foreach (Test::find()->batch(1) as $test) {
var_dump(count($test));
}

delete operation

//delete
// Select to delete
$res = Test::find()->where(['id'=>1])->all();
$res[0]->delete() ;
// Directly delete
var_dump(Test::deleteAll('id>:id', array(':id' => 2)));

Modification operation

In addition to the code method, yii2 directly provides update operation.

// Activity record modification
$res = Test::find()->where(['id'=>4])->one();
$res->name = "update" ;
$res->save();

Associated query operation

Two tables in the associated query example:

A student table (student): id, name;

A score table (score): id ,stu_id,score

// All scores of the corresponding student
$stu = Student::find()->where(['name'=>'xiaozhuai'])->one();
var_dump( $stu->id);
// Basic acquisition
$scores_1 = $stu->hasMany('appmodelScore',['stu_id'=>$stu->id])->asArray()- >all();
$scores_2 = $stu->hasMany(Score::className(),['stu_id'=>'id'])->asArray()->all();
var_dump($scores_1);
var_dump($scores_2);

Two related query methods; however, when performing related operations in the controller, the code is too confusing, so the call is encapsulated in the model

First encapsulate the relevant associated calling functions in the student model

namespace appmodels;
use Yii;
use yiidbActiveRecord;
class Student extends ActiveRecord
{
public static function tableName()
{
return 'student' ;
}
//Get score information
public function getScores()
{
$scores = $this->hasMany(Score::className(), ['stu_id' => 'id'])-> asArray()->all();
return $scores;
}
}

and then called directly, two calling methods

//Call after function encapsulation
$scores = $stu->getScores() ;
var_dump($scores);
//Use the automatic calling method of __get
$scores = $stu->scores;
var_dump($scores);

Finally

deployment and use of the above in yii2 Some basic operations such as additions, deletions, modifications, and related queries during the process.