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