Yii2数据库操作常用方法小结

// 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();

// 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 customer-10
customer::findone(10)->delete();
// the above code is equivalent to:
customer::deleteall(['status' => 1], 'id = :id',[':id'=>10]);

$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');

// select
$customers = yii::$app->db->createcommand('select * from customer')->queryall();
// update
yii::$app->db->createcommand()->update('customer',['status'=>1],'id=10')->execute();
// delete
yii::$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();
}

[
  'class' => 'yiidbconnection',
  // 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'],
  ],
]