PHP SQLQuery::addGroupBy方法代码示例

 function exportCLAUsers()
 {
     $params = $this->owner->getRequest()->getVars();
     $fields = $params['fields'];
     $groups = $params['groups'];
     $ext = $params['ext'];
     if (!isset($params['fields']) || empty($params['fields'])) {
         return $this->owner->httpError('412', 'missing required param fields');
     }
     if (!isset($params['groups']) || empty($params['groups'])) {
         return $this->owner->httpError('412', 'missing required param groups');
     }
     if (!isset($params['ext']) || empty($params['ext'])) {
         return $this->owner->httpError('412', 'missing required param ext');
     }
     if (!count($fields)) {
         return $this->httpError('412', 'missing required param fields');
     }
     $query = new SQLQuery();
     $query->setFrom('Member');
     $query->addLeftJoin('Group_Members', 'GM.MemberID = Member.ID', 'GM');
     $query->addLeftJoin('Group_Members', 'GM2.MemberID = Member.ID', 'GM2');
     $query->addLeftJoin('Group', 'Group.ID = GM.GroupID');
     $query->addWhere('Member.GerritID IS NOT NULL');
     $query->addWhere('GM2.GroupID IN (' . implode(',', $groups) . ')');
     $fields['Groups'] = "GROUP_CONCAT(Group.Code SEPARATOR ' | ')";
     $query->setSelect($fields);
     $query->addGroupBy('Member.ID');
     $query->addOrderBy(array('Member.Surname', 'Member.FirstName'));
     $result = $query->execute();
     $filename = "MembersByRole-" . date('Ymd') . "." . $ext;
     $delimiter = $ext == 'xls' ? "\t" : ",";
     return CSVExporter::getInstance()->export($filename, $result, $delimiter);
 }

 public function ChartData()
 {
     $chartData = array();
     $list = ArrayList::create(array());
     $sqlQuery = new SQLQuery();
     $sqlQuery->setFrom('Addon');
     $sqlQuery->setSelect('Created');
     $sqlQuery->selectField('COUNT(*)', 'CountInOneDay');
     $sqlQuery->addWhere('"Created" >= DATE_SUB(NOW(), INTERVAL 30 DAY)');
     $sqlQuery->addGroupBy('DATE(Created)');
     $result = $sqlQuery->execute();
     if (count($result)) {
         foreach ($result as $row) {
             $date = date('j M Y', strtotime($row['Created']));
             if (!isset($chartData[$date])) {
                 $chartData[$date] = $row['CountInOneDay'];
             }
         }
     }
     if (count($chartData)) {
         foreach ($chartData as $x => $y) {
             $list->push(ArrayData::create(array('XValue' => $x, 'YValue' => $y)));
         }
     }
     return $list;
 }

 public function Tags()
 {
     $select = array('Title');
     $query = new SQLQuery($select, 'PostTag');
     $query->selectField('count(PostTag.ID)', 'Number');
     $query->selectField('PostTag.ID');
     $query->addInnerjoin('MicroPost_Tags', 'PostTag.ID = MicroPost_Tags.PostTagID');
     $date = date('Y-m-d H:i:s', strtotime('-1 month'));
     $query->addWhere("MicroPost_Tags.Tagged > '{$date}'");
     $query->addWhere('"PostTag"."Title" NOT LIKE \'SELF_TAG%\'');
     $query->addGroupBy('PostTag.ID');
     $query->setLimit(20);
     $rows = $query->execute();
     $tags = ArrayList::create();
     foreach ($rows as $row) {
         $data = new ArrayData($row);
         $data->Link = Controller::join_links(TimelineController::URL_SEGMENT, '?tags=' . urlencode($data->Title));
         $tags->push($data);
     }
     return $tags;
 }

 /**
  * Append a GROUP BY clause to this query.
  * 
  * @param String $groupby Escaped SQL statement
  */
 public function groupby($groupby)
 {
     $this->query->addGroupBy($groupby);
     return $this;
 }

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

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