YII2.0 数据库增删改查

/*==================== dkhBaseModel 数据库增删改查方法 start ================================*/
    //新增一条数据
    public function baseInsertData($data){
        if(!is_array($data)) {return $this->setError(['errmsg'=>'data Error']);};
        $db = Yii::$app->db;
        $ok = $db->createCommand()->insert($this->tableName(), $data)->execute();
        if($ok){
            //返回自增id
            return $db->getLastInsertID();
        }else{
            return $this->setError(['errmsg'=>'插入记录失败']);
        }
    }

    //通过条件查询一条记录
    public function baseGetOne($condition = [],$orderBy = 'id DESC',$select = array()){
        //$condition 搜索条件 如：$condition = ['and'];$condition[] = ['=','id',1];$condition[] = ['LIKE','name','dkh'];
        //$select 字段过滤 如： ['id','field1','field2']
        return $this->find()->select($select)->where($condition)->orderBy($orderBy)->asArray()->one();
    }

    //通过条件查询一条关联模型记录
    public function baseGetOneRelation($condition = [],$tables,$orderBy = '',$select = array()){
        return $this->find()->joinWith($tables)->select($select)->where($condition)->orderBy($orderBy)->asArray()->one();
    }

    //获取全部数据
    public function baseGetAll($condition = [],$orderBy = 'id DESC',$select = array()){
        return $this->find()->select($select)->where($condition)->orderBy($orderBy)->asArray()->all();
    }

    //通过条件查询多条关联模型记录
    public function baseGetAllRelation($condition = [],$tables,$orderBy = '',$select = array()){
        return $this->find()->joinWith($tables)->select($select)->where($condition)->orderBy($orderBy)->asArray()->all();
    }

    //根据id删除一条数据
    public function baseDeleteById($id){
        $this->id = $id;
        return $this->findOne($id)->delete();

    }

    //删除指定条件的数据
    public function baseDeleteAll($condition = []){
        if(empty($condition)) {return $this->setError(['errmsg'=>'deleteAll Error']);};
        return $this->deleteAll($condition);
    }

    //执行原生sql语句，一般用于更新，批量新增，删除数据
    public function baseExecuteSql($sql){
        $db = Yii::$app->db;
        //在调用的地方做执行是否成功的判断
        return $db->createCommand($sql)->execute();
    }


    //执行原生sql语句，一般用于查询数据 返回单行
    public function baseQueryOneSql($sql){
        $db = Yii::$app->db;
        return $db->createCommand($sql)->queryOne();
    }

    //执行原生sql语句，一般用于查询数据 返回多行
    public function baseQueryAllSql($sql){
        $db = Yii::$app->db;
        return $db->createCommand($sql)->queryAll();
    }

    //通过主键查询一条记录
    public function baseGetOneByid($id){
        $model =  $this->findOne($id);
        if($model){
            return $model->attributes;
        }else{
            return array();
        }
    }

    //更新符合条件的数据
    public function baseUpdateData($data,$condition = []){
        if(empty($condition)) {return $this->setError(['errmsg'=>'condition Empty']);};
        $res = $this->updateAll($data,$condition);
        if($res === false){
            return $this->setError(['errmsg'=>'更新失败']);
        }
        return $res;
    }

    //获取分页数据
    public function basePageData($condition = [],$page,$pageSize,$orderBy = 'id DESC',$select = array()){
        //$page,$pageSize   相当于 LIMIT $page,$pageSize
        //$select 过滤字段 ['id','field1','field2']
        return $this->find()->select($select)->where($condition)->offset($page)->limit($pageSize)->orderBy($orderBy)->asArray()->all();
    }


    //获取分页数据 并且 关联模型记录
    public function basePageDataRelation($condition = [],$page,$pageSize,$tables,$orderBy = '',$select = array()){
        //$page,$pageSize  相当于SQL语句 LIMIT $page,$pageSize
        //$select 字段过滤 如： ['id','field1','field2']
        //$tables 关联模型表 如： ['table1','table2']
        return $this->find()->joinWith($tables)->select($select)->where($condition)->orderBy($orderBy)->offset($page)->limit($pageSize)->asArray()->all();
    }

    //通过条件查询记录条数
    public function baseGetCount($condition = [],$select = array()){
        return $this->find()->select($select)->where($condition)->count();
    }

    //通过条件查询记录条数 并且 关联模型
    public function baseGetCountRelation($condition = [],$tables,$select = array()){
        return $this->find()->joinWith($tables)->select($select)->where($condition)->count();
    }




    //通过条件查询一条记录
    public function baseModelGetOne($params){
        if(isset($params['condition']) && is_array($params['condition']) && !empty($params['condition'])){
            $condition = $params['condition'];
        }else{
            $condition = [];
        }
        if(isset($params['field']) && is_array($params['field']) && !empty($params['field'])){
            $field = $params['field'];
        }else{
            $field = [];
        }
        if(isset($params['order'])  && !empty($params['order'])){
            $order = $params['order'];
        }else{
            $order = 'id DESC';
        }
        return $this->find()->select($field)->where($condition)->orderBy($order)->asArray()->one();
    }

    //通过条件查询一条关联模型记录
    public function baseModelGetOneRelation($params){
        if(isset($params['condition']) && is_array($params['condition']) && !empty($params['condition'])){
            $condition = $params['condition'];
        }else{
            $condition = [];
        }
        if(isset($params['field']) && is_array($params['field']) && !empty($params['field'])){
            $field = $params['field'];
        }else{
            $field = [];
        }
        if(isset($params['order'])  && !empty($params['order'])){
            $order = $params['order'];
        }else{
            $order = 'id DESC';
        }
        if(isset($params['join']) && is_array($params['join'])  && !empty($params['join'])){
            $join = $params['join'];
        }else{
            $join = '';
        }
        return $this->find()->joinWith($join)->select($field)->where($condition)->orderBy($order)->asArray()->one();
    }


    //获取全部数据
    public function baseModelGetAll($params){
        if(isset($params['condition']) && is_array($params['condition']) && !empty($params['condition'])){
            $condition = $params['condition'];
        }else{
            $condition = [];
        }
        if(isset($params['field']) && is_array($params['field']) && !empty($params['field'])){
            $field = $params['field'];
        }else{
            $field = [];
        }
        if(isset($params['order'])  && !empty($params['order'])){
            $order = $params['order'];
        }else{
            $order = 'id DESC';
        }
        return $this->find()->select($field)->where($condition)->orderBy($order)->asArray()->all();
    }

    //通过条件查询多条关联模型记录
    public function baseModelGetAllRelation($params){
        if(isset($params['condition']) && is_array($params['condition']) && !empty($params['condition'])){
            $condition = $params['condition'];
        }else{
            $condition = [];
        }
        if(isset($params['field']) && is_array($params['field']) && !empty($params['field'])){
            $field = $params['field'];
        }else{
            $field = [];
        }
        if(isset($params['order'])  && !empty($params['order'])){
            $order = $params['order'];
        }else{
            $order = 'id DESC';
        }
        if(isset($params['join']) && is_array($params['join'])  && !empty($params['join'])){
            $join = $params['join'];
        }else{
            $join = '';
        }
        return $this->find()->joinWith($join)->select($field)->where($condition)->orderBy($order)->asArray()->all();
    }



    //获取分页数据
    public function baseModelPageData($params){
        if(isset($params['condition']) && is_array($params['condition']) && !empty($params['condition'])){
            $condition = $params['condition'];
        }else{
            $condition = [];
        }
        if(isset($params['field']) && is_array($params['field']) && !empty($params['field'])){
            $field = $params['field'];
        }else{
            $field = [];
        }
        if(isset($params['order'])  && !empty($params['order'])){
            $order = $params['order'];
        }else{
            $order = 'id DESC';
        }
        if(isset($params['page'])){
            $page = $params['page'];
        }else{
            $page = 0;
        }
        if(isset($params['pageSize'])){
            $pageSize = $params['pageSize'];
        }else{
            $pageSize = 10;
        }
        return $this->find()->select($field)->where($condition)->offset($page)->limit($pageSize)->orderBy($order)->asArray()->all();
    }


    //获取分页数据 并且 关联模型记录
    public function baseModelPageDataRelation($params){
        if(isset($params['condition']) && is_array($params['condition']) && !empty($params['condition'])){
            $condition = $params['condition'];
        }else{
            $condition = [];
        }
        if(isset($params['field']) && is_array($params['field']) && !empty($params['field'])){
            $field = $params['field'];
        }else{
            $field = [];
        }
        if(isset($params['order'])  && !empty($params['order'])){
            $order = $params['order'];
        }else{
            $order = 'id DESC';
        }
        if(isset($params['join']) && is_array($params['join'])  && !empty($params['join'])){
            $join = $params['join'];
        }else{
            $join = '';
        }
        if(isset($params['page'])){
            $page = $params['page'];
        }else{
            $page = 0;
        }
        if(isset($params['pageSize'])){
            $pageSize = $params['pageSize'];
        }else{
            $pageSize = 10;
        }
        return $this->find()->joinWith($join)->select($field)->where($condition)->orderBy($order)->offset($page)->limit($pageSize)->asArray()->all();
    }

    //通过条件查询记录条数
    public function baseModelGetCount($params){
        if(isset($params['condition']) && is_array($params['condition']) && !empty($params['condition'])){
            $condition = $params['condition'];
        }else{
            $condition = [];
        }
        if(isset($params['field']) && is_array($params['field']) && !empty($params['field'])){
            $field = $params['field'];
        }else{
            $field = [];
        }
        return $this->find()->select($field)->where($condition)->count();
    }

    //通过条件查询记录条数 并且 关联模型
    public function baseModelGetCountRelation($params){
        if(isset($params['condition']) && is_array($params['condition']) && !empty($params['condition'])){
            $condition = $params['condition'];
        }else{
            $condition = [];
        }
        if(isset($params['field']) && is_array($params['field']) && !empty($params['field'])){
            $field = $params['field'];
        }else{
            $field = [];
        }
        if(isset($params['join']) && is_array($params['join'])  && !empty($params['join'])){
            $join = $params['join'];
        }else{
            $join = '';
        }
        return $this->find()->joinWith($join)->select($field)->where($condition)->count();
    }



    //删除指定条件的数据
    public function baseModelDeleteAll($params){
        if(empty($params['condition'])) {return $this->setError(['errmsg'=>'deleteAll Error']);};
        return $this->deleteAll($params['condition']);
    }

    //更新符合条件的数据
    public function baseModelUpdateData($params){
        if(empty($params['condition'])) {return $this->setError(['errmsg'=>'condition is Empty']);};
        $res = $this->updateAll($params['data'],$params['condition']);
        if($res === false){
            return $this->setError(['errmsg'=>'更新失败']);
        }
        return $res;
    }

 增删改查使用方法：

                //选择分组
                $condition = ['and'];
                $condition[] = ['=','member.shop_id',$params['shop_id']];
                $condition[] = ['IN','member.member_group_id',$params['member_tag']['groups']];　　//索引数组
                $wshMemberModel = new WshMember();
                $memberList = $wshMemberModel->baseGetAllRelation($condition,['wxUserInfos'],'member.id DESC',['member.id']);

 

$wshWxUsersModel = new WshWxUsers();
                $userList = $wshWxUsersModel->baseGetAll(['shop_id'=>$params['shop_id'],'user_id'=>$uid],'id DESC',['user_id','open_id']);

 

$insertData = [
            'material_id'=>isset($params['material_id'])?$params['material_id']:0,
            'shop_id'=>$params['shop_id'],
            'shop_sub_id'=>$_SESSION['_staff']['shop_sub_id'],
            'send_time'=>$now,
            'send_type'=>$params['send_type'],
            'text_content'=>$params['text_content'],
            'sort_content'=>$params['sort_content'],
        ];

        $insertId = $wshWxMessageSendMutilModel->baseInsertData($insertData);

 

            $dkhWxMessageMemberMutilModel = new DkhWxMessageMemberMutil();
            $successValues = '';$successSql = '';
            //组装sql语句
            foreach($successList as $item){
                $successValues .= "('{$insertId}','{$item['user_id']}','1','{$now}','{$now}'),";
            }
            if(!empty($successValues)) {
                $successSql = "INSERT INTO `dkh_wx_message_member_mutil` (`send_id`,`user_id`,`send_result`,`created`,`modified`) VALUES " . substr($successValues, 0, -1) . ';';
                //插入发送成功数据
                $dkhWxMessageMemberMutilModel->baseExecuteSql($successSql);
            }

 

            //消费减余额
            $balance = $balanceData['balance'] - $data['balance'];
            $map = ['and'];
            $map[] = ['=', 'member_id', $orderData['member_id']];
            $map[] = ['>=', 'balance', $data['balance']];
            $balanceOk = $dkhMemberExtendModel->baseUpdateData(['balance' => $balance], $map);

 

            //首充 ，可参加充值赠送活动
            $condition = ['and'];
            $condition[] = ['=', 'dkh_recharge_activity.shop_id', $data['shop_id']];
            $condition[] = ['=', 'dkh_recharge_activity.is_del', 0];
            $condition[] = ['=', 'dkh_recharge_activity.status', 1];
            $condition[] = ['<=', 'dkh_recharge_activity.start_time', $now];
            $condition[] = ['>=', 'dkh_recharge_activity.end_time', $now];
            $dkhRechargeActivity = new DkhRechargeActivity();
            $rechargeActivityData = $dkhRechargeActivity->baseGetOneRelation($condition, ['dkhRechargeActivityRelation'], 'dkh_recharge_activity.id DESC');

 

Yii2查询之where条件拼装

熟悉Yii2的查询条件后，用Active Record查询数据非常方便。
以下我们介绍where()方法当中，条件的拼装方式。
 
#某个值为null，会用IS NULL来生成语句:
['type' => 1, 'status' => 2]            // 生成：(type = 1) AND (status = 2)
['id' => [1, 2, 3], 'status' => 2]      // 生成：(id IN (1, 2, 3)) AND (status = 2)
['status' => null]                      // 生成：status IS NULL
['NOT', ['type' => null]]                // 生成：type IS NOT NULL
 
#对比
['>', 'id', 1]                                        // 生成：id > 1
['<', 'id', 100]                                      // 生成：id < 100
['=', 'id', 10]                                       // 生成：id = 10
['>=', 'id', 1]                                       // 生成：id >= 1
['<=', 'id', 100]                                     // 生成：id <= 100
['!=', 'id', 10]                                      // 生成：id != 10
 
['and', 'id' => 1, 'id' => 2]                        // 生成：id=1 AND id=2
['and', 'id=1', 'id=2']                              // 生成：id=1 AND id=2
['and', 'type=1', ['or', 'id=1', 'id=2']]            // 生成：type=1 AND (id=1 OR id=2)
 
['or', ['type' => [7, 8, 9]], ['id' => [1, 2, 3]]]   // 生成：(type IN (7, 8, 9) OR (id IN (1, 2, 3)))
 
['not', ['attribute' => null]]                       // 生成：NOT (attribute IS NULL)
 
['between', 'id', 1, 10]                             // 生成：id BETWEEN 1 AND 10
['not between', 'id', 1, 10]                         // 生成：id NOT BETWEEN 1 AND 10
 
['in', 'id', [1, 2, 3]]                               // 生成：id IN (1, 2, 3)
['id' => [4, 8, 15]]                                  // 生成：id IN (4, 8, 15)
['not in', 'id', [1, 2, 3]]                           // 生成：id NOT IN (1, 2, 3)
 
['in', ['id', 'name'], [['id' => 1, 'name' => 'foo'], ['id' => 2, 'name' => 'bar']]]  // 生成：(`id`, `name`) IN ((1, 'foo'), (2, 'bar'))
 
#用子查询作为IN条件的值，如下：
['in', 'user_id', (new Query())->select('id')->from('users')->where(['active' => 1])]
 
['like', 'name', 'tester']                             // 生成：name LIKE '%tester%'
['like', 'name', ['test', 'sample']]                   // 生成：name LIKE '%test%' AND name LIKE '%sample%'
['like', 'name', '%tester', false]                     // 生成：name LIKE '%tester'
                                                       // 这是自定义查询方式，要传入值为false的运算数3，并且自行添加%
['or like', 'name', ['test', 'sample']]                 // 生成：name LIKE '%test%' OR name LIKE '%sample%'
['not like', 'name', 'tester']                          // 生成：name NOT LIKE '%tester%'
['or not like', 'name', ['test', 'sample']]             // 生成：name NOT LIKE '%test%' OR name NOT LIKE '%sample%'
 
['exists', (new Query())->select('id')->from('users')->where(['active' => 1])] // 生成：EXISTS (SELECT "id" FROM "users" WHERE "active"=1)