PHP Doctrine_Query::orderBy方法代码示例

 public function retrieveFolderList(Doctrine_Query $q)
 {
     $alias = $q->getRootAlias();
     $q->leftJoin($alias . '.Assets a');
     $q->orderBy($alias . '.lft');
     return $q;
 }

 /**
  * @param $orderby
  */
 protected function setOrderBy($orderby)
 {
     if ($this->_orderby_run) {
         return;
     }
     $this->_query->orderBy('f.created_at DESC');
     $this->_orderby_run = true;
 }

 public function testQueryRemoveOrderByPart()
 {
     $q = new Doctrine_Query();
     $q->from('User u');
     $q->orderBy('u.id DESC');
     $q->removeDqlQueryPart('orderby');
     $this->assertEqual($q->getSqlQuery(), 'SELECT e.id AS e__id, e.name AS e__name, e.loginname AS e__loginname, e.password AS e__password, e.type AS e__type, e.created AS e__created, e.updated AS e__updated, e.email_id AS e__email_id FROM entity e WHERE (e.type = 0)');
 }

 public function addUpcoming(Doctrine_Query $q, $limit = null)
 {
     $q->orderBy('start_date');
     $q->addWhere('DATE(start_date) >= DATE(NOW())');
     if (!is_null($limit)) {
         $q->limit($limit);
     }
 }

 public function orderBy($orderby)
 {
     $validCharacters = array('.', ' ', ',', '_');
     $string = str_replace($validCharacters, '', $orderby);
     if (!ctype_alnum($string)) {
         return;
     }
     return parent::orderBy($orderby);
 }

 public function orderBy($order)
 {
     if (strlen(strstr($order, 'due_amount')) > 0) {
         $this->addSelect("c.id, SUM(i.gross_amount-i.paid_amount) AS due_amount");
     }
     if (strlen(strstr($order, 'gross_amount')) > 0) {
         $this->addSelect("c.id, SUM(if(i.gross_amount is null,0,i.gross_amount)) as gross_amount");
     }
     return parent::orderBy($order);
 }

 function buildQuery(Doctrine_Query $query)
 {
     $query->leftJoin('swBlogPost.Translation');
     $query->leftJoin('swBlogPost.swBlogPostTags t');
     $query->leftJoin('t.Translation tt');
     if (strlen($this->getValue('tag')) > 0) {
         $query->addWhere('tt.name = ? and tt.lang = ?', array($this->getValue('tag'), sfContext::getInstance()->getUser()->getCulture()));
     }
     if (!is_null($this->getValue('published'))) {
         $query->addWhere('swBlogPost.published = ?', $this->getValue('published'));
     }
     $query->orderBy('created_at DESC');
     return $query;
 }

 /**
  * @param $orderby
  */
 protected function setOrderBy()
 {
     if ($this->_orderby_run) {
         return;
     }
     switch ($this->_order_by) {
         case 'gallery_ordering':
             if ($this->_gallery_added) {
                 $this->_query->orderBy('s.ordering ' . $this->_order_direction);
             }
             break;
         default:
             $this->_query->orderBy('f.' . $this->_order_by . ' ' . $this->_order_direction);
     }
     $this->_orderby_run = true;
 }

 /**
  * @return
  */
 protected function setOrderBy()
 {
     if ($this->_orderby_run) {
         return;
     }
     switch ($this->_order_by) {
         case 'slice_title':
         case 'slice_filesize':
         case 'slice_xsize':
         case 'slice_ysize':
         case 'slice_updated_at':
         case 'slice_created_at':
             $this->_query->orderBy('s.' . str_replace('slice_', '', $this->_order_by) . ' ' . $this->_order_direction);
             break;
         case 'file_title':
         case 'file_filesize':
         case 'file_xsize':
         case 'file_ysize':
         case 'file_updated_at':
         case 'file_created_at':
             $this->_query->leftJoin('s.File f');
             $this->_query->orderBy('f.' . str_replace('file_', '', $this->_order_by) . ' ' . $this->_order_direction);
             break;
         case 'views':
             $this->_query->leftJoin('s.File f');
             $this->_query->leftJoin('f.Views v');
             $this->_query->orderBy('v.count ' . $this->_order_direction);
             break;
         case 'loves':
             $this->_query->leftJoin('s.File f');
             $this->_query->leftJoin('f.Loves l');
             $this->_query->orderBy('l.count ' . $this->_order_direction);
             break;
         case 'random':
             $this->_query->select('s.*, RANDOM() as rand');
             $this->_query->orderby('rand');
             break;
         case 'gallery_ordering':
             $this->_query->orderBy('s.ordering');
     }
     $this->_orderby_run = true;
 }

 function buildQuery(Doctrine_Query $query)
 {
     $query->orderBy('swBlogComment.created_at ASC');
     $query->leftJoin('swBlogComment.swBlogPost');
     if ($this->getValue('moderated') == -2) {
         $query->whereIn('swBlogComment.moderated', array(swBlogComment::MODERATED_NONE, swBlogComment::MODERATED_OK));
     }
     if ($this->getValue('moderated') == -1) {
         $query->whereIn('swBlogComment.moderated', array(swBlogComment::MODERATED_NONE));
     }
     if ($this->getValue('moderated') == -3) {
         $query->whereIn('swBlogComment.moderated', array(swBlogComment::MODERATED_KO, swBlogComment::MODERATED_OK));
     }
     if ($this->getValue('moderated') == 1) {
         $query->whereIn('swBlogComment.moderated', array(swBlogComment::MODERATED_OK));
     }
     if ($this->getValue('moderated') === 0) {
         $query->whereIn('swBlogComment.moderated', array(swBlogComment::MODERATED_KO));
     }
     if (is_numeric($this->getValue('post_id'))) {
         $query->addWhere('swBlogComment.post_id = ?', $this->getValue('post_id'));
     }
     return $query;
 }

 /**
  *
  * @param Doctrine_Query $query
  * @param FilterWidgetForm $filter
  * @return Doctrine_Query
  */
 public function filter(Doctrine_Query $query, $filter)
 {
     if (!$filter) {
         return $query;
     }
     //    print_r($filter->getValues()); die('y');
     /* @var $filter  policatFilter */
     if ($filter->getValue(self::FILTER_CAMPAIGN)) {
         $query->andWhere('w.campaign_id = ?', $filter->getValue(self::FILTER_CAMPAIGN));
     }
     if ($filter->getValue(self::FILTER_STATUS)) {
         $query->andWhere('w.status = ?', $filter->getValue(self::FILTER_STATUS));
     }
     if ($filter->getValue(self::FILTER_LANGUAGE)) {
         $query->leftJoin('w.PetitionText pt_for_lang')->andWhere('pt_for_lang.language_id = ?', $filter->getValue(self::FILTER_LANGUAGE));
     }
     $petition_joined = false;
     if ($filter->getValue(self::FILTER_START)) {
         if (!$petition_joined) {
             $query->leftJoin('w.Petition p_filter');
             $petition_joined = true;
         }
         $query->andWhere('p_filter.start_at > ?', $filter->getValue(self::FILTER_START));
     }
     if ($filter->getValue(self::FILTER_END)) {
         if (!$petition_joined) {
             $query->leftJoin('w.Petition p_filter');
             $petition_joined = true;
         }
         $query->andWhere('p_filter.end_at < ?', $filter->getValue(self::FILTER_END));
     }
     if ($filter->getValue(self::FILTER_PETITION)) {
         $query->andWhere('w.petition_id = ?', $filter->getValue(self::FILTER_PETITION));
     }
     if ($filter->getValue(self::FILTER_ORDER)) {
         switch ($filter->getValue(self::FILTER_ORDER)) {
             case self::ORDER_CAMPAIGN_ASC:
                 $query->leftJoin('w.Campaign c_order')->orderBy('c_order.name ASC')->addOrderBy('w.campaign_id ASC')->addOrderBy('w.id ASC');
                 break;
             case self::ORDER_CAMPAIGN_DESC:
                 $query->leftJoin('w.Campaign c_order')->orderBy('c_order.name DESC')->addOrderBy('w.campaign_id DESC')->addOrderBy('w.id DESC');
                 break;
             case self::ORDER_ACTION_ASC:
                 if (!$petition_joined) {
                     $query->leftJoin('w.Petition p_filter');
                     $petition_joined = true;
                 }
                 $query->orderBy('p_filter.name ASC')->addOrderBy('w.petition_id ASC')->addOrderBy('w.id ASC');
                 break;
             case self::ORDER_ACTION_DESC:
                 if (!$petition_joined) {
                     $query->leftJoin('w.Petition p_filter');
                     $petition_joined = true;
                 }
                 $query->orderBy('p_filter.name DESC')->addOrderBy('w.petition_id DESC')->addOrderBy('w.id DESC');
                 break;
             case self::ORDER_STATUS_ASC:
                 $query->orderBy('w.status ASC')->addOrderBy('w.id ASC');
                 break;
             case self::ORDER_STATUS_DESC:
                 $query->orderBy('w.status DESC')->addOrderBy('w.id DESC');
                 break;
             case self::ORDER_WIDGET_ASC:
                 $query->orderBy('w.id ASC');
                 break;
             case self::ORDER_WIDGET_DESC:
                 $query->orderBy('w.id DESC');
                 break;
             case self::ORDER_LANGUAGE_ASC:
                 $query->leftJoin('w.PetitionText pt_ord_lang')->leftJoin('pt_ord_lang.Language ord_lang')->orderBy('ord_lang.order_number ASC')->addOrderBy('w.id ASC');
                 break;
             case self::ORDER_LANGUAGE_DESC:
                 $query->leftJoin('w.PetitionText pt_ord_lang')->leftJoin('pt_ord_lang.Language ord_lang')->orderBy('ord_lang.order_number DESC')->addOrderBy('w.id DESC');
                 break;
             case self::ORDER_ACTIVITY_ASC:
                 $query->orderBy('w.activity_at ASC');
                 break;
             case self::ORDER_ACTIVITY_DESC:
                 $query->orderBy('w.activity_at DESC');
                 break;
             case self::ORDER_TRENDING:
                 $query->select('w.*');
                 $query->addSelect('(SELECT count(z.id) FROM PetitionSigning z WHERE DATE_SUB(NOW(),INTERVAL 1 DAY) <= z.created_at  and z.widget_id = w.id and z.status = ' . PetitionSigning::STATUS_VERIFIED . ') as signings24');
                 $query->orderBy('signings24 DESC, w.activity_at DESC, w.id DESC');
                 break;
         }
     }
     if ($filter->getValue(self::FILTER_MIN_SIGNINGS)) {
         $query->andWhere('(SELECT count(ps.id) FROM PetitionSigning ps WHERE ps.widget_id = w.id AND ps.status = ? LIMIT ' . $filter->getValue(self::FILTER_MIN_SIGNINGS) . ') >= ?', array(PetitionSigning::STATUS_VERIFIED, $filter->getValue(self::FILTER_MIN_SIGNINGS)));
     }
     return $query;
 }

 /**
  * Create the query for selecting objects, eventually along with related
  * objects
  *
  * @param   array   $params  an array of criterions for the selection
  */
 public function addToQuery(Doctrine_Query $query, $params)
 {
     if (isset($sort)) {
         $query->orderBy($sort);
     }
     if (isset($params['id'])) {
         $values = explode(',', $params['id']);
         if (count($values) == 1) {
             $query->andWhere($this->model . '.id = ?', $values[0]);
         } else {
             $query->whereIn($this->model . '.id', $values);
         }
         unset($params['id']);
     }
     foreach ($params as $name => $value) {
         $query->andWhere($this->model . '.' . $name . ' = ?', $value);
     }
     return $query;
 }

 /**
  *
  * @param Doctrine_Query $query
  * @param FilterPetitionForm $filter
  * @return Doctrine_Query
  */
 public function filter(Doctrine_Query $query, $filter)
 {
     if (!$filter) {
         return $query;
     }
     /* @var $filter policatFilter */
     if ($filter->getValue(self::FILTER_CAMPAIGN)) {
         $query->andWhere('p.campaign_id = ?', $filter->getValue(self::FILTER_CAMPAIGN));
     }
     if ($filter->getValue(self::FILTER_KIND)) {
         $query->andWhere('p.kind = ?', $filter->getValue(self::FILTER_KIND));
     }
     if ($filter->getValue(self::FILTER_STATUS)) {
         $query->andWhere('p.status = ?', $filter->getValue(self::FILTER_STATUS));
     } else {
         $query->andWhere('p.status != ?', Petition::STATUS_DELETED);
     }
     if ($filter->getValue(self::FILTER_START)) {
         $query->andWhere('p.start_at > ?', $filter->getValue(self::FILTER_START));
     }
     if ($filter->getValue(self::FILTER_END)) {
         $query->andWhere('p.end_at < ?', $filter->getValue(self::FILTER_END));
     }
     if ($filter->getValue(self::FILTER_ORDER)) {
         switch ($filter->getValue(self::FILTER_ORDER)) {
             case self::ORDER_CAMPAIGN_ASC:
                 $query->leftJoin('p.Campaign c_order')->orderBy('c_order.name ASC')->addOrderBy('p.campaign_id ASC')->addOrderBy('p.id ASC');
                 break;
             case self::ORDER_CAMPAIGN_DESC:
                 $query->leftJoin('p.Campaign c_order')->orderBy('c_order.name DESC')->addOrderBy('p.campaign_id DESC')->addOrderBy('p.id DESC');
                 break;
             case self::ORDER_ACTION_ASC:
                 $query->orderBy('p.name ASC');
                 break;
             case self::ORDER_ACTION_DESC:
                 $query->orderBy('p.name DESC');
                 break;
             case self::ORDER_STATUS_ASC:
                 $query->orderBy('p.status ASC')->addOrderBy('p.id ASC');
                 break;
             case self::ORDER_STATUS_DESC:
                 $query->orderBy('p.status DESC')->addOrderBy('p.id DESC');
                 break;
             case self::ORDER_ID_ASC:
                 $query->orderBy('p.id ASC');
                 break;
             case self::ORDER_ID_DESC:
                 $query->orderBy('p.id DESC');
                 break;
             case self::ORDER_ACTIVITY_ASC:
                 $query->orderBy('p.activity_at ASC');
                 break;
             case self::ORDER_ACTIVITY_DESC:
                 $query->orderBy('p.activity_at DESC');
                 break;
             case self::ORDER_TRENDING:
                 $query->select('p.*');
                 $query->addSelect('(SELECT count(z.id) FROM PetitionSigning z WHERE DATE_SUB(NOW(),INTERVAL 1 DAY) <= z.created_at  and z.petition_id = p.id and z.status = ' . PetitionSigning::STATUS_VERIFIED . ') as signings24');
                 $query->orderBy('signings24 DESC, p.activity_at DESC, p.id DESC');
                 break;
         }
     }
     if ($filter->getValue(self::FILTER_MIN_SIGNINGS)) {
         $query->andWhere('(SELECT count(ps.id) FROM PetitionSigning ps WHERE ps.petition_id = p.id AND ps.status = ? LIMIT ' . $filter->getValue(self::FILTER_MIN_SIGNINGS) . ') >= ?', array(PetitionSigning::STATUS_VERIFIED, $filter->getValue(self::FILTER_MIN_SIGNINGS)));
     }
     return $query;
 }

 /**
  * Typesafe call to modify
  * @access private
  **/
 protected function modifyImpl(Doctrine_Query &$o)
 {
     if ($this->sortfield) {
         $o->orderBy($this->sortfield . " " . $this->dir);
     }
 }

 public function addDefaultSort(Doctrine_Query $q)
 {
     return $q->orderBy($q->getRootAlias() . '.lft ASC');
 }