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();

大海技术博客
请先登录后发表评论
  • latest comments
  • 总共0条评论