PHP SQLQuery::addOrderBy方法代码示例

 /**
  * Return the {@link SQLQuery} that provides your report data.
  */
 function sourceQuery($params)
 {
     $sqlQuery = new SQLQuery();
     $sqlQuery->setFrom('CalendarEvent');
     $sqlQuery->selectField('Date');
     $sqlQuery->selectField('CalendarEvent.Title', 'Event');
     $sqlQuery->selectField('StartTime');
     $sqlQuery->selectField('EndTime');
     $sqlQuery->addInnerJoin('CalendarEventDate', '"CalendarEventDate"."CalendarEventID" = "CalendarEvent"."ID"');
     if (isset($params['DateFrom'])) {
         $fromDate = new SS_DateTime('FromDate');
         $fromDate->setValue($params['DateFrom']);
         $sqlQuery->addWhere(array('Date >= ?' => $fromDate->Format("Y-m-d")));
     }
     if (isset($params['DateTo'])) {
         $toDate = new SS_DateTime('ToDate');
         $toDate->setValue($params['DateTo']);
         $sqlQuery->addWhere(array('Date <= ?' => $toDate->Format("Y-m-d")));
     }
     if (isset($params['PrivateBookings'])) {
         if ($params['PrivateBookings'] == 'Private') {
             $sqlQuery->addWhere('Private = 1');
         } elseif ($params['PrivateBookings'] == 'Public') {
             $sqlQuery->addWhere('Private = 0');
         }
     }
     $sqlQuery->addOrderBy('Date');
     $sqlQuery->addOrderBy('Event');
     $sqlQuery->addOrderBy('StartTime');
     $sqlQuery->addOrderBy('EndTime');
     return $sqlQuery;
 }

 /**
  * @see DataExtension::augmentSQL()
  */
 public function augmentSQL(SQLQuery &$query)
 {
     $select = $query->getSelect();
     if (empty($select) || $query->getDelete() || in_array("COUNT(*)", $select) || in_array("count(*)", $select)) {
         return;
     }
     if (!isset(self::$sortTables[$this->owner->class])) {
         $classes = array_reverse(ClassInfo::dataClassesFor($this->owner->class));
         $class = null;
         foreach ($classes as $cls) {
             if (DataObject::has_own_table($cls) && ($fields = DataObject::database_fields($cls)) && isset($fields['SortOrder'])) {
                 $class = $cls;
                 break;
             }
         }
         self::$sortTables[$this->owner->class] = $class;
     } else {
         $class = self::$sortTables[$this->owner->class];
     }
     if ($class) {
         $query->addOrderBy("\"{$class}\".\"SortOrder\" " . self::$sort_dir);
     } else {
         $query->addOrderBy("\"SortOrder\" " . self::$sort_dir);
     }
 }

 /**
  * 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;
 }

 /**
  * @see DataExtension::augmentSQL()
  */
 public function augmentSQL(SQLQuery &$query)
 {
     $select = $query->getSelect();
     if (empty($select) || $query->getDelete() || in_array("COUNT(*)", $select) || in_array("count(*)", $select)) {
         return;
     }
     if (!isset(self::$sortTables[$this->owner->class])) {
         // look up the table that has the SortOrder field
         $class = ClassInfo::table_for_object_field($this->owner->class, 'SortOrder');
         self::$sortTables[$this->owner->class] = $class;
     } else {
         $class = self::$sortTables[$this->owner->class];
     }
     if ($class) {
         $query->addOrderBy("\"{$class}\".\"SortOrder\" " . self::$sort_dir);
     } else {
         $query->addOrderBy("\"SortOrder\" " . self::$sort_dir);
     }
 }

 /**
  * Polls the message queue. Will return a map of senders with the lastest message ID
  * @see http://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results
  * @param SS_HTTPRequest $request
  */
 public function get_openchats(SS_HTTPRequest $request)
 {
     if (!Permission::checkMember(Member::currentUser(), "CMS_ACCESS_LiveChatAdmin")) {
         header("HTTP/1.0 403 Forbidden");
         die('You do not have permission to use the live chat module');
     }
     // find the messages that have been sent to you
     $query1 = new SQLQuery("*", "LiveChatMessage", "ToID = " . (int) Member::currentUserID());
     $query1->addOrderBy("ID DESC");
     $query1->addWhere("ClassName = 'LiveChatMessage'");
     $query2 = new SQLQuery("ID, FromID, FromName", '(' . $query1->sql() . ') x');
     $query2->addGroupBy("FromID, FromName");
     // only select the largest ID, unique for each sender
     $result = $query2->execute();
     $returnar = array();
     // add the names to the array
     foreach ($result as $id) {
         $member = Member::get()->byID($id['FromID']);
         $returnar[$id['ID']] = array("Name" => $member ? $member->getName() : $id['FromName'], "FromID" => $id['FromID'] ? $id['FromID'] : md5($id['FromName']));
     }
     header('Content-Type: application/json');
     die(json_encode($returnar));
 }

 function exportCompanyData()
 {
     $params = $this->owner->getRequest()->getVars();
     if (!isset($params['report_name']) || empty($params['report_name']) || !count($params['report_name'])) {
         return $this->owner->httpError('412', 'missing required param report_name');
     }
     if (!isset($params['extension']) || empty($params['extension'])) {
         return $this->owner->httpError('412', 'missing required param extension');
     }
     $report_name = isset($params['report_name']) ? $params['report_name'] : '';
     $fields = isset($params['fields']) ? $params['fields'] : array();
     $ext = $params['extension'];
     $query = new SQLQuery();
     if ($report_name) {
         switch ($report_name) {
             case 'sponsorship_type':
                 $query->setFrom('Company');
                 $query->addLeftJoin('SummitSponsorPage_Companies', 'SummitSponsorPage_Companies.CompanyID = Company.ID');
                 $query->addLeftJoin('Summit', 'Summit.ID = SummitSponsorPage_Companies.SummitID');
                 $query->addWhere('Summit.Active', '1');
                 $fields = array_merge($fields, array('Sponsorship' => 'SummitSponsorPage_Companies.SponsorshipType', 'Summit ID' => 'Summit.ID'));
                 $query->setSelect($fields);
                 $query->addOrderBy('SummitSponsorPage_Companies.SponsorshipType');
                 $filename = "Sponsorship_Levels_" . date('Ymd') . "." . $ext;
                 break;
             case 'member_level':
                 $query->setFrom('Company');
                 array_push($fields, 'Company.MemberLevel');
                 $query->setSelect($fields);
                 $filename = "Foundation_Levels_" . date('Ymd') . "." . $ext;
                 break;
             case 'users_roles':
                 $query->setFrom('Company');
                 $query->addInnerJoin('Company_Administrators', 'Company_Administrators.CompanyID = Company.ID');
                 $query->addLeftJoin('Member', 'Member.ID = Company_Administrators.MemberID');
                 $query->addLeftJoin('Group', 'Group.ID = Company_Administrators.GroupID');
                 array_push($fields, 'Group.Title');
                 $query->setSelect($fields);
                 $query->addOrderBy('Company.Name');
                 $filename = "User_Roles_" . date('Ymd') . "." . $ext;
                 break;
             case 'affiliates':
                 $query->setFrom('Org');
                 $query->addLeftJoin('Affiliation', 'Affiliation.OrganizationID = Org.ID');
                 $query->addLeftJoin('Member', 'Member.ID = Affiliation.MemberID');
                 $fields = array_merge($fields, array('Is Current' => 'Affiliation.Current', 'Job Title' => 'Affiliation.JobTitle'));
                 $query->setSelect($fields);
                 $query->addOrderBy('Org.Name');
                 $filename = "Employees_Affiliates_" . date('Ymd') . "." . $ext;
                 break;
             case 'deployments':
                 $query->setFrom('Org');
                 $query->addInnerJoin('Deployment', 'Deployment.OrgID = Org.ID');
                 $custom_fields = array('Creation' => 'Deployment.Created', 'Edited' => 'Deployment.LastEdited', 'Label' => 'Deployment.Label', 'Is Public' => 'Deployment.IsPublic');
                 $fields = array_merge($fields, $custom_fields);
                 $query->setSelect($fields);
                 $query->selectField("CONCAT('http://openstack.org/sangria/DeploymentDetails/',Deployment.ID)", "Link");
                 $query->addOrderBy('Org.Name');
                 $filename = "Deployments_" . date('Ymd') . "." . $ext;
                 break;
             case 'deployment_surveys':
                 $query->setFrom('Org');
                 $query->addLeftJoin('DeploymentSurvey', 'DeploymentSurvey.OrgID = Org.ID');
                 $query->addLeftJoin('Member', 'DeploymentSurvey.MemberID = Member.ID');
                 $custom_fields = array('Creation' => 'DeploymentSurvey.Created', 'Edited' => 'DeploymentSurvey.LastEdited', 'Title' => 'DeploymentSurvey.Title', 'City' => 'DeploymentSurvey.PrimaryCity', 'State' => 'DeploymentSurvey.PrimaryState', 'Country' => 'DeploymentSurvey.PrimaryCountry', 'Org Size' => 'DeploymentSurvey.OrgSize', 'Is Group Member' => 'DeploymentSurvey.UserGroupMember', 'Group Name' => 'DeploymentSurvey.UserGroupName', 'Ok to Contact' => 'DeploymentSurvey.OkToContact');
                 //insert custom fields after org fields
                 $pos = -1;
                 foreach ($fields as $field) {
                     $pos++;
                     if (strpos($field, 'Org') !== false) {
                         continue;
                     } else {
                         array_splice($fields, $pos, 0, $custom_fields);
                         break;
                     }
                 }
                 $query->setSelect($fields);
                 $query->selectField("CONCAT('http://openstack.org/sangria/SurveyDetails/',DeploymentSurvey.ID)", "Link");
                 $filename = "Deployment_Surveys" . date('Ymd') . "." . $ext;
                 break;
             case 'speakers':
                 $query->setFrom('PresentationSpeaker');
                 $query->addLeftJoin('Affiliation', 'Affiliation.MemberID = PresentationSpeaker.MemberID');
                 $query->addLeftJoin('Org', 'Affiliation.OrganizationID = Org.ID');
                 $query->addLeftJoin('Summit', 'Summit.ID = PresentationSpeaker.SummitID');
                 $custom_fields = array('Speaker Name' => 'PresentationSpeaker.FirstName', 'Speaker Surname' => 'PresentationSpeaker.LastName', 'Summit' => 'Summit.Name');
                 $fields = array_merge($fields, $custom_fields);
                 $query->setSelect($fields);
                 $filename = "Speakers_" . date('Ymd') . "." . $ext;
                 break;
         }
     }
     //die($query->sql());
     $result = $query->execute();
     $delimiter = $ext == 'xls' ? "\t" : ",";
     return CSVExporter::getInstance()->export($filename, $result, $delimiter);
 }

 /**
  * Returns a number of topseller products.
  * 
  * @return ArrayList
  * 
  * @author Sebastian Diel <sdiel@pixeltricks.de>,
  *         Sascha Koehler <skoehler@pixeltricks.de>
  * @since 03.02.2015
  */
 public function Elements()
 {
     if (!$this->numberOfProductsToShow) {
         $this->numberOfProductsToShow = SilvercartTopsellerProductsWidget::$defaults['numberOfProductsToShow'];
     }
     $cachekey = 'TopsellerProducts' . $this->numberOfProductsToShow;
     $cache = SS_Cache::factory($cachekey);
     $cachedResult = $cache->load($cachekey);
     if ($cachedResult) {
         $result = unserialize($result);
     } else {
         $products = array();
         $sqlQuery = new SQLQuery();
         $sqlQuery->selectField('SOP.SilvercartProductID');
         $sqlQuery->selectField('SUM(SOP.Quantity) AS OrderedQuantity');
         $sqlQuery->addFrom('SilvercartOrderPosition SOP');
         $sqlQuery->addLeftJoin('SilvercartProduct', 'SP.ID = SOP.SilvercartProductID', 'SP');
         $sqlQuery->addWhere('SP.isActive = 1');
         $sqlQuery->addGroupBy('SOP.SilvercartProductID');
         $sqlQuery->addOrderBy('OrderedQuantity', 'DESC');
         $sqlQuery->setLimit($this->numberOfProductsToShow);
         $sqlResult = $sqlQuery->execute();
         foreach ($sqlResult as $row) {
             $product = DataObject::get_by_id('SilvercartProduct', $row['SilvercartProductID']);
             $product->addCartFormIdentifier = $this->ID . '_' . $product->ID;
             $products[] = $product;
         }
         $result = new ArrayList($products);
     }
     return $result;
 }

 /**
  * Perform a search against the data table.
  *
  * @param array $where Array of strings to add into the WHERE clause
  * @param array $orderby Array of column as key, to direction as value to add into the ORDER BY clause
  * @param string|int $start Record to start at (for paging)
  * @param string|int $pageLength Number of results per page (for paging)
  * @param boolean $paged Paged results or not?
  * @return ArrayList|PaginatedList
  */
 protected function queryList($where = array(), $orderby = array(), $start, $pageLength, $paged = true)
 {
     $dataClass = $this->dataRecord->getDataClass();
     if (!$dataClass) {
         return new PaginatedList(new ArrayList());
     }
     $resultColumns = $this->dataRecord->getDataSingleton()->summaryFields();
     $resultColumns['ID'] = 'ID';
     $results = new ArrayList();
     $query = new SQLQuery();
     $query->setSelect($this->escapeSelect(array_keys($resultColumns)))->setFrom("\"{$dataClass}\"");
     $query->addWhere($where);
     $query->addOrderBy($orderby);
     $query->setConnective('AND');
     if ($paged) {
         $query->setLimit($pageLength, $start);
     }
     foreach ($query->execute() as $record) {
         $result = new $dataClass($record);
         $result->Columns = $this->Columns($result);
         // we attach Columns here so the template can loop through them on each result
         $results->push($result);
     }
     if ($paged) {
         $list = new PaginatedList($results);
         $list->setPageStart($start);
         $list->setPageLength($pageLength);
         $list->setTotalItems($query->unlimitedRowCount());
         $list->setLimitItems(false);
     } else {
         $list = $results;
     }
     return $list;
 }

 /**
  * Update any requests to limit the results to the current site
  */
 public function augmentSQL(SQLQuery &$query)
 {
     if (Subsite::$disable_subsite_filter) {
         return;
     }
     // If you're querying by ID, ignore the sub-site - this is a bit ugly... (but it was WAYYYYYYYYY worse)
     //@TODO I don't think excluding if SiteTree_ImageTracking is a good idea however because of the SS 3.0 api and ManyManyList::removeAll() changing the from table after this function is called there isn't much of a choice
     $from = $query->getFrom();
     $where = $query->getWhere();
     if (!isset($from['SiteTree_ImageTracking']) && !($where && preg_match('/\\.(\'|"|`|)ID(\'|"|`|)/', $where[0]))) {
         $subsiteID = (int) Subsite::currentSubsiteID();
         // The foreach is an ugly way of getting the first key :-)
         foreach ($query->getFrom() as $tableName => $info) {
             $where = "\"{$tableName}\".\"SubsiteID\" IN (0, {$subsiteID})";
             $query->addWhere($where);
             break;
         }
         $sect = array_values($query->getSelect());
         $isCounting = strpos($sect[0], 'COUNT') !== false;
         // Ordering when deleting or counting doesn't apply
         if (!$query->getDelete() && !$isCounting) {
             $query->addOrderBy("\"SubsiteID\"");
         }
     }
 }