PHP Doctrine_Query::orderby方法代码示例

 public function testGetLimitSubqueryWithOrderByOnAggregateValuesAndColumns()
 {
     $q = new Doctrine_Query();
     $q->select('u.name, COUNT(DISTINCT a.id) num_albums');
     $q->from('User u, u.Album a');
     $q->orderby('num_albums, u.name');
     $q->groupby('u.id');
     $q->limit(5);
     $q->execute();
     $this->assertEqual($this->dbh->pop(), 'SELECT e.id AS e__id, e.name AS e__name, COUNT(DISTINCT a.id) AS a__0 FROM entity e LEFT JOIN album a ON e.id = a.user_id WHERE e.id IN (SELECT doctrine_subquery_alias.id FROM (SELECT DISTINCT e2.id, COUNT(DISTINCT a2.id) AS a2__0 FROM entity e2 LEFT JOIN album a2 ON e2.id = a2.user_id WHERE (e2.type = 0) GROUP BY e2.id ORDER BY a2__0, e2.name LIMIT 5) AS doctrine_subquery_alias) AND (e.type = 0) GROUP BY e.id ORDER BY a__0, e.name');
 }

 public function testGetLimitSubquerySupportsOrderByAndHavingWithAggregateValues()
 {
     $q = new Doctrine_Query();
     $q->select('u.name, COUNT(DISTINCT a.id) num_albums');
     $q->from('User u, u.Album a');
     $q->orderby('num_albums DESC');
     $q->having('num_albums > 0');
     $q->groupby('u.id');
     $q->limit(5);
     $q->execute();
     $this->dbh->pop();
     $this->assertEqual($this->dbh->pop(), 'SELECT DISTINCT e2.id, COUNT(DISTINCT a2.id) AS a2__0 FROM entity e2 LEFT JOIN album a2 ON e2.id = a2.user_id WHERE (e2.type = 0) GROUP BY e2.id HAVING a2__0 > 0 ORDER BY a2__0 DESC LIMIT 5');
 }

 public function testSelfReferencingWithNestedOrderBy()
 {
     $query = new Doctrine_Query();
     $query->from('Forum_Category.Subcategory.Subcategory');
     $query->orderby('Forum_Category.id ASC, Forum_Category.Subcategory.name DESC');
     $coll = $query->execute();
     $category = $coll[0];
     $this->assertEqual($category->name, 'Root');
     $this->assertEqual($category->Subcategory[0]->name, 'Sub 2');
     $this->assertEqual($category->Subcategory[1]->name, 'Sub 1');
     $this->assertEqual($category->Subcategory[1]->Subcategory[0]->name, 'Sub 1 Sub 1');
     $this->assertEqual($category->Subcategory[1]->Subcategory[1]->name, 'Sub 1 Sub 2');
     $this->assertEqual($category->Subcategory[0]->Subcategory[0]->name, 'Sub 2 Sub 1');
     $this->assertEqual($category->Subcategory[0]->Subcategory[1]->name, 'Sub 2 Sub 2');
     $this->connection->clear();
 }

 public function testGetLimitSubqueryOrderBy2()
 {
     $q = new Doctrine_Query();
     $q->select('u.name, COUNT(DISTINCT a.id) num_albums');
     $q->from('User u, u.Album a');
     $q->orderby('num_albums');
     $q->groupby('u.id');
     try {
         // this causes getLimitSubquery() to be used, and it fails
         $q->limit(5);
         $users = $q->execute();
         $count = $users->count();
     } catch (Doctrine_Exception $e) {
         $this->fail();
     }
     $this->assertEqual($q->getSql(), 'SELECT e.id AS e__id, e.name AS e__name, COUNT(DISTINCT a.id) AS a__0 FROM entity e LEFT JOIN album a ON e.id = a.user_id WHERE e.id IN (SELECT DISTINCT e2.id FROM entity e2 LEFT JOIN album a2 ON e2.id = a2.user_id WHERE (e2.type = 0) GROUP BY e2.id ORDER BY a__0 LIMIT 5) AND (e.type = 0) GROUP BY e.id ORDER BY a__0');
 }

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

 public function testOrderByExpression()
 {
     $query = new Doctrine_Query();
     $query->select('u.*, (u.id * u.id) multiplication');
     $query->from('User u');
     $query->orderby('multiplication asc');
     try {
         $users = $query->execute();
         $this->pass();
     } catch (Exception $e) {
         $this->fail();
     }
 }