/** * @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; }
/** * @param xPDOQuery $c * * @return xPDOQuery */ public function prepareQueryBeforeCount(xPDOQuery $c) { $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'); $c->leftJoin('modUser', 'Referrer', $this->classKey . '.referrer_id = Referrer.id'); $c->select('Referrer.username as referrer_username'); $c->leftJoin('modUserProfile', 'ReferrerProfile', $this->classKey . '.referrer_id = ReferrerProfile.internalKey'); $c->select('ReferrerProfile.fullname as referrer_fullname'); $c->leftJoin($this->classKey, 'Referrals', $this->classKey . '.id = Referrals.referrer_id'); $c->select('COUNT(Referrals.id) as referrals'); if ($query = $this->getProperty('query')) { $c->where(array('User.username:LIKE' => "%{$query}%", 'OR:UserProfile.fullname:LIKE' => "%{$query}%")); } return $c; }
/** * @param xPDOQuery $c * * @return xPDOQuery */ public function prepareQueryBeforeCount(xPDOQuery $c) { $c->leftJoin('Ticket', 'Ticket'); $c->select($this->modx->getSelectColumns('TicketThread', 'TicketThread')); $c->select(array('Ticket.pagetitle')); if (!$this->getProperty('combo')) { $c->leftJoin('TicketComment', 'Comments'); $c->select(array('comments' => 'COUNT(Comments.id)')); $c->groupby('TicketThread.id'); } if ($query = $this->getProperty('query', null)) { $query = trim($query); if (is_numeric($query)) { $c->where(array('TicketThread.id:=' => $query, 'OR:TicketThread.resource:=' => $query)); } else { $c->where(array('Ticket.pagetitle:LIKE' => "%{$query}%", 'OR:TicketThread.name: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; }