PHP SQLQuery::orderby方法代码示例

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