PHP xPDOQuery::groupby方法代码示例

 /**
  * @param xPDOQuery $c
  *
  * @return xPDOQuery
  */
 public function prepareQueryBeforeCount(xPDOQuery $c)
 {
     $c->leftJoin('modMediaSource', 'Source');
     $c->leftJoin($this->classKey, 'Thumb', "`{$this->classKey}`.`id` = `Thumb`.`parent`");
     $c->groupby($this->classKey . '.id');
     $c->select('`Source`.`name` as `source_name`');
     $c->select('`Thumb`.`url` as `thumbnail`');
     $c->where(array('resource_id' => $this->getProperty('resource_id')));
     $parent = $this->getProperty('parent');
     if ($parent !== false) {
         $c->where(array('parent' => $parent));
     }
     $query = trim($this->getProperty('query'));
     if (!empty($query)) {
         $c->where(array('file:LIKE' => "%{$query}%", 'OR:name:LIKE' => "%{$query}%", 'OR:alt:LIKE' => "%{$query}%", 'OR:description:LIKE' => "%{$query}%", 'OR:add:LIKE' => "%{$query}%"));
     }
     $tags = array_map('trim', explode(',', $this->getProperty('tags')));
     if (!empty($tags[0])) {
         $tags = implode("','", $tags);
         $c->innerJoin('msResourceFileTag', 'Tag', "`{$this->classKey}`.`id` = `Tag`.`file_id` AND `Tag`.`tag` IN ('" . $tags . "')");
         $c->groupby($this->classKey . '.id');
         $c->prepare();
         $this->modx->log(1, $c->toSQL());
     }
     return $c;
 }

 public function prepareQueryBeforeCount(xPDOQuery $c)
 {
     $c->leftJoin($this->classKey, 'Children');
     $c->where(array($this->classKey . '.parent' => $this->getProperty('id')));
     $c->groupby($this->classKey . '.text');
     return parent::prepareQueryBeforeCount($c);
 }

 /** {@inheritDoc} */
 public function prepareQueryBeforeCount(xPDOQuery $c)
 {
     if ($this->getProperty('combo')) {
         $c->limit(0);
         $c->groupby('level');
     }
     return $c;
 }

 /**
  * Group query by given field
  */
 public function addGrouping()
 {
     if (!empty($this->config['groupby'])) {
         $time = microtime(true);
         $groupby = $this->config['groupby'];
         $this->query->groupby($groupby);
         $this->addTime('Grouped by <b>' . $groupby . '</b>', microtime(true) - $time);
     }
 }

 /**
  * @param xPDOQuery $c
  *
  * @return xPDOQuery
  */
 public function prepareQueryBeforeCount(xPDOQuery $c)
 {
     $c->where(array('referrer_id' => $this->getProperty('referrer_id')));
     $c->groupby($this->classKey . '.id');
     $c->select($this->modx->getSelectColumns($this->classKey, $this->classKey));
     $c->innerJoin('modUser', 'User', $this->classKey . '.id = User.id');
     $c->select('User.username');
     $c->innerJoin('modUserProfile', 'UserProfile', $this->classKey . '.id = UserProfile.internalKey');
     $c->select('UserProfile.fullname');
     return $c;
 }

 /**
  * @param xPDOQuery $c
  *
  * @return xPDOQuery
  */
 public function prepareQueryBeforeCount(xPDOQuery $c)
 {
     $c->leftJoin('modMediaSource', 'Source');
     $c->leftJoin($this->classKey, 'Thumb', "`{$this->classKey}`.`id` = `Thumb`.`parent`");
     $c->groupby($this->classKey . '.id');
     $c->select('`Source`.`name` as `source_name`');
     $c->select('`Thumb`.`url` as `thumbnail`');
     $c->where(array('resource_id' => $this->getProperty('resource_id')));
     $parent = $this->getProperty('parent');
     if ($parent !== false) {
         $c->where(array('parent' => $parent));
     }
     return $c;
 }

 /**
  * @param xPDOQuery $c
  *
  * @return xPDOQuery
  */
 public function prepareQueryBeforeCount(xPDOQuery $c)
 {
     $c->leftJoin('modTemplate', 'Template');
     $c->leftJoin('sxSubscriber', 'Subscribers');
     $c->select($this->modx->getSelectColumns($this->classKey, $this->classKey));
     $c->select($this->modx->getSelectColumns('modTemplate', 'Template', '', array('templatename')));
     $c->select('COUNT(`Subscribers`.`id`) as `subscribers`');
     $c->groupby($this->classKey . '.id');
     if ($query = $this->getProperty('query')) {
         $c->where(array('name:LIKE' => "%{$query}%", 'OR:description:LIKE' => "%{$query}%"));
     }
     if ($this->getProperty('combo')) {
         $c->where(array('active' => 1));
     }
     return $c;
 }

 /**
  * @param xPDOQuery $c
  *
  * @return xPDOQuery
  */
 public function prepareQueryBeforeCount(xPDOQuery $c)
 {
     $c->groupby('glData.id');
     $c->leftJoin('glCountry', 'glCountry', 'glCountry.id = glData.identifier');
     $c->leftJoin('glRegion', 'glRegion', 'glRegion.id = glData.identifier');
     $c->leftJoin('glCity', 'glCity', 'glCity.id = glData.identifier');
     $c->select($this->modx->getSelectColumns('glData', 'glData'));
     $c->select(array('name' => 'glCountry.name_ru', 'name1' => 'glRegion.name_ru', 'name2' => 'glCity.name_ru', 'active' => 'glCountry.active', 'active1' => 'glRegion.active', 'active2' => 'glCity.active'));
     $query = trim($this->getProperty('query'));
     if ($query) {
         $c->where(array('name_ru:LIKE' => "%{$query}%", 'OR:name_en:LIKE' => "%{$query}%"));
     }
     //		$c->sortby('active', 'DESC');
     //		$c->sortby('name_ru', 'ASC');
     return $c;
 }

 /**
  * @param xPDOQuery $c
  *
  * @return xPDOQuery
  */
 public function prepareQueryBeforeCount(xPDOQuery $c)
 {
     /* Get all comments by section */
     if ($section = (int) $this->getProperty('section')) {
         if ($section = $this->modx->getObject('modResource', $section)) {
             $parents = $this->modx->getChildIds($section->get('id'), 1, array('context' => $section->get('context_key')));
             if (empty($parents)) {
                 $parents = array('0');
             }
             $c->where(array('Thread.resource:IN' => $parents));
         }
     } elseif ($threads = $this->getProperty('threads')) {
         if (!is_array($threads)) {
             $threads = explode(',', $threads);
         }
         if (!empty($threads)) {
             $c->where(array('TicketComment.thread:IN' => $threads));
         }
     } elseif ($parents = $this->getProperty('parents')) {
         if (!is_array($parents)) {
             $parents = explode(',', $parents);
         }
         if (!empty($parents)) {
             $c->where(array('Thread.resource:IN' => $parents));
         }
     } else {
         //$c->where(array('Thread.resource:!=' => 0));
     }
     if ($query = $this->getProperty('query', null)) {
         $query = trim($query);
         if (is_numeric($query)) {
             $c->where(array('TicketComment.id:=' => $query, 'OR:TicketComment.parent:=' => $query));
         } else {
             $c->where(array('TicketComment.text:LIKE' => '%' . $query . '%', 'OR:TicketComment.name:LIKE' => '%' . $query . '%', 'OR:TicketComment.email:LIKE' => '%' . $query . '%'));
         }
     }
     $c->leftJoin('TicketThread', 'Thread');
     $c->leftJoin('modUser', 'User');
     $c->leftJoin('modUserProfile', 'UserProfile');
     $c->leftJoin('modResource', 'Resource', 'Thread.resource = Resource.id');
     $c->select($this->modx->getSelectColumns('TicketComment', 'TicketComment'));
     $c->select(array('Thread.resource', 'Thread.properties', 'thread_name' => 'Thread.name', 'User.username', 'UserProfile.fullname', 'Resource.pagetitle', 'Resource.context_key'));
     $c->groupby('TicketComment.id');
     return $c;
 }

 /** {@inheritDoc} */
 public function prepareQueryBeforeCount(xPDOQuery $c)
 {
     if (!$this->getProperty('combo')) {
         $c->groupby('MlmSystemClient.id');
         $c->leftJoin('modUser', 'modUser', 'modUser.id = MlmSystemClient.id');
         $c->leftJoin('modUserProfile', 'modUserProfile', 'modUserProfile.internalKey = MlmSystemClient.id');
         $c->leftJoin('modUser', 'modUserParent', 'modUserParent.id = MlmSystemClient.parent');
         $c->leftJoin('modUserProfile', 'modUserProfileParent', 'modUserProfileParent.internalKey = MlmSystemClient.parent');
         $c->leftJoin('MlmSystemStatus', 'MlmSystemStatus', 'MlmSystemStatus.id = MlmSystemClient.status');
         $c->leftJoin('MlmSystemPath', 'MlmSystemPath', 'MlmSystemPath.id = MlmSystemClient.id');
         $c->leftJoin('MlmSystemClient', 'MlmSystemClientParent', 'MlmSystemClientParent.parent = MlmSystemClient.id');
         $c->select($this->modx->getSelectColumns('MlmSystemClient', 'MlmSystemClient'));
         $c->select($this->modx->getSelectColumns('modUserProfile', 'modUserProfile', 'profile_', array('id', 'internalKey'), true));
         $c->select(array('username' => 'modUser.username', 'fullname' => 'modUserProfile.fullname', 'email' => 'modUserProfile.email', 'parent_username' => 'modUserParent.username', 'parent_fullname' => 'modUserProfileParent.fullname', 'parent_email' => 'modUserProfileParent.email', 'status_name' => 'MlmSystemStatus.name', 'status_color' => 'MlmSystemStatus.color', 'level' => 'MlmSystemPath.level', 'children' => 'COUNT(MlmSystemClientParent.parent)'));
     } else {
         $c->leftJoin('modUser', 'modUser', 'modUser.id = MlmSystemClient.id');
         $c->leftJoin('modUserProfile', 'modUserProfile', 'modUserProfile.internalKey = MlmSystemClient.id');
         $c->select($this->modx->getSelectColumns('MlmSystemClient', 'MlmSystemClient'));
         $c->select($this->modx->getSelectColumns('modUserProfile', 'modUserProfile', 'profile_', array('id', 'internalKey'), true));
         $c->select(array('username' => 'modUser.username', 'fullname' => 'modUserProfile.fullname'));
         $c->where(array('status:NOT IN' => array(4)));
         $client = $this->getProperty('client', 0);
         if (!empty($client)) {
             $c->where(array('id:!=' => $client));
         }
     }
     $status = $this->getProperty('status');
     if (!empty($status)) {
         $c->where(array('status' => $status));
     }
     $level = $this->getProperty('level');
     if (!empty($level)) {
         $c->where(array('MlmSystemPath.level' => $level));
     }
     $leader = $this->getProperty('leader');
     if ($leader != '') {
         $c->where(array('leader' => $leader));
     }
     // query
     if ($query = $this->getProperty('query')) {
         $c->where(array('modUser.username:LIKE' => '%' . $query . '%', 'OR:modUserProfile.fullname:LIKE' => '%' . $query . '%', 'OR:modUserProfile.email:LIKE' => '%' . $query . '%'));
     }
     return $c;
 }

 protected function prepareGroupCondition(xPDOQuery $c)
 {
     $c->groupby('sm_price');
     $c->groupby('sm_trade_price');
     return $c;
 }

 public function prepareQueryAfterCount(xPDOQuery $c)
 {
     $c->groupby($this->defaultSortField, $this->defaultSortDirection);
     return $c;
 }

 /** {@inheritDoc} */
 public function prepareQueryAfterCount(xPDOQuery $c)
 {
     $c->groupby($this->classKey . '.id');
     return $c;
 }

 public function prepareQueryBeforeCount(xPDOQuery $c)
 {
     $c->groupby('form');
     return $c;
 }

 public function prepareQueryAfterCount(xPDOQuery $c)
 {
     $c->select(array('modAccessPermission.id', 'modAccessPermission.name', 'modAccessPermission.description', 'Template.lexicon'));
     $c->groupby('modAccessPermission.name');
     return $c;
 }