PHP Query::setGroup方法代码示例

 public static function getList($parameters = array())
 {
     $query = new Query(static::getEntity());
     if (!isset($parameters['select'])) {
         $query->setSelect(array('*'));
     }
     foreach ($parameters as $param => $value) {
         switch ($param) {
             case 'select':
                 $query->setSelect($value);
                 break;
             case 'filter':
                 $query->setFilter($value);
                 break;
             case 'group':
                 $query->setGroup($value);
                 break;
             case 'order':
                 $query->setOrder($value);
                 break;
             case 'limit':
                 $query->setLimit($value);
                 break;
             case 'offset':
                 $query->setOffset($value);
                 break;
             case 'count_total':
                 $query->countTotal($value);
                 break;
             case 'options':
                 $query->setOptions($value);
                 break;
             case 'runtime':
                 foreach ($value as $name => $fieldInfo) {
                     $query->registerRuntimeField($name, $fieldInfo);
                 }
                 break;
             case 'data_doubling':
                 if ($value) {
                     $query->enableDataDoubling();
                 } else {
                     $query->disableDataDoubling();
                 }
                 break;
             default:
                 throw new Main\ArgumentException("Unknown parameter: " . $param, $param);
         }
     }
     return $query->exec();
 }

 /**
  * @param Type\DateTime $dateFrom
  * @param Type\DateTime $dateTo
  * @param               $interval
  *
  * @return array|bool
  * @throws \Bitrix\Main\ArgumentException
  */
 public static function getMaxUserActivity(Type\DateTime $dateFrom, Type\DateTime $dateTo, $interval)
 {
     if (!in_array($interval, array('hour', 'day', 'month'), true)) {
         throw new Main\ArgumentException('Interval should be the "hour", or "day", or "month".');
     }
     // first, get sum by user
     if ($interval === 'hour') {
         $subQuery = new Entity\Query(UserHourTable::getEntity());
         $subQuery->setFilter(array('><HOUR' => array(ConvertTimeStamp($dateFrom->getTimestamp(), 'FULL'), ConvertTimeStamp($dateTo->getTimestamp(), 'FULL'))));
     } else {
         $subQuery = new Entity\Query(UserDayTable::getEntity());
         $subQuery->setFilter(array('><DAY' => array(ConvertTimeStamp($dateFrom->getTimestamp()), ConvertTimeStamp($dateTo->getTimestamp()))));
     }
     $subQuery->addSelect(new Entity\ExpressionField('TOTAL_SUM', 'SUM(%s)', 'TOTAL'));
     foreach (UserHourTable::getSectionNames() as $sectionName) {
         $subQuery->addSelect(new Entity\ExpressionField($sectionName . '_SUM', 'SUM(%s)', $sectionName));
     }
     $subQuery->setGroup('USER_ID');
     // then get max values
     $query = new Entity\Query($subQuery);
     $query->addSelect(new Entity\ExpressionField('TOTAL', 'MAX(%s)', 'TOTAL_SUM'));
     foreach (UserHourTable::getSectionNames() as $sectionName) {
         $query->addSelect(new Entity\ExpressionField($sectionName, 'MAX(%s)', $sectionName . '_SUM'));
     }
     $result = $query->exec();
     $data = $result->fetch();
     return $data;
 }

 public static function GetRegionsIdsByNames($arRegNames, $countryId = false)
 {
     if (self::isLocationProMigrated()) {
         try {
             $types = self::getTypes();
             $query = new Entity\Query(self::SELF_ENTITY_NAME);
             $fieldMap = array('RID' => 'REGION_ID', 'RNAME' => 'NAME.NAME', 'RSHORT_NAME' => 'NAME.SHORT_NAME');
             $selectFields = $fieldMap;
             $filterFields = array(array('LOGIC' => 'OR', 'RNAME' => $arRegNames, 'RSHORT_NAME' => $arRegNames), '=TYPE_ID' => $types['REGION'], '!=REGION_ID' => '0');
             if ($countryId = intval($countryId)) {
                 $filterFields['=COUNTRY_ID'] = $countryId;
             }
             // order
             $orderFields = array('RNAME' => 'asc', 'RSHORT_NAME' => 'asc');
             // group
             $groupFields = array('RID');
             $nameJoinCondition = array('=this.ID' => 'ref.LOCATION_ID');
             if (strlen($strLang)) {
                 $nameJoinCondition['=ref.LANGUAGE_ID'] = array('?', $strLang);
             }
             $query->registerRuntimeField('NAME', array('data_type' => self::NAME_ENTITY_NAME, 'reference' => $nameJoinCondition, 'join_type' => 'left'));
             $query->setSelect($selectFields);
             $query->setFilter($filterFields);
             $query->setOrder($orderFields);
             $query->setGroup($groupFields);
             $result = array();
             $res = $query->exec();
             while ($item = $res->fetch()) {
                 $result[strlen($item['RNAME']) ? $item['RNAME'] : $item['RSHORT_NAME']] = $item['RID'];
             }
             return $result;
         } catch (Exception $e) {
             return array();
         }
     } else {
         global $DB;
         $arResult = array();
         $arWhere = array();
         $arQueryFields = array('RL.NAME', 'RL.SHORT_NAME');
         if (is_array($arRegNames)) {
             foreach ($arRegNames as $regName) {
                 $regName = $DB->ForSql($regName);
                 foreach ($arQueryFields as $field) {
                     $arWhere[] = $field . " LIKE '" . $regName . "'";
                 }
             }
             if (count($arWhere) > 0) {
                 $strWhere = implode(' OR ', $arWhere);
                 $query = "\tSELECT RL.REGION_ID, RL.NAME, RL.SHORT_NAME\n\t\t\t\t\t\t\t\tFROM b_sale_location_region_lang RL ";
                 if ($countryId) {
                     $strWhere = 'L.COUNTRY_ID=\'' . intval($countryId) . '\' AND (' . $strWhere . ')';
                     $query .= "LEFT JOIN b_sale_location L ON L.REGION_ID=RL.REGION_ID ";
                 }
                 $query .= "WHERE " . $strWhere;
                 $query .= " GROUP BY RL.REGION_ID";
                 $query .= " ORDER BY RL.NAME, RL.SHORT_NAME";
                 $dbList = $DB->Query($query);
                 $arRegionsLang = array();
                 while ($arRegion = $dbList->Fetch()) {
                     if (strlen($arRegion["NAME"]) > 0) {
                         $idx = $arRegion["NAME"];
                     } else {
                         $idx = $arRegion["SHORT_NAME"];
                     }
                     $arResult[$idx] = $arRegion["REGION_ID"];
                 }
             }
         }
         return $arResult;
     }
 }

     if (!array_key_exists($k, $totalColumns)) {
         unset($totalSelect[$k]);
     }
 }
 // add SUM aggr
 $_totalSelect = $totalSelect;
 $totalSelect = array();
 foreach ($_totalSelect as $k => $v) {
     $totalSelect[] = new Entity\ExpressionField('TOTAL_' . $k, 'SUM(%s)', $k);
 }
 if (!empty($totalSelect)) {
     // source query
     $query_from = new Entity\Query($entity);
     $query_from->setSelect($select);
     $query_from->setFilter($filter);
     $query_from->setGroup($group);
     foreach ($runtime as $k => $v) {
         $query_from->registerRuntimeField($k, $v);
     }
     // total query
     $total_query = new Entity\Query($query_from);
     $total_query->setSelect($totalSelect);
     $result = $total_query->exec();
     $total = $result->fetch();
     $total = $total === false ? array() : $total;
 } else {
     $total = array();
 }
 // </editor-fold>
 // <editor-fold defaultstate="collapsed" desc="group_concat fields">
 $grcData = array();

 public static function getConnectedEntitiesQuery($locationPrimary, $linkType = 'id', $parameters = array())
 {
     if ($linkType == 'id') {
         $locationPrimary = Assert::expectIntegerPositive($locationPrimary, Loc::getMessage('SALE_LOCATION_CONNECTOR_ENTITY_LOCATION_PRIMARY_FLD_NAME'));
     } else {
         $locationPrimary = Assert::expectStringNotNull($locationPrimary, Loc::getMessage('SALE_LOCATION_CONNECTOR_ENTITY_LOCATION_PRIMARY_FLD_NAME'));
     }
     $useGroups = GroupTable::checkGroupUsage() && static::getUseGroups();
     // check if we have groups in project and entity uses groups
     $useCodes = static::getUseCodes();
     // this entity uses codes
     $groupUseCodes = GroupLocationTable::getUseCodes();
     // group entity uses codes
     $typeFld = static::getTypeField();
     /*LOCATION_TYPE*/
     $linkFld = static::getLinkField();
     /*DELIVERY_ID*/
     $locationLinkFld = static::getLocationLinkField();
     /*LOCATION_ID*/
     $targetPrimaryFld = static::getTargetEntityPrimaryField();
     /*ID*/
     $groupLocationLinkFld = GroupLocationTable::getLocationLinkField();
     /*LOCATION_ID*/
     $groupLinkFld = GroupLocationTable::getLinkField();
     /*LOCATION_GROUP_ID*/
     $seachById = $linkType == 'id';
     $dbConnection = Main\HttpApplication::getConnection();
     if (!is_array($parameters)) {
         $parameters = array();
     }
     if (is_array($parameters['runtime'])) {
         Assert::announceNotImplemented('Sorry, runtime clause is not implemented currently.');
     }
     $order = array();
     if (is_array($parameters['order'])) {
         Assert::announceNotImplemented('Sorry, order-over-union clause is not implemented currently.');
     }
     $filter = array();
     if (is_array($parameters['filter']) && !empty($parameters['filter'])) {
         $filter = $parameters['filter'];
     }
     $select = array('*');
     if (is_array($parameters['select']) && !empty($parameters['select'])) {
         $select = $parameters['select'];
     }
     /*
     query example when working with delivery:
     
     select distinct D.* from b_sale_delivery D
     	inner join b_sale_delivery2location DL on D.ID = DL.DELIVERY_ID and DL.LOCATION_TYPE = 'L'
     	inner join b_sale_location L1 on L1.CODE = DL.LOCATION_ID
     	inner join b_sale_location L2 on L2.ID(there will be CODE, if we search by code) = 65683 and L2.LEFT_MARGIN >= L1.LEFT_MARGIN and L2.RIGHT_MARGIN <= L1.RIGHT_MARGIN;
     */
     $query = new Entity\Query(static::getTargetEntityName());
     $DLCondition = array('=this.' . $targetPrimaryFld => 'ref.' . $linkFld);
     if ($useGroups) {
         $DLCondition['=ref.' . $typeFld] = array('?', self::DB_LOCATION_FLAG);
     }
     $query->registerRuntimeField('DL', array('data_type' => get_called_class(), 'reference' => $DLCondition, 'join_type' => 'inner'))->registerRuntimeField('L1', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=this.DL.' . $locationLinkFld => 'ref.' . ($useCodes ? 'CODE' : 'ID')), 'join_type' => 'inner'))->registerRuntimeField('L2', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=ref.' . ($seachById ? 'ID' : 'CODE') => array('?', $locationPrimary), '>=ref.LEFT_MARGIN' => 'this.L1.LEFT_MARGIN', '<=ref.RIGHT_MARGIN' => 'this.L1.RIGHT_MARGIN'), 'join_type' => 'inner'))->setSelect($select)->setFilter($filter)->setOrder($order);
     if (!$useGroups) {
         // emulate "select distinct"
         $query->setGroup($select);
         return $query->getQuery();
     } else {
         $sqls = array($query->getQuery());
         $query = new Entity\Query(static::getTargetEntityName());
         /*
         query example when working with delivery:
         
         select D.* from b_sale_delivery D
         	inner join b_sale_delivery2location DL on D.ID = DL.DELIVERY_ID and DL.LOCATION_TYPE = 'G'
         	inner join b_sale_location_group G on G.CODE = DL.LOCATION_ID (if this entity uses ID, skip this join)
         	inner join b_sale_grouplocation GL on GL.LOCATION_GROUP_ID = G.ID (if this entity uses ID, there will be DL.LOCATION_ID)
         	inner join b_sale_location L1 on L1.ID (there will be CODE, if grouplocation entity uses CODE) = GL.LOCATION_ID
         	inner join b_sale_location L2 on L2.ID (there will be CODE, if we seach by code) = 65683 and L2.LEFT_MARGIN >= L1.LEFT_MARGIN and L2.RIGHT_MARGIN <= L1.RIGHT_MARGIN;
         */
         $query->registerRuntimeField('DL', array('data_type' => get_called_class(), 'reference' => array('=this.' . $targetPrimaryFld => 'ref.' . $linkFld, '=ref.' . $typeFld => array('?', self::DB_GROUP_FLAG)), 'join_type' => 'inner'));
         if ($useCodes) {
             $query->registerRuntimeField('G', array('data_type' => '\\Bitrix\\Sale\\Location\\Group', 'reference' => array('=this.DL.' . $locationLinkFld => 'ref.CODE'), 'join_type' => 'inner'));
         }
         $query->registerRuntimeField('GL', array('data_type' => '\\Bitrix\\Sale\\Location\\GroupLocation', 'reference' => array($useCodes ? '=this.G.ID' : '=this.DL.' . $locationLinkFld => 'ref.' . $groupLinkFld), 'join_type' => 'inner'))->registerRuntimeField('L1', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=this.GL.' . $groupLocationLinkFld => 'ref.' . ($groupUseCodes ? 'CODE' : 'ID')), 'join_type' => 'inner'))->registerRuntimeField('L2', array('data_type' => '\\Bitrix\\Sale\\Location\\Location', 'reference' => array('=ref.' . ($seachById ? 'ID' : 'CODE') => array('?', $locationPrimary), '>=ref.LEFT_MARGIN' => 'this.L1.LEFT_MARGIN', '<=ref.RIGHT_MARGIN' => 'this.L1.RIGHT_MARGIN'), 'join_type' => 'inner'))->setSelect($select)->setFilter($filter)->setOrder($order);
         $sqls[] = $query->getQuery();
         return static::unionize($sqls);
     }
 }