Home  >  Article  >  Backend Development  >  cakephp2.X multi-table joint query join and method of using paging query

cakephp2.X multi-table joint query join and method of using paging query

高洛峰
高洛峰Original
2017-02-25 16:28:031218browse

The example of this article describes the cakephp2.X multi-table joint query join and the method of using paging query. Share it with everyone for your reference, the details are as follows:

Format parameters:

public function getconditions($data){
  $this->loadModel("Cm.LoginHistory");
  $conditions = array();
  foreach ($data as $key=>$val){
    if($key=='start_date'){
      $conditions['LoginHistory.logintime >=']=trim($val);
    }elseif ($key=='end_date'){
      $conditions[&#39;LoginHistory.logouttime <=&#39;]=trim($val);
    }elseif ($key==&#39;selectvsoftid&#39; and $val !=&#39;&#39;){
      $conditions[&#39;LoginHistory.LOGINSUBSYSTEM LIKE&#39;]=&#39;%&#39;.trim($val).&#39;%&#39;;
    }elseif ($key==&#39;username&#39; and $val !=&#39;&#39;){
      $conditions[&#39;LoginHistory.USERNAME LIKE&#39;]=&#39;%&#39;.trim($val).&#39;%&#39;;
    }elseif ($key==&#39;vdevicename&#39; and $val !=&#39;&#39;){
      $conditions[&#39;LoginHistory.WINDOWNAME LIKE&#39;]=&#39;%&#39;.trim($val).&#39;%&#39;;
    }elseif ($key==&#39;selectvsoftid&#39; and $val !=&#39;&#39;){
      $conditions[&#39;LoginHistory.PHDEVICENAME LIKE&#39;]=&#39;%&#39;.trim($val).&#39;%&#39;;
    }
  }
  return $conditions;
}

Paging query:

$fields = array(&#39;LoginHistory.windowname&#39;,&#39;LoginHistory.loginsubsystem&#39;,&#39;VirtualDevice.PHDEVICEID&#39;,&#39;LoginHistory.userid&#39;, &#39;VirtualDevice.SYSTYPEID&#39;,&#39;UserInfo.USERNAME&#39;,&#39;"SUM"(LoginHistory.LOGOUTTIME-LoginHistory.LOGINTIME) moument&#39;,&#39;UserInfo.USERNAME&#39;,&#39;DeviceInfo.DEVICENAME&#39;);
$group=array(&#39;LoginHistory.windowname&#39;, &#39;LoginHistory.loginsubsystem&#39;, &#39;LoginHistory.userid&#39;,&#39;UserInfo.USERNAME&#39;,&#39;DeviceInfo.DEVICENAME&#39;,&#39;VirtualDevice.SYSTYPEID&#39;,&#39;VirtualDevice.PHDEVICEID&#39;);
$this->paginate = array(&#39;conditions&#39;=>$conditions,&#39;fields&#39;=>$fields,&#39;group&#39;=>$group,&#39;limit&#39;=>9,&#39;joins&#39; => array(
   array(
     &#39;alias&#39; => &#39;UserInfo&#39;,
     &#39;table&#39; => &#39;SMM_USERINFO&#39;,
     &#39;type&#39; => &#39;LEFT&#39;,
     &#39;conditions&#39; => &#39; LoginHistory.USERID=UserInfo.USERID &#39;,
   ),
   array(
     &#39;alias&#39; => &#39;VirtualDevice&#39;,
     &#39;table&#39; => &#39;ET_NMVIRTUALDEVICEINFO&#39;,
     &#39;type&#39; => &#39;LEFT&#39;,
     &#39;conditions&#39; => &#39; LoginHistory.WINDOWNAME=VirtualDevice.DEVICENAME &#39;,
   ),
   array(
     &#39;alias&#39; => &#39;DeviceInfo&#39;,
     &#39;table&#39; => &#39;ET_NMDEVICEINFO&#39;,
     &#39;type&#39; => &#39;LEFT&#39;,
     &#39;conditions&#39; => &#39; VirtualDevice.PHDEVICEID=DeviceInfo.DEVICEID &#39;,
   ),
 ));
$LoginHistory = $this->paginate(&#39;LoginHistory&#39;);
$this->set(&#39;loginhistory&#39;,$LoginHistory);

For more cakephp2.X multi-table joint query join and paging query related articles, please pay attention to the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn