Summary of common methods for Yii2 database operations and summary of yii2 Database

Summary of common methods for Yii2 database operations and summary of yii2 Database

This document describes common methods for operating the Yii2 database. We will share this with you for your reference. The details are as follows:

Query:

// find the customers whose primary key value is 10$customers = Customer::findAll(10);$customer = Customer::findOne(10);// the above code is equivalent to:$customers = Customer::find()->where(['id' => 10])->all();// find the customers whose primary key value is 10, 11 or 12.$customers = Customer::findAll([10, 11, 12]);$customers = Customer::find()->where(['IN','id',[10,11,12]])->all();// the above code is equivalent to:$customers = Customer::find()->where(['id' => [10, 11, 12]])->all();// find customers whose age is 30 and whose status is 1$customers = Customer::findAll(['age' => 30, 'status' => 1]);// the above code is equivalent to:$customers = Customer::find()->where(['age' => 30, 'status' => 1])->all();// use params binding$customers = Customer::find()->where('age=:age AND status=:status')->addParams([':age'=>30,':status'=>1])->all();// use index$customers = Customer::find()->indexBy('id')->where(['age' => 30, 'status' => 1])->all();// get customers count$count = Customer::find()->where(['age' => 30, 'status' => 1])->count();// add addition condition$customers = Customer::find()->where(['age' => 30, 'status' => 1])->andWhere('score > 100')->orderBy('id DESC')->offset(5)->limit(10)->all();// find by sql$customers = Customer::findBySql('SELECT * FROM customer WHERE age=30 AND status=1 AND score>100 ORDER BY id DESC LIMIT 5,10')->all();

Modify:

// update status for customer-10$customer = Customer::findOne(10);$customer->status = 1;$customer->update();// the above code is equivalent to:Customer::updateAll(['status' => 1], 'id = :id',[':id'=>10]);

Delete:

// delete customer-10Customer::findOne(10)->delete();// the above code is equivalent to:Customer::deleteAll(['status' => 1], 'id = :id',[':id'=>10]);

---------------- Use subquery ----------------------

$subQuery = (new Query())->select('COUNT(*)')->from('customer');// SELECT `id`, (SELECT COUNT(*) FROM `customer`) AS `count` FROM `customer`$query = (new Query())->select(['id', 'count' => $subQuery])->from('customer');

---------------- Handwritten SQL -----------------------

// select$customers = Yii::$app->db->createCommand('SELECT * FROM customer')->queryAll();// updateYii::$app->db->createCommand()->update('customer',['status'=>1],'id=10')->execute();// deleteYii::$app->db->createCommand()->delete('customer','id=10')->execute();//transaction// outer$transaction1 = $connection->beginTransaction();try {  $connection->createCommand($sql1)->execute();  // internal  $transaction2 = $connection->beginTransaction();  try {    $connection->createCommand($sql2)->execute();    $transaction2->commit();  } catch (Exception $e) {    $transaction2->rollBack();  }  $transaction1->commit();} catch (Exception $e) {  $transaction1->rollBack();}

--------------- Master/Slave configuration ----------------------

[  'class' => 'yii\db\Connection',  // master  'dsn' => 'dsn for master server',  'username' => 'master',  'password' => '',  // slaves  'slaveConfig' => [    'username' => 'slave',    'password' => '',    'attributes' => [      // use a smaller connection timeout      PDO::ATTR_TIMEOUT => 10,    ],  ],  'slaves' => [    ['dsn' => 'dsn for slave server 1'],    ['dsn' => 'dsn for slave server 2'],    ['dsn' => 'dsn for slave server 3'],    ['dsn' => 'dsn for slave server 4'],  ],]