PHP Query::setTableAliasPostfix方法代码示例

 /** @return array */
 public function getList(array $params)
 {
     $group = isset($params['group']) ? strtoupper($params['group']) : '';
     if ($group !== '' && $group !== self::GROUP_BY_USER && $group !== self::GROUP_BY_DATE) {
         $group = '';
     }
     /** @var Filter $filter */
     $filter = isset($params['filter']) ? $params['filter'] : null;
     if (!$filter instanceof Filter) {
         throw new Main\ObjectNotFoundException("The 'filter' is not found in params.");
     }
     $semanticID = $filter->getExtraParam('semanticID', PhaseSemantics::UNDEFINED);
     $isFinalSemantics = PhaseSemantics::isFinal($semanticID);
     $group = isset($params['group']) ? strtoupper($params['group']) : '';
     if ($group !== '' && $group !== self::GROUP_BY_USER && $group !== self::GROUP_BY_DATE) {
         $group = '';
     }
     /** @var array $select */
     $select = isset($params['select']) && is_array($params['select']) ? $params['select'] : array();
     $name = '';
     $aggregate = '';
     if (!empty($select)) {
         $selectItem = $select[0];
         if (isset($selectItem['name'])) {
             $name = $selectItem['name'];
         }
         if (isset($selectItem['aggregate'])) {
             $aggregate = strtoupper($selectItem['aggregate']);
         }
     }
     if ($name === '') {
         $name = 'INVOICE_SUM';
     }
     if ($aggregate !== '' && !in_array($aggregate, array('SUM', 'COUNT', 'MAX', 'MIN'))) {
         $aggregate = '';
     }
     $permissionSql = '';
     if ($this->enablePermissionCheck) {
         $permissionSql = $this->preparePermissionSql();
         if ($permissionSql === false) {
             //Access denied;
             return array();
         }
     }
     $period = $filter->getPeriod();
     $periodStartDate = $period['START'];
     $periodEndDate = $period['END'];
     $query = new Query(DealInvoiceStatisticsTable::getEntity());
     $query->addSelect($name);
     if ($aggregate !== '') {
         if ($aggregate === 'COUNT') {
             $query->registerRuntimeField('', new ExpressionField($name, "COUNT(*)"));
         } else {
             $query->registerRuntimeField('', new ExpressionField($name, "{$aggregate}({$name})"));
         }
     }
     $query->setTableAliasPostfix('_s2');
     $subQuery = new Query(DealInvoiceStatisticsTable::getEntity());
     $subQuery->setTableAliasPostfix('_s1');
     $subQuery->addSelect('OWNER_ID');
     $subQuery->addFilter('>=END_DATE', $periodStartDate);
     $subQuery->addFilter('<=START_DATE', $periodEndDate);
     //$subQuery->addFilter('<=CREATED_DATE', $periodEndDate);
     if ($semanticID !== PhaseSemantics::UNDEFINED) {
         $subQuery->addFilter('=STAGE_SEMANTIC_ID', $semanticID);
     }
     if ($this->enablePermissionCheck && is_string($permissionSql) && $permissionSql !== '') {
         $subQuery->addFilter('@OWNER_ID', new SqlExpression($permissionSql));
     }
     $responsibleIDs = $filter->getResponsibleIDs();
     if (!empty($responsibleIDs)) {
         $subQuery->addFilter('@RESPONSIBLE_ID', $responsibleIDs);
     }
     $subQuery->addGroup('OWNER_ID');
     $subQuery->addSelect('MAX_CREATED_DATE');
     $subQuery->registerRuntimeField('', new ExpressionField('MAX_CREATED_DATE', 'MAX(CREATED_DATE)'));
     $query->registerRuntimeField('', new ReferenceField('M', Base::getInstanceByQuery($subQuery), array('=this.OWNER_ID' => 'ref.OWNER_ID', '=this.CREATED_DATE' => 'ref.MAX_CREATED_DATE'), array('join_type' => 'INNER')));
     $sort = isset($params['sort']) && is_array($params['sort']) && !empty($params['sort']) ? $params['sort'] : null;
     if ($sort) {
         foreach ($sort as $sortItem) {
             if (isset($sortItem['name'])) {
                 $query->addOrder($sortItem['name'], isset($sortItem['order']) ? $sortItem['order'] : 'asc');
             }
         }
     }
     if ($group !== '') {
         if ($group === self::GROUP_BY_USER) {
             $query->addSelect('RESPONSIBLE_ID');
             $query->addGroup('RESPONSIBLE_ID');
         } else {
             if ($group === self::GROUP_BY_DATE) {
                 if ($isFinalSemantics) {
                     $query->addSelect('END_DATE', 'D');
                     $query->addGroup('END_DATE');
                     if (!$sort) {
                         $query->addOrder('END_DATE', 'ASC');
                     }
                 } else {
                     $query->addSelect('CREATED_DATE', 'D');
//.........这里部分代码省略.........

 /**
  * @return Query
  */
 protected static function prepareInvoiceQuery($startDate, $endDate, $ownerFieldReference = '%s', $postfix = '')
 {
     $query = new Query(DealInvoiceStatisticsTable::getEntity());
     if ($postfix !== '') {
         $query->setTableAliasPostfix($postfix);
     }
     $query->addFilter('=IS_LOST', false);
     $query->addFilter('>=CREATED_DATE', $startDate);
     $query->addFilter('<=CREATED_DATE', $endDate);
     $query->addFilter('=OWNER_ID', new SqlExpression($ownerFieldReference));
     if (!Main\Application::getConnection() instanceof Main\DB\OracleConnection) {
         $query->setLimit(1);
     }
     return $query;
 }

                 foreach ($entity->GetPrimaryArray() as $_primary) {
                     $_sub_filter['=' . $_primary] = new CSQLWhereExpression('?#', $_sub_init_table_alias . '.' . $_primary);
                 }
                 // add value filter
                 $filterCompare = CReport::$iBlockCompareVariations[$fElem['compare']];
                 $filterName = $fElem['name'];
                 $filterValue = $fElem['value'];
                 if ($filterCompare === '>%') {
                     $filterCompare = '';
                     $filterValue = $filterValue . '%';
                 }
                 $_sub_filter[$filterCompare . $filterName] = $filterValue;
                 // build subquery
                 $_sub_query = new Entity\Query($entity);
                 $_sub_query->setFilter($_sub_filter);
                 $_sub_query->setTableAliasPostfix('_sub');
                 $_sub_sql = 'EXISTS(' . $_sub_query->getQuery() . ')';
                 $_sub_sql = '(CASE WHEN ' . $_sub_sql . ' THEN 1 ELSE 0 END)';
                 // expression escaping as sprintf requires
                 $_sub_sql = str_replace('%', '%%', $_sub_sql);
                 $_runtime_field = array('data_type' => 'integer', 'expression' => array($_sub_sql));
                 $f_filter_alias = 'F_FILTER_ALIAS_' . ++$f_filter_alias_count;
                 $runtime[$f_filter_alias] = $_runtime_field;
                 $fElem['name'] = $f_filter_alias;
                 $fElem['compare'] = 'EQUAL';
                 $fElem['value'] = 1;
             }
         }
     }
 }
 unset($fInfo);

 protected function getFilterCswFields(&$filter)
 {
     $fields = array();
     foreach ($filter as $filter_def => &$filter_match) {
         if ($filter_def === 'LOGIC') {
             continue;
         }
         if (!is_numeric($filter_def)) {
             $csw_result = \CSQLWhere::makeOperation($filter_def);
             list($definition, ) = array_values($csw_result);
             $chain = $this->filter_chains[$definition];
             $last = $chain->getLastElement();
             // need to create an alternative of CSQLWhere in D7.Entity
             $field_type = $last->getValue()->getDataType();
             // rewrite type & value for CSQLWhere
             if ($field_type == 'integer') {
                 $field_type = 'int';
             } elseif ($field_type == 'boolean') {
                 $field_type = 'string';
                 /** @var BooleanField $field */
                 $field = $last->getValue();
                 $values = $field->getValues();
                 if (is_numeric($values[0]) && is_numeric($values[1])) {
                     $field_type = 'int';
                 }
                 if (is_scalar($filter_match)) {
                     $filter_match = $field->normalizeValue($filter_match);
                 }
             } elseif ($field_type == 'float') {
                 $field_type = 'double';
             } elseif ($field_type == 'enum' || $field_type == 'text') {
                 $field_type = 'string';
             }
             $sqlDefinition = $chain->getSqlDefinition();
             $callback = null;
             // data-doubling-off mode
             /** @see disableDataDoubling */
             if ($chain->forcesDataDoublingOff() || $this->data_doubling_off && $chain->hasBackReference()) {
                 $primaryName = $this->init_entity->getPrimary();
                 $uniquePostfix = '_TMP' . rand();
                 // build subquery
                 $subQuery = new Query($this->init_entity);
                 $subQuery->addSelect($primaryName);
                 $subQuery->addFilter($filter_def, $filter_match);
                 $subQuery->setTableAliasPostfix(strtolower($uniquePostfix));
                 $subQuerySql = $subQuery->getQuery();
                 // proxying subquery as value to callback
                 $filter_match = $subQuerySql;
                 $callback = array($this, 'dataDoublingCallback');
                 $field_type = 'callback';
                 // change sql definition
                 $idChain = $this->getRegisteredChain($primaryName);
                 $sqlDefinition = $idChain->getSqlDefinition();
             }
             //$is_having = $last->getValue() instanceof ExpressionField && $last->getValue()->isAggregated();
             // if back-reference found (Entity:REF)
             // if NO_DOUBLING mode enabled, then change getSQLDefinition to subquery exists(...)
             // and those chains should not be in joins if it is possible
             /*if (!$this->data_doubling && $chain->hasBackReference())
             				{
             					$field_type = 'callback';
             					$init_query = $this;
             
             					$callback = function ($field, $operation, $value) use ($init_query, $chain)
             					{
             						$init_entity = $init_query->getEntity();
             						$init_table_alias = CBaseEntity::camel2snake($init_entity->getName()).$init_query->getTableAliasPostfix();
             
             						$filter = array();
             
             						// add primary linking with main query
             						foreach ($init_entity->getPrimaryArray() as $primary)
             						{
             							$filter['='.$primary] = new CSQLWhereExpression('?#', $init_table_alias.'.'.$primary);
             						}
             
             						// add value filter
             						$filter[CSQLWhere::getOperationByCode($operation).$chain->getDefinition()] = $value;
             
             						// build subquery
             						$query_class = __CLASS__;
             						$sub_query = new $query_class($init_entity);
             						$sub_query->setFilter($filter);
             						$sub_query->setTableAliasPostfix('_sub');
             
             						return 'EXISTS(' . $sub_query->getQuery() . ')';
             					};
             				}*/
             $fields[$definition] = array('TABLE_ALIAS' => 'table', 'FIELD_NAME' => $sqlDefinition, 'FIELD_TYPE' => $field_type, 'MULTIPLE' => '', 'JOIN' => '', 'CALLBACK' => $callback);
         }
         if (is_array($filter_match)) {
             $fields = array_merge($fields, $this->getFilterCswFields($filter_match));
         }
     }
     return $fields;
 }

 /**
  * @param                     $elem
  * @param                     $select
  * @param                     $is_init_entity_aggregated
  * @param                     $fList
  * @param Entity\QueryChain[] $fChainList
  * @param                     $helper_class
  * @param Entity\Base         $entity
  *
  * @return array
  */
 public static function prepareSelectViewElement($elem, $select, $is_init_entity_aggregated, $fList, $fChainList, $helper_class, Entity\Base $entity)
 {
     $result = null;
     $alias = null;
     if (empty($elem['aggr']) && !strlen($elem['prcnt'])) {
         $result = $elem['name'];
     } else {
         $expression = '';
         /** @var Entity\Field $field */
         $field = $fList[$elem['name']];
         $chain = $fChainList[$elem['name']];
         $alias = $chain->getAlias();
         $dataType = call_user_func(array($helper_class, 'getFieldDataType'), $field);
         if (!empty($elem['aggr'])) {
             $alias = $elem['aggr'] . '_' . $alias;
             if ($dataType == 'boolean') {
                 // sum int for boolean
                 global $DB;
                 /** @var Entity\BooleanField $field */
                 $trueValue = $field->normalizeValue(true);
                 $localDef = 'CASE WHEN %s = \'' . $DB->ForSql($trueValue) . '\' THEN 1 ELSE 0 END';
             } else {
                 $localDef = '%s';
             }
             if ($elem['aggr'] == 'COUNT_DISTINCT') {
                 $dataType = 'integer';
                 $expression = array('COUNT(DISTINCT ' . $localDef . ')', $elem['name']);
             } else {
                 if ($dataType == 'boolean') {
                     $dataType = 'integer';
                 }
                 if ($elem['aggr'] == 'GROUP_CONCAT') {
                     $expression = array($localDef, $elem['name']);
                 } else {
                     $expression = array($elem['aggr'] . '(' . $localDef . ')', $elem['name']);
                 }
             }
             // pack 1:N aggregations into subquery
             if ($chain->hasBackReference() && $elem['aggr'] != 'GROUP_CONCAT') {
                 $confirm = call_user_func_array(array($helper_class, 'confirmSelectBackReferenceRewrite'), array(&$elem, $chain));
                 if ($confirm) {
                     $filter = array();
                     foreach ($entity->GetPrimaryArray() as $primary) {
                         $filter['=' . $primary] = new CSQLWhereExpression('?#', ToLower($entity->getCode()) . '.' . $primary);
                     }
                     $query = new Entity\Query($entity);
                     $query->addSelect(new Entity\ExpressionField('X', $expression[0], $elem['name']));
                     $query->setFilter($filter);
                     $query->setTableAliasPostfix('_sub');
                     $expression = array('(' . $query->getQuery() . ')');
                     // double aggregation if init entity aggregated
                     if ($is_init_entity_aggregated) {
                         if ($elem['aggr'] == 'COUNT_DISTINCT') {
                             $expression[0] = 'SUM(' . $expression[0] . ')';
                         } else {
                             $expression[0] = $elem['aggr'] . '(' . $expression[0] . ')';
                         }
                     }
                 }
                 // confirmed
             }
         }
         if (strlen($elem['prcnt'])) {
             $alias = $alias . '_PRCNT';
             $dataType = 'integer';
             if ($elem['prcnt'] == 'self_column') {
                 if (empty($expression)) {
                     $expression = array('%s', $elem['name']);
                 }
             } else {
                 if (empty($expression)) {
                     $localDef = '%s';
                     $localMembers = array($elem['name']);
                 } else {
                     $localDef = $expression[0];
                     $localMembers = array_slice($expression, 1);
                 }
                 list($remoteAlias, $remoteSelect) = self::prepareSelectViewElement($select[$elem['prcnt']], $select, $is_init_entity_aggregated, $fList, $fChainList, $helper_class, $entity);
                 if (is_array($remoteSelect) && !empty($remoteSelect['expression'])) {
                     // remote field is expression
                     $remoteDef = $remoteSelect['expression'][0];
                     $remoteMembers = array_slice($remoteSelect['expression'], 1);
                     $alias = $alias . '_FROM_' . $remoteAlias;
                 } else {
                     // remote field is usual field
                     $remoteDef = '%s';
                     $remoteMembers = array($remoteSelect);
                     $remoteAlias = Entity\QueryChain::getAliasByDefinition($entity, $remoteSelect);
                     $alias = $alias . '_FROM_' . $remoteAlias;
//.........这里部分代码省略.........