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 testGetLimitSubqueryWithHavingOnAggregateValuesIncorrectAlias()
{
$q = new Doctrine_Query();
$q->select('u.name, COUNT(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(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 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');
}
