$statusdb->updateAll(['status'=>2],'earnestId=:earnestIdandtype=:type',['earnestId'=>$infoArray[1],'type'=>4]);//改变状态

Collect::updateAll(['isDel'=>'1'],'Id=:Id',array(':Id'=>$fangId))

修改2个where条件的值

一次修改多个data值

$field = array();
$field['depId'] = $data['depId'];
$field['areaId'] = $data['areaId'];
$field['storeName'] = $data['storeName'];
$field['lat'] = $data['lat'];
$field['lng'] = $data['lng'];
$field['address'] = $data['address'];
$field['phone'] = $data['phone'];

$model->updateAll($field,'storeId=:storeId',array(':storeId'=>$data['storeId']));

2个二维数组数据插入（使用之前先clone否则值被覆盖）

foreach($data['role_parma'] as $k=> $v1) {
if (!empty($v1)){
$_usermodel = clone $usermodel;
$_usermodel->storeId = $data['storeId'];
$_usermodel->depId = $data['depId'];
$_usermodel->role_parma = $v1;
$_usermodel->userId = $data['userId'][$k];
$_usermodel->whoCreate = $login['userId'];
$_usermodel->isDel = 0;
$_usermodel->save(false);
}
}

执行原生sql

$connection = Yii::$app->getDb();
$ziduan=explode("=", $result['infoAddress']);

$sql = "update " . $result['tableName'] . " set " .$result['field']."=".$result['status']. " where " .$ziduan[0] ."=" .$ziduan[1]; //修改对方表为同意状态为2
$command = $connection->createCommand($sql);
$command->execute();

join表

public static function getBindInfo($shopId){
if(empty($shopId)) return array();

$query = self::find();
$query->asArray();
$query->where(['mn_store_user.depId'=>$shopId]);
$query->select("mn_store_user.role_parma,user.*");
$query->leftJoin('mn_user as user','mn_store_user.userId=user.mnUserId');
$data = $query->all();
$return = array();
if($data){
foreach($data as $val){
$return[$val['role_parma']] = $val;
}
}
return $return;

}

//打印sql

echo$query->createCommand()->getRawSql();exit;

打印错误

$db->error();

$query->Where(['followUser'=>explode(',',$data)]);

in查询了

$query->andFilterWhere(['or',['like','villageName',$keyword],['like','ownerName',$keyword],['like','ownerTel',$keyword],['like','fangNumber',$keyword]]);

多个or like查询

$query->andWhere(['like','c.depId',','.$rShopId.',']);

$query->andWhere(['between','addTime',strtotime(date("Y-m-d00:00:01",strtotime($time3))),strtotime(date("Y-m-d23:23:59",strtotime($time4)))]);

between查询

$query->andWhere(['notin','fangId',$dealdata]);$dealdata是个一维数组notin写法

事务回滚功能：

$transaction=Yii::$app->db->beginTransaction();

try{

提交数据操作xxxx 然后return 正常操作

}catch (Exception $e) {
$transaction->rollback();//如果操作失败, 数据回滚
return $this->render('create', [
'model' => $model,
]);
}

if(empty($id)||empty($configId)){
return false;
}

$connection = \Yii::$app->db;
$data_extend = array();
$tiem = time();
foreach($configId as $ke=>$re){
if($re=='0'){
continue;
}
$data_extend[]=array(
$configId[$ke],
$id,
1,
$tiem
);
}

if(empty($data_extend)){
return false;
}
$infos = $connection->createCommand()->batchInsert(ReGoodsConfig::tableName(), ['ItemId', 'configId','isDel','createTime'], $data_extend)->execute();
if(!$infos){
return false;
}

原生sql查询数据，结果是对象
$connection = Yii::$app->getDb();
$sql = "select * from iom_examine_communication_reply where (sendUserId=".$userId." and content!='') or (toUserId=".$userId." and content is null) ORDER BY UpdateTime desc"; //
$command = $connection->createCommand($sql);
$data=$command->queryall();

更新单个数据找到主键更新

$BudgetDetail=BudgetDetail::findOne($budgetDetailId)

$BudgetDetail->budgetAmount = $request["budgetAmount"];
$BudgetDetail->budgetUnitPrice = $request["budgetUnitPrice"];
$BudgetDetail->budgetNum = $request["budgetNum"];
$updateRes = $BudgetDetail->update();//更新副表状态

事务回滚结构

public function actionDelete($id,$userId)
{
$transaction=Yii::$app->db->beginTransaction();
try{
$model = $this->findModel($id);
$model->status =4;
if ($model->save()) {
$flowRecord = FlowNoteRecord::setDelStatus($model->budgetId,$model->tableId,$userId);
$handle = Handle::setDelStatus($model->budgetId,$model->tableId);
if($flowRecord&&$handle){
$transaction->commit();//提交事务会真正的执行数据库操作
return $this->redirect(['index']);
}else{
$transaction->rollback();//如果操作失败, 数据回滚
return $this->redirect(['index']);
}
}else{
$transaction->rollback();//如果操作失败, 数据回滚
return $this->redirect(['index']);
}

}catch (Exception $e) {
$transaction->rollback();//如果操作失败, 数据回滚
return $this->redirect(['index']);
}
}

$query->andWhere(['and',['isDel'=>1],['in','status',[1,2]]]);

$query->where(
['and',['=', 'isDel', 1],['=', 'status', 2],['or',['and',['=', 'budgetType', 1],['in', 'customerId', $organization]],['and',['=', 'budgetType', 2],['in', 'customerId', $project]]]]
);