YII2 MYSQL数据库CURD增删改查

 

/*==================== newbaseModel 数据库增删改查方法 start ================================*/

    public function newbaseGetOne($condition = [],$orderBy = 'id DESC',$select = array()){
        $query =   self::find()->select($select);
        $query->orderBy($orderBy);
        if($condition){
            $query->where($condition);
        }
        return $query->asArray()->one();
    }

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

    }

    //通过条件查询多条关联模型记录
    public function newbaseGetAllRelation($condition = [],$tables = [],$orderBy = '',$select = array(),$joinType = 'INNER JOIN',$distinct = false){
        $query =   self::find()->select($select);
//        $query->innerJoinWith($tables);
//        $query->with($tables);
//        $query->joinWith($tables, true, 'RIGHT JOIN');
        $query->joinWith($tables,true,$joinType);
        $query->orderBy($orderBy);
        if($distinct == true){
            $query->distinct();
        }
        if($condition){
            $query->where($condition);
        }

        return $query->asArray()->all();
    }

    //获取分页数据
    public function newbasePageData($condition = [],$page = 1,$pageSize = 10,$orderBy = 'id DESC',$select = array()){
        //$select 过滤字段 ['id','field1','field2']

        $page = ( $page - 1 ) ;
        $query =   self::find()->select($select);
        $provider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => [
                'pageSize' => $pageSize,
                'page' => $page,
            ],
        ]);
        $query->orderBy($orderBy);
        if($condition){
            $query->where($condition);
        }
        //pr($query ->createCommand()->getRawSql());
        return $provider;
    }

    //获取分页数据 并且 关联模型记录
    //$joinType  LEFT JOIN ,INNER JOIN RIGHT JOIN
    public function newbasePageDataRelation($condition = [],$page = 1,$pageSize = 10,$tables,$orderBy = '',$select = array(),$joinType = 'INNER JOIN',$distinct = false){
        //$select 字段过滤 如： ['id','field1','field2']
        //$tables 关联模型表 如： ['table1','table2']
        $page = ( $page - 1 );
        $query =   self::find()->select($select);
//        $query->with($tables);
//        $query->joinWith($tables, true, 'RIGHT JOIN');
        //$query->innerJoinWith($tables);
        $query->joinWith($tables,true,$joinType);
        $query->orderBy($orderBy);
        if($distinct == true){
            $query->distinct();
        }
        $provider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => [
                'pageSize' => $pageSize,
                'page' => $page,
            ],
        ]);

        if($condition){
            $query->where($condition);
        }
        //pr($query ->createCommand()->getRawSql());
        return $provider;
    }

    /*==================== newbaseModel 数据库增删改查方法 end ================================*/

    /*==================== baseModel 数据库增删改查方法 start ================================*/
    //新增一条数据
    public function baseInsertData($data){
        if(!is_array($data)) {return false;;};
        $db = \Yii::$app->db;
        $ok = $db->createCommand()->insert(self::tableName(), $data)->execute();

        if($ok){
            //返回自增id
            return $db->getLastInsertID();
        }else{
            return false;
        }
    }

    //新增一条数据,返回原数据
    public function baseCreateData($data){
        if(!is_array($data)) {return false;};
        $db = \Yii::$app->db;
        $ok = $db->createCommand()->insert(self::tableName(), $data)->execute();
        if($ok){
            //返回自增id和新增数据
            $data['id'] = $db->getLastInsertID();
            return $data;
        }else{
            return false;
        }
    }

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

    /* *
     * 通过条件查询一条关联模型记录
     * $joinType  LEFT JOIN ,INNER JOIN RIGHT JOIN
     * */
    public function baseGetOneRelation($condition = [],$tables = [],$orderBy = '',$select = array(),$joinType = 'INNER JOIN'){
//        return self::find()->innerJoinWith($tables)->select($select)->where($condition)->orderBy($orderBy)->asArray()->one();
        $data =  self::find()->joinWith($tables,true,$joinType)->select($select)->where($condition)->orderBy($orderBy)->asArray()->one();
        if(empty($data)){
            $data = [];
        }
        return $data;
    }

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

    //通过条件查询多条关联模型记录
    //$joinType  LEFT JOIN ,INNER JOIN RIGHT JOIN
    public function baseGetAllRelation($condition = [],$tables = [],$orderBy = '',$select = array(),$joinType = 'INNER JOIN'){
//        return self::find()->joinWith($tables)->select($select)->where($condition)->orderBy($orderBy)->asArray()->all();
        //$data = self::find()->innerJoinWith($tables)->select($select)->where($condition)->orderBy($orderBy)->asArray()->all();
        $data = self::find()->joinWith($tables,true,$joinType)->select($select)->where($condition)->orderBy($orderBy)->asArray()->all();
        if(empty($data)){
            $data = [];
        }
        return $data;
    }

    //根据id删除一条数据
    public function baseDeleteById($id){
        $this->id = $id;
        //return $this->findOne($id)->delete();
        $status = self::findOne($id)->delete();
        if(empty($status)){
            return false;
        }else{
            return true;
        }

    }

    //删除指定条件的数据
    public function baseDeleteAll($condition = []){
        if(empty($condition) || !is_array($condition)) {return false;};
        $status = self::deleteAll($condition);
        if(empty($status)){
            return false;
        }else{
            return true;
        }
    }

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


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

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

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

    //更新符合条件的数据
    public function baseUpdateData($data,$condition = []){
        if(empty($data)) {return false;};
        if(empty($condition) || !is_array($condition)) {return false;;};
        $res = self::updateAll($data,$condition);
        if($res === false){
            return false;
        }
        return true;
    }

    //获取分页数据
    public function basePageData($condition = [],$page = 1,$pageSize = 10,$orderBy = 'id DESC',$select = array()){
        //$select 过滤字段 ['id','field1','field2']
        $page = ( $page - 1 ) * $pageSize;
        $data = self::find()->select($select)->where($condition)->offset($page)->limit($pageSize)->orderBy($orderBy)->asArray()->all();
        if(empty($data)){
            $data = [];
        }
        return $data;
    }


    //获取分页数据 并且 关联模型记录
    //$joinType  LEFT JOIN ,INNER JOIN RIGHT JOIN
    public function basePageDataRelation($condition = [],$page = 1,$pageSize = 10,$tables = [],$orderBy = '',$select = array(),$joinType = 'INNER JOIN'){
        //$select 字段过滤 如： ['id','field1','field2']
        //$tables 关联模型表 如： ['table1','table2']
//        return $this->find()->joinWith($tables)->select($select)->where($condition)->orderBy($orderBy)->offset($page)->limit($pageSize)->asArray()->all();
        $page = ( $page - 1 ) * $pageSize;
        //$data = self::find()->innerJoinWith($tables)->select($select)->where($condition)->orderBy($orderBy)->offset($page)->limit($pageSize)->asArray()->all();
        $data = self::find()->joinWith($tables,true,$joinType)->select($select)->where($condition)->orderBy($orderBy)->offset($page)->limit($pageSize)->asArray()->all();
        //return self::find()->joinWith($tables)->select($select)->where($condition)->orderBy($orderBy)->offset($page)->limit($pageSize)->asArray()->all();
//        return self::find()->joinWith($tables)->select($select)->where($condition)->orderBy($orderBy)->offset($page)->limit($pageSize)->createCommand()->getRawSql();
        if(empty($data)){
            $data = [];
        }
        return $data;
    }

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

    //通过条件查询记录总和
    public function baseGetSum($condition = [],$field = '',$select = array()){
        $data =  self::find()->select($select)->where($condition)->sum($field);
        return empty($data)?'0':$data;
    }

    //通过条件查询记录条数 并且 关联模型
    //$joinType  LEFT JOIN ,INNER JOIN RIGHT JOIN
    public function baseGetCountRelation($condition = [],$tables = [],$select = array(),$joinType = 'INNER JOIN'){
        //return self::find()->innerJoinWith($tables)->select($select)->where($condition)->count();
        return self::find()->joinWith($tables,true,$joinType)->select($select)->where($condition)->count();
       // return self::find()->joinWith($tables)->select($select)->where($condition)->count();
    }

    /*==================== baseModel 数据库增删改查方法 end ================================*/

 

 

 

增删改查使用方法：

                //选择分组
                $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)