YII2 常用操作记录

$statusdb->updateAll(['status'=>2], 'earnestId=:earnestId and type=: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-d 00:00:01",strtotime($time3))),strtotime(date("Y-m-d 23:23:59",strtotime($time4)))]);

between查询

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

事务回滚功能：

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