public function testSelectWithOrderbyClause()
{
$query = new SQLSelect();
$query->setFrom("MyTable");
$query->setOrderBy('MyName');
$this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName ASC', $query->sql($parameters));
$query = new SQLSelect();
$query->setFrom("MyTable");
$query->setOrderBy('MyName desc');
$this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName DESC', $query->sql($parameters));
$query = new SQLSelect();
$query->setFrom("MyTable");
$query->setOrderBy('MyName ASC, Color DESC');
$this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName ASC, Color DESC', $query->sql($parameters));
$query = new SQLSelect();
$query->setFrom("MyTable");
$query->setOrderBy('MyName ASC, Color');
$this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName ASC, Color ASC', $query->sql($parameters));
$query = new SQLSelect();
$query->setFrom("MyTable");
$query->setOrderBy(array('MyName' => 'desc'));
$this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName DESC', $query->sql($parameters));
$query = new SQLSelect();
$query->setFrom("MyTable");
$query->setOrderBy(array('MyName' => 'desc', 'Color'));
$this->assertSQLEquals('SELECT * FROM MyTable ORDER BY MyName DESC, Color ASC', $query->sql($parameters));
$query = new SQLSelect();
$query->setFrom("MyTable");
$query->setOrderBy('implode("MyName","Color")');
$this->assertSQLEquals('SELECT *, implode("MyName","Color") AS "_SortColumn0" FROM MyTable ORDER BY "_SortColumn0" ASC', $query->sql($parameters));
$query = new SQLSelect();
$query->setFrom("MyTable");
$query->setOrderBy('implode("MyName","Color") DESC');
$this->assertSQLEquals('SELECT *, implode("MyName","Color") AS "_SortColumn0" FROM MyTable ORDER BY "_SortColumn0" DESC', $query->sql($parameters));
$query = new SQLSelect();
$query->setFrom("MyTable");
$query->setOrderBy('RAND()');
$this->assertSQLEquals('SELECT *, RAND() AS "_SortColumn0" FROM MyTable ORDER BY "_SortColumn0" ASC', $query->sql($parameters));
$query = new SQLSelect();
$query->setFrom("MyTable");
$query->addFrom('INNER JOIN SecondTable USING (ID)');
$query->addFrom('INNER JOIN ThirdTable USING (ID)');
$query->setOrderBy('MyName');
$this->assertSQLEquals('SELECT * FROM MyTable ' . 'INNER JOIN SecondTable USING (ID) ' . 'INNER JOIN ThirdTable USING (ID) ' . 'ORDER BY MyName ASC', $query->sql($parameters));
}
/**
* Return the number of rows in this query if the limit were removed. Useful in paged data sets.
*
* @param string $column
* @return int
*/
public function unlimitedRowCount($column = null)
{
// we can't clear the select if we're relying on its output by a HAVING clause
if (count($this->having)) {
$records = $this->execute();
return $records->numRecords();
}
$clone = clone $this;
$clone->limit = null;
$clone->orderby = null;
// Choose a default column
if ($column == null) {
if ($this->groupby) {
// @todo Test case required here
$countQuery = new SQLSelect();
$countQuery->select("count(*)");
$countQuery->addFrom(array('(' . $clone->sql($innerParameters) . ') all_distinct'));
$sql = $countQuery->sql($parameters);
// $parameters should be empty
$result = DB::prepared_query($sql, $innerParameters);
return $result->value();
} else {
$clone->setSelect(array("count(*)"));
}
} else {
$clone->setSelect(array("count({$column})"));
}
$clone->setGroupBy(array());
return $clone->execute()->value();
}
