PHP Zend_Db_Select::order方法代碼示例

 function getById($jobid)
 {
     // do Bacula ACLs
     Zend_Loader::loadClass('Job');
     $table = new Job();
     if (!$table->isJobIdExists($jobid)) {
         return FALSE;
     }
     $select = new Zend_Db_Select($this->db);
     switch ($this->db_adapter) {
         case 'PDO_SQLITE':
             // bug http://framework.zend.com/issues/browse/ZF-884
             $select->distinct();
             $select->from(array('l' => 'Log'), array('logid' => 'LogId', 'jobid' => 'JobId', 'LogTime' => 'Time', 'logtext' => 'LogText'));
             $select->where("JobId = ?", $jobid);
             $select->order(array('LogId', 'LogTime'));
             break;
         default:
             // mysql, postgresql
             $select->distinct();
             $select->from(array('l' => 'Log'), array('LogId', 'JobId', 'LogTime' => 'Time', 'LogText'));
             $select->where("JobId = ?", $jobid);
             $select->order(array('LogId', 'LogTime'));
     }
     //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
     $stmt = $select->query();
     return $stmt->fetchAll();
 }

 /**
  * sortiert nach Spaltenname der durch den Paginator dargestellten
  * Tabelle
  *
  * @param string $columnName
  */
 public function orderBy($columnName)
 {
     if ($this->isRawcolumn($columnName)) {
         $this->select->order($columnName);
     }
     //ENDE: if()
 }

 /**
  * Render sql select orders
  *
  * @return  Varien_Data_Collection_Db
  */
 protected function _renderOrders()
 {
     foreach ($this->_orders as $orderExpr) {
         $this->_select->order($orderExpr);
     }
     return $this;
 }

 /**
  * Render sql select orders
  *
  * @return  Varien_Data_Collection_Db
  */
 protected function _renderOrders()
 {
     $ordersInSelect = $this->_select->getPart(Zend_Db_Select::ORDER);
     foreach ($this->_orders as $orderExpr) {
         if (!in_array($orderExpr, $ordersInSelect)) {
             $this->_select->order($orderExpr);
         }
     }
     return $this;
 }

 /**
  * Render sql select orders
  *
  * @return  Varien_Data_Collection_Db
  */
 protected function _renderOrders()
 {
     if (!$this->_isOrdersRendered) {
         foreach ($this->_orders as $field => $direction) {
             $this->_select->order(new Zend_Db_Expr($field . ' ' . $direction));
         }
         $this->_isOrdersRendered = true;
     }
     return $this;
 }

 /**
  * @return array $relatedEntries
  */
 private function _getRelatedCompaniesLinks()
 {
     if (!is_null($this->_relatedCache)) {
         return $this->_relatedCache;
     }
     $itemRow = $this->getData()->parent->row;
     $categoryDirectory = $this->_getCategoryDirectory();
     $connectTableName = Kwc_Abstract::getSetting($categoryDirectory->componentClass, 'categoryToItemModelName');
     $linkLimit = $this->_getSetting('linkLimit');
     $categoryReference = Kwc_Directories_Category_Detail_List_Component::getTableReferenceData($connectTableName, 'Category');
     $categoryIds = array();
     $categoryIdsRowset = $itemRow->getRow()->findDependentRowset($connectTableName);
     foreach ($categoryIdsRowset as $categoryIdRow) {
         $categoryIds[] = $categoryIdRow->{$categoryReference['itemColumn']};
     }
     if ($categoryIds) {
         $itemReference = Kwc_Directories_Category_Detail_List_Component::getTableReferenceData($connectTableName, 'Item');
         // anzahl holen
         $s = new Zend_Db_Select(Kwf_Registry::get('db'));
         $s->from($itemReference['tableName'], $itemReference['itemColumn']);
         $s->where($categoryReference['itemColumn'] . ' IN(' . implode(',', $categoryIds) . ')');
         $s->order($itemReference['tableName'] . '.id ASC');
         $rowset = $s->query()->fetchAll();
         // eigene position ermitteln und rauslöschen
         $selfPos = 0;
         $i = 0;
         foreach ($rowset as $k => $row) {
             if ($row[$itemReference['itemColumn']] == $itemRow->id) {
                 if (!$selfPos) {
                     $selfPos = $i;
                 }
                 unset($rowset[$k]);
             }
             $i++;
         }
         $rowset = array_values($rowset);
         $categoryRowCount = count($rowset);
         $itemIds = $ret = array();
         if ($categoryRowCount) {
             for ($i = 0; $i < $linkLimit; $i++) {
                 $offset = ($selfPos + floor($categoryRowCount / $linkLimit) * $i) % $categoryRowCount;
                 $itemIds[] = $rowset[$offset][$itemReference['itemColumn']];
             }
             $itemDirectory = $this->getData()->parent->parent;
             $select = $itemDirectory->getGenerator('detail')->select($itemDirectory);
             $select->where('id IN(' . implode(',', $itemIds) . ')');
             $ret = $itemDirectory->getChildComponents($select);
         }
         $this->_relatedCache = $ret;
         return $ret;
     }
     $this->_relatedCache = array();
     return array();
 }

 public function fetchAllUsers($order = 'id')
 {
     $select = new Zend_Db_Select($this->db);
     $select->from(array('user1' => 'webacula_users'), array('id', 'login', 'name', 'email', 'create_login', 'last_login', 'last_ip', 'active', 'role_id'));
     $select->joinLeft(array('role1' => 'webacula_roles'), 'user1.role_id = role1.id', array('role_name' => 'name', 'role_id' => 'id'));
     if ($order) {
         $select->order(array($order . ' ASC'));
     }
     //$sql = $select->__toString(); var_dump($sql); exit; // for !!!debug!!!
     $stmt = $select->query();
     $result = $stmt->fetchAll();
     return $result;
 }

 public function fetchAllRoles()
 {
     /*
        SELECT roles.id, roles.name, inherits.name AS inherit_name
        FROM webacula_roles AS roles
        LEFT JOIN webacula_roles AS inherits ON inherits.id = roles.inherit_id
        ORDER BY roles.inherit_id, roles.order_role ASC
     */
     $select = new Zend_Db_Select($this->db);
     $select->from(array('roles' => 'webacula_roles'), array('id', 'name', 'description', 'order_role', 'inherit_id'));
     $select->joinLeft(array('inherits' => 'webacula_roles'), 'inherits.id = roles.inherit_id', array('inherit_name' => 'name'));
     $select->order(array('roles.order_role, roles.id ASC'));
     //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
     $stmt = $select->query();
     return $stmt->fetchAll();
 }

 /**
  * Builds query order
  *
  * @param string $field Field name
  * @param string $order Query Sort Order
  * @param bool   $reset If we should reset the current order
  *
  * @return Bvb_Grid_Source_Zend_Select
  */
 public function buildQueryOrder($field, $order, $reset = false)
 {
     if (!array_key_exists($field, $this->_fields)) {
         return $this;
     }
     foreach ($this->_select->getPart(Zend_Db_Select::COLUMNS) as $col) {
         if ($col[0] . '.' . $col[2] == $field && is_object($col[1])) {
             $field = $col[2];
         }
     }
     if ($reset === true) {
         $this->_select->reset('order');
     }
     $this->_select->order($field . ' ' . $order);
     return $this;
 }

 public function search($word = null, $limit = null)
 {
     $db = Zend_Registry::get('db');
     $sql = new Zend_Db_Select($db);
     $select = $sql->from('albums');
     if (!is_null($word)) {
         $select = $sql->where($db->quoteInto('title LIKE ? OR artist LIKE ?', "%" . $word . "%"));
     }
     if ($limit > 0) {
         $select = $sql->limit($limit);
     }
     $select = $sql->order('artist DESC');
     $results = $db->query($select);
     $rows = $results->fetchAll();
     return $rows;
 }

 public function getSelectFilesByJobId($jobid)
 {
     // do Bacula ACLs
     Zend_Loader::loadClass('Job');
     $table = new Job();
     if (!$table->isJobIdExists($jobid)) {
         return FALSE;
     }
     // !!! IMPORTANT !!! с Zend Paginator нельзя использовать DISTINCT иначе не работает в PDO_PGSQL
     $select = new Zend_Db_Select($this->db);
     $select->from(array('f' => 'File'), array('FileId', 'FileIndex', 'LStat'));
     $select->joinLeft(array('p' => 'Path'), 'f.PathId = p.PathId', array('Path'));
     $select->joinLeft(array('n' => 'Filename'), 'f.FileNameId = n.FileNameId', array('Name'));
     $select->where("f.JobId = ?", $jobid);
     $select->order(array('f.FileIndex', 'f.FileId'));
     return $select;
 }

 /**
  * @param number $filterState            
  * @param string $userIds            
  * @throws Zend_Exception
  */
 public function selectByUsersIds($filterState = 1, $ids, $like = NULL)
 {
     try {
         $select = new Zend_Db_Select($this->db);
         $select->from('' . $this->name . '');
         $select->where('state=?', $filterState);
         $select->where('created_user_id IN ( ' . $ids . ' )');
         if (!is_null($like)) {
             $columns = array('cpf', 'dados_cliente');
             $select->where($columns[0] . ' LIKE ?', '%' . $like . '%');
             $select->orWhere($columns[1] . ' LIKE ?', '%' . $like . '%');
         }
         $select->order('id DESC');
         return $select;
     } catch (Zend_Exception $e) {
         throw new Zend_Exception($e->getMessage());
         return false;
     }
 }

 /**
  * Retorna uma query com informações do empreendimentos cadastrados
  * 
  * @param number $filter            
  * @throws Zend_Db_Exception
  * @return mixed|boolean
  */
 public function selectQueryList($filter = 1, $like = NULL)
 {
     try {
         $select = new Zend_Db_Select($this->db);
         $select->from($this->name);
         $select->where('state=?', $filter);
         if (!is_null($like)) {
             $columns = array('nome', 'categoria', 'logradouro', 'incorporadora');
             $select->where($columns[0] . ' LIKE ?', '%' . $like . '%');
             $select->orWhere($columns[1] . ' LIKE ?', '%' . $like . '%');
             $select->orWhere($columns[2] . ' LIKE ?', '%' . $like . '%');
             $select->orWhere($columns[3] . ' LIKE ?', '%' . $like . '%');
         }
         $select->order('id DESC');
         return $select;
     } catch (Zend_Db_Exception $e) {
         throw new Zend_Db_Exception($e->getMessage());
         return false;
     }
 }

 /**
  * Enter description here ...
  * @param Zend_Db_Select $select
  * @param array $params
  * @return Zend_Db_Select | null
  * @author Tung Ly
  */
 public function createSQL($select, $params)
 {
     $andWhere = array();
     $orWhere = array();
     foreach ($params as $field => $value) {
         if ($field === 'fieldsearch') {
             $andWhere[] = str_replace('{{param}}', addslashes(trim($params['keywords'])), $this->_search[$value]);
         } elseif ($field !== 'keywords' && $value !== 'none') {
             if (isset($this->_search[$field])) {
                 $orWhere[] = str_replace('{{param}}', $params[$field], $this->_search[$field]);
             } elseif (isset($this->_sort[$field])) {
                 $select->order(str_replace('{{param}}', $params[$field], $this->_sort[$field]));
             }
         }
     }
     if (count($andWhere)) {
         $select->where(implode(' AND ', $andWhere));
     }
     if (count($orWhere)) {
         $select->where(implode(' OR ', $orWhere));
     }
     //echo '<pre>'; print_r($params); echo $select;
     return $select;
 }

 /**
  * Get Select object (Zend_Db_Select) for sorting table records
  *
  * @param Zend_Db_Select $select
  * @param string $order         
  *
  * @return Zend_Db_Select
  */
 public static function GetSelectForSort($select, $options)
 {
     $aliasTable = '';
     $order = $options['order'];
     //--------------------------
     $arrOrder = explode('.', $order);
     // Если в параметре сортировки не задан псевдоним таблицы
     // то определим его, и если нужно присоединим,
     // соответствующую таблицу
     if (count($arrOrder) == 1) {
         $joinTableForSort = $options['joinTableForSort'];
         if ($joinTableForSort) {
             // Определим какие таблицы уже присоединены
             $fromTables = $select->getPart(Zend_Db_Select::FROM);
             foreach ($fromTables as $alias => $joinParams) {
                 // Если таблица -> $joinTableForSort уже присоединена
                 // то получим ее псевдоним
                 if ($joinParams['tableName'] == $joinTableForSort) {
                     $aliasTable = $alias;
                 }
             }
             if ($aliasTable) {
                 $order = $aliasTable . '.' . $order;
             } else {
                 // Получим поле сортировки
                 $arrOrder = explode(' ', trim($order));
                 $field = $arrOrder[0];
                 // Присоединим таблицу
                 $joinTable = $joinTableForSort;
                 switch ($joinTable) {
                     case 'blog_posts_profile':
                         $select->joinInner(array('p_profile' => $joinTable), 'p_profile.post_id = p.id', array())->where('p_profile.profile_key = ?', $field);
                         $order = 'p_profile.profile_value ' . $arrOrder[1];
                         break;
                     case 'blog_posts_images':
                         $select->joinInner(array('img' => $joinTable), 'img.post_id = p.id', array());
                         $order = 'img.' . $field . ' ' . $arrOrder[1];
                         break;
                     case 'blog_posts_audio':
                         $select->joinInner(array('au' => $joinTable), 'au.post_id = p.id', array());
                         $order = 'au.' . $field . ' ' . $arrOrder[1];
                         break;
                     case 'blog_posts_video':
                         $select->joinInner(array('v' => $joinTable), 'v.post_id = p.id', array());
                         $order = 'v.' . $field . ' ' . $arrOrder[1];
                         break;
                     case 'blog_posts_lacations':
                         $select->joinInner(array('l' => $joinTable), 'l.post_id = p.id', array());
                         $order = 'l.' . $field . ' ' . $arrOrder[1];
                         break;
                     case 'blog_posts_tags':
                         $select->joinInner(array('t' => $joinTable), 't.post_id = p.id', array());
                         $order = 't.' . $field . ' ' . $arrOrder[1];
                         break;
                     case 'blog_posts_comments':
                         $select->joinInner(array('c' => $joinTable), 'c.post_id = p.id', array());
                         $order = 'c.' . $field . ' ' . $arrOrder[1];
                         break;
                     case 'users':
                         $select->joinInner(array('u' => $joinTable), 'u.id = p.user_id', array());
                         $order = 'u.' . $field . ' ' . $arrOrder[1];
                         break;
                     case 'users_profile':
                         $select->joinInner(array('u_profile' => $joinTable), 'u_profile.user_id = p.user_id', array())->where('u_profile.profile_key = ?', $field);
                         $order = 'u_profile.profile_value ' . $arrOrder[1];
                         break;
                     default:
                         $order = 'p.' . $field . ' ' . $arrOrder[1];
                         break;
                 }
             }
         } else {
             $order = 'p.' . $order;
         }
     }
     $select->order($order);
     return $select;
 }