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