/** * @param xPDOQuery $c * * @return xPDOQuery */ public function prepareQueryBeforeCount(xPDOQuery $c) { $id = $this->getProperty('id'); if (!empty($id) and $this->getProperty('combo')) { $q = $this->modx->newQuery($this->objectType); $q->where(array('id!=' => $id)); $q->select('id'); $q->limit(11); $q->prepare(); $q->stmt->execute(); $ids = $q->stmt->fetchAll(PDO::FETCH_COLUMN, 0); $ids = array_merge_recursive(array($id), $ids); $c->where(array("{$this->objectType}.id:IN" => $ids)); } $active = $this->getProperty('active'); if ($active != '') { $c->where("{$this->objectType}.active={$active}"); } $query = trim($this->getProperty('query')); if ($query) { $c->where(array("{$this->objectType}.name_ru:LIKE" => "%{$query}%", "OR:{$this->objectType}.name_en:LIKE" => "%{$query}%")); } $c->sortby('active', 'DESC'); $c->sortby('name_ru', 'ASC'); return $c; }
/** * Can be used to adjust the query prior to the COUNT statement * * @param xPDOQuery $c * @return xPDOQuery */ public function prepareQueryBeforeCount(xPDOQuery $c) { $props = $this->getProperties(); $c->distinct(); // from combobox if (isset($props['valuesqry'])) { if ($props['valuesqry'] === 'true') { if (isset($props['query'])) { if (!empty($props['query'])) { $query = urldecode($props['query']); $queries = @explode('||', $query); $i = 0; $where = ''; foreach ($queries as $query) { $where .= ($i !== 0 ? ' OR ' : '') . "`smarttagTags`.`tag` = '{$query}'"; $i++; } $c->where("({$where})"); } } } else { if (isset($props['query']) && !empty($props['query'])) { $c->where(array('tag:LIKE' => "{$props['query']}%", 'OR:tag:LIKE' => "% {$props['query']}%")); } } } else { if (isset($props['query']) && !empty($props['query'])) { $c->where(array('tag:LIKE' => "%{$props['query']}%")); } } $tvId = isset($props['tvId']) && is_numeric($props['tvId']) ? intval($props['tvId']) : ''; // for tagcloud if (strtolower($props['sort']) === 'count') { $c->select(array('smarttagTags.id', 'smarttagTags.tag', 'count' => "(SELECT COUNT(*) FROM {$this->modx->getTableName('smarttagTagresources')} AS Tagresources " . 'WHERE (Tagresources.tag_id = smarttagTags.id ' . (!empty($tvId) ? 'AND Tagresources.tmplvar_id=' . $tvId : '') . '))')); $c->sortby('count', 'desc'); $c->sortby('tag', 'asc'); } if (!empty($tvId)) { $tv = $this->modx->getObject('modTemplateVar', $tvId); if ($tv) { $params = $tv->get('input_properties'); if ($params['globaltags'] != 'true') { $c->leftJoin('smarttagTagresources', 'Tagresources', 'Tagresources.tag_id = smarttagTags.id'); $c->where(array('Tagresources.tmplvar_id' => $tvId)); } } } return $c; }
/** * @param xPDOQuery $c * * @return xPDOQuery */ public function prepareQueryBeforeCount(xPDOQuery $c) { $context = array_map('trim', explode(',', $this->getProperty('context', $this->modx->context->key))); $c->where(array('class_key' => 'TicketsSection', 'published' => 1, 'deleted' => 0, 'context_key:IN' => $context)); $sortby = $this->getProperty('sortby'); $sortdir = $this->getProperty('sortdir'); if ($sortby && $sortdir) { $c->sortby($sortby, $sortdir); } if (!empty($_REQUEST['tid']) && ($tmp = $this->modx->getObject('Ticket', (int) $_REQUEST['tid']))) { $this->current_category = $tmp->get('parent'); } if ($parents = $this->getProperty('parents')) { $depth = $this->getProperty('depth', 0); $parents = array_map('trim', explode(',', $parents)); foreach ($parents as $pid) { $parents = array_merge($parents, $this->modx->getChildIds($pid, $depth)); } if (!empty($parents) && !empty($this->current_category)) { $c->where(array('parent:IN' => $parents, 'OR:id:=' => $this->current_category)); } else { $c->where(array('parent:IN' => $parents)); } } if ($resources = $this->getProperty('resources')) { $resources = array_map('trim', explode(',', $resources)); $c->where(array('id:IN' => $resources)); } return $c; }
public function prepareQueryAfterCount(xPDOQuery $c) { if ($this->getProperty('sort') == 'category') { $c->sortby('parent', $this->getProperty('dir', 'ASC')); } return $c; }
/** * @param xPDOQuery $c * * @return xPDOQuery */ public function prepareQueryBeforeCount(xPDOQuery $c) { if (!$this->getProperty('combo')) { } else { } $active = $this->getProperty('active'); if ($active != '') { $c->where(array('active' => $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; }
/** * Add sort to query */ public function addSort() { $time = microtime(true); $tmp = $this->config['sortby']; if (empty($tmp) || strtolower($tmp) == 'resources' || strtolower($tmp) == 'ids') { $resources = $this->config['class'] . '.' . $this->pk . ':IN'; if (!empty($this->config['where'][$resources])) { $tmp = array('FIELD(`' . $this->config['class'] . '`.`' . $this->pk . '`,\'' . implode('\',\'', $this->config['where'][$resources]) . '\')' => ''); } else { $tmp = array($this->config['class'] . '.' . $this->pk => !empty($this->config['sortdir']) ? $this->config['sortdir'] : 'ASC'); } } else { $tmp = is_string($tmp) && ($tmp[0] == '{' || $tmp[0] == '[') ? $this->modx->fromJSON($this->config['sortby']) : array($this->config['sortby'] => $this->config['sortdir']); } if (!empty($this->config['sortbyTV']) && !array_key_exists($this->config['sortbyTV'], $tmp)) { $tmp2[$this->config['sortbyTV']] = !empty($this->config['sortdirTV']) ? $this->config['sortdirTV'] : (!empty($this->config['sortdir']) ? $this->config['sortdir'] : 'ASC'); $tmp = array_merge($tmp2, $tmp); if (!empty($this->config['sortbyTVType'])) { $tv = strtolower($this->config['sortbyTV']); if (array_key_exists($tv, $this->config['tvsJoin'])) { if (!empty($this->config['tvsJoin'][$tv]['tv'])) { $this->config['tvsJoin'][$tv]['tv']['type'] = $this->config['sortbyTVType']; } } } } $fields = $this->modx->getFields($this->config['class']); $sorts = $this->replaceTVCondition($tmp); if (is_array($sorts)) { while (list($sortby, $sortdir) = each($sorts)) { if (preg_match_all('/TV(.*?)[`|.]/', $sortby, $matches)) { foreach ($matches[1] as $tv) { if (array_key_exists($tv, $this->config['tvsJoin'])) { $params = $this->config['tvsJoin'][$tv]['tv']; switch ($params['type']) { case 'number': case 'decimal': $sortby = preg_replace('/(TV' . $tv . '\\.value|`TV' . $tv . '`\\.`value`)/', 'CAST($1 AS DECIMAL(13,3))', $sortby); break; case 'integer': $sortby = preg_replace('/(TV' . $tv . '\\.value|`TV' . $tv . '`\\.`value`)/', 'CAST($1 AS SIGNED INTEGER)', $sortby); break; case 'date': case 'datetime': $sortby = preg_replace('/(TV' . $tv . '\\.value|`TV' . $tv . '`\\.`value`)/', 'CAST($1 AS DATETIME)', $sortby); break; } } } } elseif (array_key_exists($sortby, $fields)) { $sortby = $this->config['class'] . '.' . $sortby; } $this->query->sortby($sortby, $sortdir); $this->addTime('Sorted by <b>' . $sortby . '</b>, <b>' . $sortdir . '</b>', microtime(true) - $time); $time = microtime(true); } } }
public function prepareQueryBeforeCount(xPDOQuery $c) { $query = $this->getProperty('query'); if (!empty($query)) { $c->where(array('name:LIKE' => '%' . $query . '%', 'OR:alias:LIKE' => '%' . $query . '%')); } $c->sortby('menuindex', 'ASC'); return $c; }
public function prepareQueryAfterCount(xPDOQuery $c) { $c->select($this->modx->getSelectColumns('modUser', 'modUser')); $c->select(array('usergroup' => 'UserGroup.id', 'usergroup_name' => 'UserGroup.name', 'role' => 'UserGroupRole.id', 'role_name' => 'UserGroupRole.name', 'authority' => 'UserGroupRole.authority')); if ($this->getProperty('sort') !== 'authority') { $c->sortby('authority', 'ASC'); } return $c; }
public function prepareQueryBeforeCount(xPDOQuery $c) { $exclude = $this->getProperty('exclude', ''); if (!empty($exclude)) { $c->where(array('id:NOT IN' => is_array($exclude) ? $exclude : explode(',', $exclude))); } $parent = $this->getProperty('parent', ''); if (!empty($parent)) { $c->where(array('parent' => $parent)); } $query = $this->getProperty('query', ''); if (!empty($query)) { $c->where(array('name:LIKE' => '%' . $query . '%', 'OR:description:LIKE' => '%' . $query . '%')); } $c->sortby('parent', 'asc'); $c->sortby('id', 'asc'); return $c; }
public function prepareQueryBeforeCount(xPDOQuery $c) { $query = $this->getProperty('query'); if (!empty($query)) { $c->where(array('pagetitle:LIKE' => '%' . $query . '%')); } $c->where(array('deleted' => false, 'published' => true)); $c->sortby('pagetitle', 'ASC'); return $c; }
protected function getCount(xPDOQuery &$c) { $query = clone $c; $query = $this->prepareCountQuery($query); if (!($this->total = $this->countTotal($this->classKey, $query))) { return false; } # $sortKey = 'pagetitle'; if ($sortKey = $this->getProperty('sort')) { $dir = $this->getProperty('dir'); $c->sortby($sortKey, $dir); $query->sortby($sortKey, $dir); } else { $sortKey = 'show_in_tree'; $dir = 'DESC'; $c->sortby($sortKey, $dir); $query->sortby($sortKey, $dir); $sortKey = 'isfolder'; $dir = 'DESC'; $c->sortby($sortKey, $dir); $query->sortby($sortKey, $dir); } $limit = intval($this->getProperty('limit')); $start = intval($this->getProperty('start')); if ($limit > 0) { $query->limit($limit, $start); } $query = $this->prepareUniqObjectsQuery($query); if ($query->prepare() && $query->stmt->execute() && ($rows = $row = $query->stmt->fetchAll(PDO::FETCH_ASSOC))) { $IDs = array(); foreach ($rows as $row) { $IDs[] = $row['id']; } if ($this->flushWhere && isset($c->query['where'])) { $c->query['where'] = array(); } $c->where(array("{$this->classKey}.id:IN" => $IDs)); } else { return false; } return $c; }
/** * @return mixed */ public function prepareQueryBeforeCount(xPDOQuery $c) { $rid = trim($this->getProperty('resource')); $c->setClassAlias('Permissions'); $c->leftJoin('modUserProfile', 'User', 'Permissions.principal = User.internalKey AND Permissions.principal_type = "usr"'); $c->leftJoin('modUserGroup', 'Group', 'Permissions.principal = Group.id AND Permissions.principal_type = "grp"'); $c->select('Permissions.*, User.fullname, Group.name as groupname'); $c->where(array('Permissions.rid' => $rid)); $c->sortby('Permissions.weight', 'ASC'); //$c->sortby('Permissions.priority', 'DESC'); return $c; }
/** * @param xPDOQuery $c * * @return xPDOQuery */ public function prepareQueryBeforeCount(xPDOQuery $c) { if (!$this->getProperty('combo')) { } else { } $c->leftJoin('glRegion', 'glRegion', 'glRegion.id = glCity.region_id'); $c->select($this->modx->getSelectColumns('glCity', 'glCity')); $c->select(array('region_name_ru' => 'glRegion.name_ru')); $active = $this->getProperty('active'); if ($active != '') { $c->where(array('glCity.active' => $active)); } $regionId = $this->getProperty('region_id'); if ($regionId != '') { $c->where(array('glCity.region_id' => $regionId)); } $query = trim($this->getProperty('query')); if ($query) { $c->where(array('glCity.name_ru:LIKE' => "%{$query}%", 'glCity.OR:name_en:LIKE' => "%{$query}%")); } $c->sortby('glCity.active', 'DESC'); $c->sortby('glCity.name_ru', 'ASC'); return $c; }
protected function getCount(xPDOQuery &$c) { if (!($sortKey = $this->getProperty('sort'))) { $sortClassKey = $this->getSortClassKey(); $sortKey = $this->modx->getSelectColumns($sortClassKey, $this->getProperty('sortAlias', $sortClassKey), '', array($this->getProperty('sort'))); } $query = clone $c; $query = $this->prepareCountQuery($query); if (!($this->total = $this->countTotal($this->classKey, $query))) { return false; } if ($sortKey) { $c->sortby($sortKey, $this->getProperty('dir')); $query->sortby($sortKey, $this->getProperty('dir')); } $limit = intval($this->getProperty('limit')); $start = intval($this->getProperty('start')); if ($limit > 0) { $query->limit($limit, $start); } $query = $this->prepareUniqObjectsQuery($query); if ($query->prepare() && $query->stmt->execute() && ($rows = $row = $query->stmt->fetchAll(PDO::FETCH_ASSOC))) { $IDs = array(); foreach ($rows as $row) { $IDs[] = $row['id']; } // print $query->toSQL(); // print_r($IDs); // exit; if ($this->flushWhere && isset($c->query['where'])) { $c->query['where'] = array(); } $c->where(array("{$this->classKey}.id:IN" => $IDs)); } else { return false; } return $c; }
public function prepareQueryAfterCount(xPDOQuery $c) { $c->sortby($this->modx->getSelectColumns('modAction', 'modAction', '', array('namespace')), 'ASC'); return $c; }
protected function permanentSort(xPDOQuery $c, $gridSort, $sortOptions) { $sorts = explode(',', $sortOptions); foreach ($sorts as $sort) { $sort = explode('=', $sort); if (isset($sort[1])) { if ($sort[0] != '*' && strtolower($sort[0]) != strtolower($gridSort)) { continue; } } $options = isset($sort[1]) ? $sort[1] : $sort[0]; $options = explode(':', $options); if (empty($options[0])) { continue; } $options['field'] = $options[0]; $options['dir'] = empty($options[1]) ? $this->getProperty('dir') : $options[1]; $options['type'] = empty($options[2]) ? null : $options[2]; if (empty($options['type'])) { $c->sortby('`' . $options['field'] . '`', $options['dir']); } else { $c->sortby('CAST(`' . $options['field'] . '` as ' . $options['type'] . ')', $options['dir']); } } return $c; }
/** * @param xPDOQuery $c * * @return xPDOQuery */ protected function _sortResources(xPDOQuery $c) { $parent_id = $this->getProperty('parent', 0); $sort = $this->classKey . '.menuindex'; $dir = 'ASC'; if ($parent_id && ($parent = $this->modx->getObject($this->classKey, $parent_id))) { $class_key = $parent->get('class_key'); if ($class_key == 'TicketsSection') { $sort = $this->classKey . '.createdon'; $dir = 'DESC'; } } $c->sortby($sort, $dir); return $c; }