PHP SQLQuery::setOrderBy方法代码示例

 /**
  * Set the ORDER BY clause of this query
  *
  * @see SQLQuery::orderby()
  *
  * @param String $sort Column to sort on (escaped SQL statement)
  * @param String $direction Direction ("ASC" or "DESC", escaped SQL statement)
  * @param Boolean $clear Clear existing values
  * @return DataQuery
  */
 public function sort($sort = null, $direction = null, $clear = true)
 {
     if ($clear) {
         $this->query->setOrderBy($sort, $direction);
     } else {
         $this->query->addOrderBy($sort, $direction);
     }
     return $this;
 }

 private function checkBlogEntryPermissions()
 {
     $authorsId = array();
     $sqlQuery = new SQLQuery();
     $sqlQuery->setFrom('SiteTree_versions');
     $sqlQuery->selectField('AuthorID');
     $sqlQuery->addWhere('RecordID = ' . $this->ID);
     $sqlQuery->setOrderBy('ID DESC');
     $rawSQL = $sqlQuery->sql();
     $result = $sqlQuery->execute();
     foreach ($result as $row) {
         $authorsId[] = $row['AuthorID'];
     }
     $sqlQuery->setDelete(true);
     if (in_array(Member::currentUser()->ID, $authorsId) || $this->parent->OwnerID == Member::currentUser()->ID || Permission::check('ADMIN')) {
         return true;
     } else {
         return false;
     }
 }

 function exportFoundationMembers()
 {
     $params = $this->owner->getRequest()->getVars();
     if (!isset($params['fields']) || empty($params['fields'])) {
         return $this->owner->httpError('412', 'missing required param fields');
     }
     if (!isset($params['ext']) || empty($params['ext'])) {
         return $this->owner->httpError('412', 'missing required param ext');
     }
     $fields = $params['fields'];
     $ext = $params['ext'];
     $sanitized_fields = array();
     if (!count($fields)) {
         return $this->owner->httpError('412', 'missing required param fields');
     }
     $allowed_fields = array('ID' => 'ID', 'FirstName' => 'FirstName', 'SurName' => 'SurName', 'Email' => 'Email');
     for ($i = 0; $i < count($fields); $i++) {
         if (!array_key_exists($fields[$i], $allowed_fields)) {
             return $this->httpError('412', 'invalid field');
         }
         array_push($sanitized_fields, 'Member.' . $fields[$i]);
     }
     $query = new SQLQuery();
     $query->setFrom('Member');
     $query->setSelect($sanitized_fields);
     $query->addInnerJoin('Group_Members', 'Group_Members.MemberID = Member.ID');
     $query->addInnerJoin('Group', "Group.ID = Group_Members.GroupID AND Group.Code='foundation-members'");
     $query->setOrderBy('SurName,FirstName');
     $result = $query->execute();
     $data = array();
     foreach ($result as $row) {
         $member = array();
         foreach ($fields as $field) {
             $member[$field] = $row[$field];
         }
         array_push($data, $member);
     }
     $filename = "Foundation_Members_" . date('Ymd') . "." . $ext;
     return CSVExporter::getInstance()->export($filename, $data, ',');
 }

 /**
  * Test that multiple order elements are maintained in the given order
  */
 public function testOrderByMultiple()
 {
     if (DB::getConn() instanceof MySQLDatabase) {
         $query = new SQLQuery();
         $query->setSelect(array('"Name"', '"Meta"'));
         $query->setFrom('"SQLQueryTest_DO"');
         $query->setOrderBy(array('MID("Name", 8, 1) DESC', '"Name" ASC'));
         $records = array();
         foreach ($query->execute() as $record) {
             $records[] = $record;
         }
         $this->assertCount(2, $records);
         $this->assertEquals('Object 2', $records[0]['Name']);
         $this->assertEquals('2', $records[0]['_SortColumn0']);
         $this->assertEquals('Object 1', $records[1]['Name']);
         $this->assertEquals('1', $records[1]['_SortColumn0']);
     }
 }

 public function testJoinSubSelect()
 {
     $query = new SQLQuery();
     $query->setFrom('MyTable');
     $query->addInnerJoin('(SELECT * FROM MyOtherTable)', 'Mot.MyTableID = MyTable.ID', 'Mot');
     $query->addLeftJoin('(SELECT MyLastTable.MyOtherTableID, COUNT(1) as MyLastTableCount FROM MyLastTable ' . 'GROUP BY MyOtherTableID)', 'Mlt.MyOtherTableID = Mot.ID', 'Mlt');
     $query->setOrderBy('COALESCE(Mlt.MyLastTableCount, 0) DESC');
     $this->assertSQLEquals('SELECT *, COALESCE(Mlt.MyLastTableCount, 0) AS "_SortColumn0" FROM MyTable ' . 'INNER JOIN (SELECT * FROM MyOtherTable) AS "Mot" ON Mot.MyTableID = MyTable.ID ' . 'LEFT JOIN (SELECT MyLastTable.MyOtherTableID, COUNT(1) as MyLastTableCount FROM MyLastTable ' . 'GROUP BY MyOtherTableID) AS "Mlt" ON Mlt.MyOtherTableID = Mot.ID ' . 'ORDER BY "_SortColumn0" DESC', $query->sql($parameters));
 }

 public function testReverseOrderBy()
 {
     $query = new SQLQuery();
     $query->setFrom('MyTable');
     // default is ASC
     $query->setOrderBy("Name");
     $query->reverseOrderBy();
     $this->assertEquals('SELECT * FROM MyTable ORDER BY Name DESC', $query->sql());
     $query->setOrderBy("Name DESC");
     $query->reverseOrderBy();
     $this->assertEquals('SELECT * FROM MyTable ORDER BY Name ASC', $query->sql());
     $query->setOrderBy(array("Name" => "ASC"));
     $query->reverseOrderBy();
     $this->assertEquals('SELECT * FROM MyTable ORDER BY Name DESC', $query->sql());
     $query->setOrderBy(array("Name" => 'DESC', 'Color' => 'asc'));
     $query->reverseOrderBy();
     $this->assertEquals('SELECT * FROM MyTable ORDER BY Name ASC, Color DESC', $query->sql());
     $query->setOrderBy('implode("MyName","Color") DESC');
     $query->reverseOrderBy();
     $this->assertEquals('SELECT *, implode("MyName","Color") AS "_SortColumn0" FROM MyTable ORDER BY "_SortColumn0" ASC', $query->sql());
 }

 /**
  * Retrieves Locations by lat, long, distance, and optionally a limit.
  */
 public function getLocationSQLResultsByLatLong($lat = 37, $long = -122, $distance = 25, $limit = null)
 {
     //$data = DB::query('SELECT "ID" FROM "Marker" LIMIT 0 , '.$limit.';')->value();
     //$query = 'SELECT "ID", ( 3959 * acos( cos( radians('.$lat.') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('.$long.') ) + sin( radians('.$lat.') ) * sin( radians( Latitude ) ) ) ) AS "Distance" FROM "Marker" HAVING "Distance" < '.$distance.' ORDER BY "Distance" LIMIT 0 , '.$limit.';';
     $markerClass = StoreFinder::$MarkerClass;
     $sqlQuery = new SQLQuery();
     $sqlQuery->setFrom($markerClass);
     $sqlQuery->selectField('*');
     $sqlQuery->selectField('( 3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians(' . $long . ') ) + sin( radians(' . $lat . ') ) * sin( radians( Latitude ) ) ) )', 'Distance');
     $sqlQuery->setHaving("Distance < " . $distance);
     $sqlQuery->setOrderBy('Distance');
     $sqlQuery->setLimit($limit);
     if ($markerClass != 'Marker') {
         $sqlQuery->addLeftJoin("Marker", 'Marker.ID = ' . $markerClass . '.ID');
     }
     $this->extraSQL($sqlQuery);
     // Execute and return a Query object
     $result = $sqlQuery->execute();
     return $result;
 }

 function exportUsersPerRegion()
 {
     $params = $this->owner->getRequest()->getVars();
     if (!isset($params['countries']) || empty($params['countries'])) {
         return $this->owner->httpError('412', 'missing required param countries');
     }
     if (!isset($params['members']) || empty($params['members'])) {
         return $this->owner->httpError('412', 'missing required param members');
     }
     $countries = $params['countries'];
     $members = $params['members'];
     $join_members = '';
     $join_countries = '';
     if (!count($countries)) {
         return $this->owner->httpError('412', 'missing required param countries');
     } else {
         foreach ($countries as $country) {
             $join_countries .= "'" . $country . "',";
         }
     }
     $join_countries = rtrim($join_countries, ",");
     if (!count($members)) {
         return $this->owner->httpError('412', 'missing required param members');
     } else {
         foreach ($members as $member) {
             $join_members .= "'" . $member . "',";
         }
     }
     $join_members = rtrim($join_members, ",");
     $query = new SQLQuery();
     $select_fields = array('Member.FirstName', 'Member.Surname', 'Member.Email', 'Member.City', 'Member.State', 'Member.Country');
     $query->setFrom('Member');
     $query->setSelect($select_fields);
     $query->addInnerJoin('Group_Members', 'Group_Members.MemberID = Member.ID');
     $query->addInnerJoin('Group', "Group.ID = Group_Members.GroupID AND Group.Code IN (" . $join_members . ")");
     $query->setWhere("Member.Country IN (" . $join_countries . ")");
     $query->setOrderBy('SurName,FirstName');
     $result = $query->execute();
     $data = array();
     foreach ($result as $row) {
         $member = array('FirstName' => $row['FirstName'], 'Surname' => $row['Surname'], 'Email' => $row['Email'], 'City' => $row['City'], 'State' => $row['State'], 'Country' => CountryCodes::$iso_3166_countryCodes[$row['Country']]);
         array_push($data, $member);
     }
     $filename = "UsersPerCountry" . date('Ymd') . ".csv";
     return CSVExporter::getInstance()->export($filename, $data, ',');
 }

 /**
  * Ensure that if a query has an order by clause, those columns are present in the select.
  * 
  * @param SQLQuery $query
  * @return null
  */
 protected function ensureSelectContainsOrderbyColumns($query, $originalSelect = array())
 {
     $tableClasses = ClassInfo::dataClassesFor($this->dataClass);
     $baseClass = array_shift($tableClasses);
     if ($orderby = $query->getOrderBy()) {
         foreach ($orderby as $k => $dir) {
             // don't touch functions in the ORDER BY or function calls
             // selected as fields
             if (strpos($k, '(') !== false) {
                 continue;
             }
             $col = str_replace('"', '', trim($k));
             $parts = explode('.', $col);
             // Pull through SortColumn references from the originalSelect variables
             if (preg_match('/_SortColumn/', $col)) {
                 if (isset($originalSelect[$col])) {
                     $query->selectField($originalSelect[$col], $col);
                 }
                 continue;
             }
             if (count($parts) == 1) {
                 $databaseFields = DataObject::database_fields($baseClass);
                 // database_fields() doesn't return ID, so we need to
                 // manually add it here
                 $databaseFields['ID'] = true;
                 if (isset($databaseFields[$parts[0]])) {
                     $qualCol = "\"{$baseClass}\".\"{$parts[0]}\"";
                     // remove original sort
                     unset($orderby[$k]);
                     // add new columns sort
                     $orderby[$qualCol] = $dir;
                 } else {
                     $qualCol = "\"{$parts['0']}\"";
                 }
                 // To-do: Remove this if block once SQLQuery::$select has been refactored to store getSelect()
                 // format internally; then this check can be part of selectField()
                 $selects = $query->getSelect();
                 if (!isset($selects[$col]) && !in_array($qualCol, $selects)) {
                     $query->selectField($qualCol);
                 }
             } else {
                 $qualCol = '"' . implode('"."', $parts) . '"';
                 // To-do: Remove this if block once SQLQuery::$select has been refactored to store getSelect()
                 // format internally; then this check can be part of selectField()
                 if (!in_array($qualCol, $query->getSelect())) {
                     $query->selectField($qualCol);
                 }
             }
         }
         $query->setOrderBy($orderby);
     }
 }

 /**
  * @return DataList
  */
 private function getAllowedDependants()
 {
     $steps_query = new SQLQuery();
     $steps_query->setSelect("ID");
     $steps_query->setFrom("SurveyStepTemplate");
     $high_order = $this->Step()->order();
     $current_survey_id = $this->Step()->SurveyTemplateID;
     $steps_query->setWhere("SurveyTemplateID = {$current_survey_id} AND `Order` <= {$high_order} ");
     $steps_query->setOrderBy('`Order`', 'ASC');
     $current_step_ids = $steps_query->execute()->keyedColumn();
     return SurveyQuestionTemplate::get()->filter(array('StepID' => $current_step_ids));
 }

 /**
  * @return SQLQuery
  */
 protected function getRemoteObjectsQuery()
 {
     // Get all tables to query
     $tables = $this->getRemoteClassHierarchy();
     $baseClass = array_shift($tables);
     // Generate sql query
     $query = new SQLQuery("\"{$baseClass}\".*", "\"{$baseClass}\"", $this->targetWhere);
     $query->setOrderBy("\"{$baseClass}\".\"ID\" ASC");
     // Sort by ID for some performance reasons
     foreach ($tables as $class) {
         $query->addSelect("\"{$class}\".*");
         $query->addLeftJoin($class, "\"{$baseClass}\".\"ID\" = \"{$class}\".\"ID\"");
     }
     return $query;
 }