yii2笔记

yii 中orderBy 中加入条件查询的正确姿势

有个业务需要是查询某个字段的值然后再排序，一般做法是子查询或者单独把值放在另个一字段然后查询下面是直接在orderBY里面加入条件

直接上代码

            ->orderBy(
                [
                'is_top' => SORT_DESC, //置顶降序
                'is_full' => SORT_ASC, //是否报满正序
                'display_order' => SORT_ASC, //手动排序正序
                'id' => SORT_DESC, //编号排序倒序
                'case when `activity`.`status` = 30 then 0 else 1 end' => SORT_DESC, //编号排序倒序
                ]
            )

 

打印最后一条数据

$query = Salesorder::find()  
  ->where(['order_id'=>[1,2,3,4]])  
  ->select(['order_id'])  
;

sql调试

 

$commandQuery = clone $query;  
echo $commandQuery->createCommand()->getRawSql();  
exit;

 

$ReSwitchTimer = \app\models\SwitchTimer::find()->andWhere(['in','switch_id',array_column($switch_ids,'id')]);
        $temp = clone $ReSwitchTimer;
        var_dump($temp->createCommand()->getRawSql());

新增后获取自增id

 

$sid = \Yii::$app->db->getLastInsertID();

$SelfExamination = new \app\models\SelfExamination();
                $SelfExamination->self_id = $utime;
                $SelfExamination->equipment_id = $ids;
                $SelfExamination->box_name = $equ_name;
                $SelfExamination->status = $mon['status'];
                $SelfExamination->self_time = time();
                $SelfExamination->save();
                var_dump($SelfExamination->id);die;//取自增id

 

调试
  //插入自检单条记录
             

 

$SelfExamination = new \app\models\SelfExamination();
                $SelfExamination->self_id = "$SelfAll->id";
                $SelfExamination->equipment_id = $ids;
                $SelfExamination->box_name = $equ_name;
                $SelfExamination->status = $mon['status'];
                $SelfExamination->self_time = time();
                if (!$SelfExamination->validate()) {
                    var_dump( $SelfExamination->firstErrors);
                } else {
                    $SelfExamination->save();
                }

mongodb 调试

 

{created:{$gte: new Date('2018-07-05 19:20:00')}}

mongodb 查询数据

$query = new \yii\mongodb\Query();
$where =
    [
        'payload.deviceid' => $serial_number,
        'payload.desired.arrays.0.device.index' => $index,
        'payload.desired.arrays.0.status.meterd' => ['$exists' => true]
    ];

$condition = ["between", "payload.timestamp", $time_min, $time_max];

$log = $query->select(['created', 'payload'])
    ->from('publish_message')
    ->where($where)
    ->andWhere($condition)
    ->orderBy('created desc')
    ->one();

 

联表查询

 

第一种:

$query = new \yii\db\Query();
            
            $list = $query
            ->select(['t2.id', 't2.link', 't2.style', 't2.resource_name', 't2.parent_id', 't2.script'])
            ->from(['t1' => 'ub_role_resource_link'])
            ->leftJoin(['t2' => 'ub_resource'], 't1.resource_id = t2.id')
            ->where(['t1.role_id' => $currUser->role_id, 't2.resource_type' => $type, 'module' => 'backend', 't2.controller' => $controller, 't2.is_valid' => 1])
            ->orderBy('t2.orderby ASC')
            ->all();

 

 

 

第二种:

$lists = \app\services\EquipmentSwitch::find()->andWhere(['ss_equipment_switch.id'=>$switchId])->joinWith('equipment')
->asArray()->all();

public function getEquipment(){


        // 第一个参数为要关联的子表模型类名，
        // 第二个参数指定 通过子表的equipment_id，关联主表的id字段
        return $this->hasOne(\app\services\Equipment::className(), ['id' => 'equipment_id'])->select('*');
    }

$switch_ids = \app\models\EquipmentSwitch::find()->select('id')->where(['equipment_id' => $equipment_id])->asArray()-


>all();
        $ReSwitchTimer = \app\models\SwitchTimer::find()->andWhere(['in','switch_id',array_column($switch_ids,'id')]);

删除

 

try{
        \app\models\EquipmentGroupLink::deleteAll(['group_id' => $group_id]);
        \app\models\UserGroup::findOne(['id' => $group_id])->delete();
        return $this->returnData('', 1, '删除成功');
       }catch (\Exception $e) {
           print $e->getMessage();
           exit();
       }

修改

 

$switch_id = $timer->switch_id;
        \Yii::$app->db->createCommand()->update('ss_switch_timer', ['status' => 0], "switch_id = $switch_id and status = 
1")->execute();

// 修改定时状态
        $timer->status = $status;
        $timer->save();

事务

 

$transaction = Yii::$app->db->beginTransaction();
        try{
            xxxx
        }catch (Exception $e) {
            $transaction->rollBack();
            throw $e;
        }
        $transaction->commit();
        return true;

数据分页

// 分页查询相关
        $page = intval(\Yii::$app->request->post("page", 1));
        $rows = intval(\Yii::$app->request->post("rows", 20));
  $list = $activeRecord->orderBy('updated DESC, id DESC')->offset(($page - 1) * $rows)->limit($rows)->asArray()->all();

定时任务参数

windows
 D:\wamp\www\smartSPACE>yii crontab/copy-db,你需要配置你的php环境变量

linux
*/1 * * * * /usr/local/php/bin/php /home/wwwroot/smartSPACE/yii crontab/copydb

/usr/local/php/bin/php /home/wwwroot/smartSPACE/yii crontab/copy-db

mongodb 新增

$collection = \Yii::$app->mongodb->getCollection('timed_task');
        $collection->insert(['end_time' => $end_time]);

mongodb 查询

$last_result = $query->select(['end_time'])->from('timed_task')->orderBy('_id desc')->limit(1)->one();