function testSelectWithOrderbyClause() {
// numeric limit
$query = new SQLQuery();
$query->from[] = "MyTable";
$query->orderby('MyName ASC');
// can't escape as we don't know if ASC or DESC is appended
$this->assertEquals("SELECT * FROM MyTable ORDER BY MyName ASC", $query->sql());
// array limit
$query = new SQLQuery();
$query->from[] = "MyTable";
$query->orderby(array('sort'=>'MyName'));
$this->assertEquals("SELECT * FROM MyTable ORDER BY `MyName`", $query->sql());
// array limit with start (MySQL specific)
$query = new SQLQuery();
$query->from[] = "MyTable";
$query->orderby(array('sort'=>'MyName','dir'=>'desc'));
$this->assertEquals("SELECT * FROM MyTable ORDER BY `MyName` DESC", $query->sql());
}
/**
* Build a {@link SQLQuery} object to perform the given query.
*
* @param string $filter A filter to be inserted into the WHERE clause.
* @param string|array $sort A sort expression to be inserted into the ORDER BY clause. If omitted, self::$default_sort will be used.
* @param string|array $limit A limit expression to be inserted into the LIMIT clause.
* @param string $join A single join clause. This can be used for filtering, only 1 instance of each DataObject will be returned.
* @param boolean $restictClasses Restrict results to only objects of either this class of a subclass of this class
* @param string $having A filter to be inserted into the HAVING clause.
*
* @return SQLQuery Query built.
*/
public function buildSQL($filter = "", $sort = "", $limit = "", $join = "", $restrictClasses = true, $having = "") {
// Find a default sort
if(!$sort) {
$sort = $this->stat('default_sort');
}
// Get the tables to join to
$tableClasses = ClassInfo::dataClassesFor($this->class);
if(!$tableClasses) {
if(!ManifestBuilder::has_been_included()) {
user_error("DataObjects have been requested before the manifest is loaded. Please ensure you are not querying the database in _config.php.", E_USER_ERROR);
} else {
user_error("DataObject::buildSQL: Can't find data classes (classes linked to tables) for $this->class. Please ensure you run dev/build after creating a new DataObject.", E_USER_ERROR);
}
}
$baseClass = array_shift($tableClasses);
$select = array("`$baseClass`.*");
// Build our intial query
$query = new SQLQuery($select);
$query->from("`$baseClass`");
$query->where($filter);
$query->orderby($sort);
$query->limit($limit);
// Add SQL for multi-value fields on the base table
$databaseFields = $this->databaseFields();
if($databaseFields) foreach($databaseFields as $k => $v) {
if(!in_array($k, array('ClassName', 'LastEdited', 'Created'))) {
if(ClassInfo::classImplements($v, 'CompositeDBField')) {
$this->dbObject($k)->addToQuery($query);
}
}
}
// Join all the tables
if($tableClasses && self::$subclass_access) {
foreach($tableClasses as $tableClass) {
$query->from[$tableClass] = "LEFT JOIN `$tableClass` ON `$tableClass`.ID = `$baseClass`.ID";
$query->select[] = "`$tableClass`.*";
// Add SQL for multi-value fields
$SNG = singleton($tableClass);
$databaseFields = $SNG->databaseFields();
if($databaseFields) foreach($databaseFields as $k => $v) {
if(!in_array($k, array('ClassName', 'LastEdited', 'Created'))) {
if(ClassInfo::classImplements($v, 'CompositeDBField')) {
$SNG->dbObject($k)->addToQuery($query);
}
}
}
}
}
$query->select[] = "`$baseClass`.ID";
$query->select[] = "if(`$baseClass`.ClassName,`$baseClass`.ClassName,'$baseClass') AS RecordClassName";
// Get the ClassName values to filter to
$classNames = ClassInfo::subclassesFor($this->class);
if(!$classNames) {
user_error("DataObject::get() Can't find data sub-classes for '$callerClass'");
}
// If querying the base class, don't bother filtering on class name
if($restrictClasses && $this->class != $baseClass) {
// Get the ClassName values to filter to
$classNames = ClassInfo::subclassesFor($this->class);
if(!$classNames) {
user_error("DataObject::get() Can't find data sub-classes for '$callerClass'");
}
$query->where[] = "`$baseClass`.ClassName IN ('" . implode("','", $classNames) . "')";
}
if($having) {
$query->having[] = $having;
}
if($join) {
$query->from[] = $join;
$query->groupby[] = reset($query->from) . ".ID";
}
return $query;
}
public function testReverseOrderBy() {
$query = new SQLQuery();
$query->from('MyTable');
// default is ASC
$query->orderby("Name");
$query->reverseOrderBy();
$this->assertEquals('SELECT * FROM MyTable ORDER BY Name DESC',$query->sql());
$query->orderby("Name DESC");
$query->reverseOrderBy();
$this->assertEquals('SELECT * FROM MyTable ORDER BY Name ASC',$query->sql());
$query->orderby(array("Name" => "ASC"));
$query->reverseOrderBy();
$this->assertEquals('SELECT * FROM MyTable ORDER BY Name DESC',$query->sql());
$query->orderby(array("Name" => 'DESC', 'Color' => 'asc'));
$query->reverseOrderBy();
$this->assertEquals('SELECT * FROM MyTable ORDER BY Name ASC, Color DESC',$query->sql());
$query->orderby('implode("MyName","Color") DESC');
$query->reverseOrderBy();
$this->assertEquals('SELECT implode("MyName","Color") AS "_SortColumn0" FROM MyTable ORDER BY _SortColumn0 ASC',$query->sql());
}
