cakephp2.X多表联合查询join及使用分页查询的方法

作者:袖梨 2022-06-24

格式化参数:

 代码如下 复制代码

publicfunctiongetconditions($data){

  $this->loadModel("Cm.LoginHistory");

  $conditions=array();

  foreach($dataas$key=>$val){

    if($key=='start_date'){

      $conditions['LoginHistory.logintime >=']=trim($val);

    }elseif($key=='end_date'){

      $conditions['LoginHistory.logouttime <=']=trim($val);

    }elseif($key=='selectvsoftid'and$val!=''){

      $conditions['LoginHistory.LOGINSUBSYSTEM LIKE']='%'.trim($val).'%';

    }elseif($key=='username'and$val!=''){

      $conditions['LoginHistory.USERNAME LIKE']='%'.trim($val).'%';

    }elseif($key=='vdevicename'and$val!=''){

      $conditions['LoginHistory.WINDOWNAME LIKE']='%'.trim($val).'%';

    }elseif($key=='selectvsoftid'and$val!=''){

      $conditions['LoginHistory.PHDEVICENAME LIKE']='%'.trim($val).'%';

    }

  }

  return$conditions;

}

分页查询:

 代码如下 复制代码

$fields=array('LoginHistory.windowname','LoginHistory.loginsubsystem','VirtualDevice.PHDEVICEID','LoginHistory.userid','VirtualDevice.SYSTYPEID','UserInfo.USERNAME','"SUM"(LoginHistory.LOGOUTTIME-LoginHistory.LOGINTIME) moument','UserInfo.USERNAME','DeviceInfo.DEVICENAME');

$group=array('LoginHistory.windowname','LoginHistory.loginsubsystem','LoginHistory.userid','UserInfo.USERNAME','DeviceInfo.DEVICENAME','VirtualDevice.SYSTYPEID','VirtualDevice.PHDEVICEID');

$this->paginate =array('conditions'=>$conditions,'fields'=>$fields,'group'=>$group,'limit'=>9,'joins'=>array(

   array(

     'alias'=>'UserInfo',

     'table'=>'SMM_USERINFO',

     'type'=>'LEFT',

     'conditions'=>' LoginHistory.USERID=UserInfo.USERID ',

   ),

   array(

     'alias'=>'VirtualDevice',

     'table'=>'ET_NMVIRTUALDEVICEINFO',

     'type'=>'LEFT',

     'conditions'=>' LoginHistory.WINDOWNAME=VirtualDevice.DEVICENAME ',

   ),

   array(

     'alias'=>'DeviceInfo',

     'table'=>'ET_NMDEVICEINFO',

     'type'=>'LEFT',

     'conditions'=>' VirtualDevice.PHDEVICEID=DeviceInfo.DEVICEID ',

   ),

 ));

$LoginHistory=$this->paginate('LoginHistory');

$this->set('loginhistory',$LoginHistory);

相关文章

精彩推荐