PHP xPDOQuery::sortby方法代码示例

 /**
  * @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;
 }

 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;
 }

 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;
 }