PHP Zend_Db_Select::group方法代码示例

 /**
  * Add track info to the select statement
  *
  * @param string|array $fields
  * @param boolean $groupBy Optional, add these fields to group by statement
  * @return \Gems_Tracker_Token_TokenSelect
  */
 public function andTracks($fields = '*', $groupBy = false)
 {
     $this->sql_select->join('gems__tracks', 'gto_id_track = gtr_id_track', $fields);
     if ($groupBy && is_array($fields)) {
         $this->sql_select->group($fields);
     }
     return $this;
 }

 /**
  * appends sql to given select statement
  *
  * @param  Zend_Db_Select                $_select
  * @param  Tinebase_Backend_Sql_Abstract $_backend
  * @throws Tinebase_Exception_InvalidArgument
  */
 public function appendFilterSql($_select, $_backend)
 {
     // quote field identifier, set action and replace wildcards
     $field = $this->_getQuotedFieldName($_backend);
     if (!(isset($this->_opSqlMap[$this->_operator]) || array_key_exists($this->_operator, $this->_opSqlMap))) {
         throw new Tinebase_Exception_InvalidArgument('Operator "' . $this->_operator . '" not defined in sql map of ' . get_class($this));
     }
     $action = $this->_opSqlMap[$this->_operator];
     // don't remove wildcards for certain operators
     // TODO add an option for this?
     $value = !in_array($this->_operator, array('in', 'notin')) ? $this->_replaceWildcards($this->_value) : $this->_value;
     // check if group by is operator and return if this is the case
     if ($this->_operator == 'group') {
         $_select->group($this->_field);
     }
     if (in_array($this->_operator, array('in', 'notin')) && !is_array($value)) {
         $value = explode(' ', $value);
     }
     // this is a text filter, so all items in the filter must be of type text (needed in pgsql)
     if (in_array($this->_operator, array('in', 'notin')) && is_array($value)) {
         foreach ($value as &$item) {
             $item = (string) $item;
         }
     }
     $db = Tinebase_Core::getDb();
     if (is_array($value) && empty($value)) {
         $_select->where('1=' . (substr($this->_operator, 0, 3) == 'not' ? '1/* empty query */' : '0/* impossible query */'));
         return;
     }
     if ($this->_operator == 'equalsspecial') {
         if (is_array($value)) {
             foreach ($value as $key => $v) {
                 $value[$key] = preg_replace('/(\\s+|\\-)/', '%', $v);
             }
         } else {
             $value = preg_replace('/(\\s+|\\-)/', '%', $value);
         }
     }
     if (!in_array($this->_operator, array('in', 'notin'))) {
         $where = Tinebase_Core::getDb()->quoteInto(Tinebase_Backend_Sql_Command::factory($db)->prepareForILike($field) . ' ' . $action['sqlop'], $value);
     } else {
         $where = Tinebase_Core::getDb()->quoteInto($field . $action['sqlop'], $value);
     }
     if (in_array($this->_operator, array('not', 'notin')) && $value !== '') {
         $where = "( {$where} OR {$field} IS NULL)";
     }
     if (in_array($this->_operator, array('equals', 'equalsspecial', 'contains', 'startswith', 'endswith', 'in')) && $value === '') {
         $where = "( {$where} OR {$field} IS NULL)";
     }
     // finally append query to select object
     $_select->where($where);
 }

 /**
  * appends sql to given select statement
  *
  * @param  Zend_Db_Select                $_select
  * @param  Tinebase_Backend_Sql_Abstract $_backend
  * @throws Tinebase_Exception_NotFound
  */
 public function appendFilterSql($_select, $_backend)
 {
     // don't take empty tag filter into account
     if (empty($this->_value)) {
         if ($this->_operator === 'in') {
             $_select->where('1=0');
         }
         return;
     }
     // check the view right of the tag (throws Exception if not accessable)
     Tinebase_Tags::getInstance()->getTagsById($this->_value);
     $db = Tinebase_Core::getDb();
     $idProperty = $db->quoteIdentifier($this->_options['idProperty']);
     $app = Tinebase_Application::getInstance()->getApplicationByName($this->_options['applicationName']);
     $correlationName = Tinebase_Record_Abstract::generateUID(5) . (is_array($this->_value) === true ? implode(',', $this->_value) : $this->_value) . 'tag';
     // per left join we add a tag column named as the tag and filter this joined column
     // NOTE: we name the column we join like the tag, to be able to join multiple tag criteria (multiple invocations of this function)
     $_select->joinLeft(array($correlationName => SQL_TABLE_PREFIX . 'tagging'), $db->quoteIdentifier("{$correlationName}.record_id") . " = {$idProperty} " . " AND " . $db->quoteIdentifier("{$correlationName}.application_id") . " = " . $db->quote($app->getId()) . " AND " . $db->quoteInto($db->quoteIdentifier("{$correlationName}.tag_id") . " IN (?)", (array) $this->_value), array());
     $_select->where($db->quoteIdentifier("{$correlationName}.tag_id") . $this->_opSqlMap[$this->_operator]['sqlop']);
     $_select->group($this->_options['idProperty']);
 }

 public function getNumberOfTaxes()
 {
     $tbl_prefix = SimpleInvoices_Db_Table_Abstract::getTablePrefix();
     $select = new Zend_Db_Select($this->_db);
     $select->distinct(true);
     $select->from($tbl_prefix . 'tax', array('tax_id'));
     $select->joinInner($tbl_prefix . 'invoice_item_tax', $tbl_prefix . "invoice_item_tax.tax_id = " . $tbl_prefix . "tax.tax_id", NULL);
     $select->joinInner($tbl_prefix . "invoice_items", $tbl_prefix . "invoice_items.id = " . $tbl_prefix . "invoice_item_tax.invoice_item_id", NULL);
     $select->where($tbl_prefix . "invoice_items.invoice_id=?", $this->_id);
     $select->group(array($tbl_prefix . "tax.tax_id"));
     $result = $this->_db->fetchAll($select);
     return count($result);
 }

 /**
  * Public service for grouping treatment
  * 
  * @param Zend_Db_Select $select
  */
 public static function traitGroup(Zend_Db_Select $select)
 {
     // not needed for MySQL backends
     if ($select->getAdapter() instanceof Zend_Db_Adapter_Pdo_Mysql) {
         return;
     }
     $group = $select->getPart(Zend_Db_Select::GROUP);
     if (empty($group)) {
         return;
     }
     $columns = $select->getPart(Zend_Db_Select::COLUMNS);
     $updatedColumns = array();
     //$column is an array where 0 is table, 1 is field and 2 is alias
     foreach ($columns as $key => $column) {
         if ($column[1] instanceof Zend_Db_Expr) {
             if (preg_match('/^\\(.*\\)/', $column[1])) {
                 $updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $column[1] . ")"), $column[2]);
             } else {
                 $updatedColumns[] = $column;
             }
             continue;
         }
         if (preg_match('/^\\(.*\\)/', $column[1])) {
             $updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $column[1] . ")"), $column[2]);
             continue;
         }
         // resolve * to single columns
         if ($column[1] == '*') {
             $tableFields = Tinebase_Db_Table::getTableDescriptionFromCache(SQL_TABLE_PREFIX . $column[0], $select->getAdapter());
             foreach ($tableFields as $columnName => $schema) {
                 // adds columns into group by clause (table.field)
                 // checks if field has a function (that must be an aggregation)
                 $fieldName = "{$column[0]}.{$columnName}";
                 if (in_array($fieldName, $group)) {
                     $updatedColumns[] = array($column[0], $fieldName, $columnName);
                 } else {
                     // any selected field which is not in the group by clause must have an aggregate function
                     // we choose MIN() as default. In practice the affected columns will have only one value anyways.
                     $updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $select->getAdapter()->quoteIdentifier($fieldName) . ")"), $columnName);
                 }
             }
             continue;
         }
         $fieldName = $column[0] . '.' . $column[1];
         if (in_array($fieldName, $group)) {
             $updatedColumns[] = $column;
         } else {
             // any selected field which is not in the group by clause must have an aggregate function
             // we choose MIN() as default. In practice the affected columns will have only one value anyways.
             $updatedColumns[] = array($column[0], new Zend_Db_Expr("MIN(" . $select->getAdapter()->quoteIdentifier($fieldName) . ")"), $column[2] ? $column[2] : $column[1]);
         }
     }
     $select->reset(Zend_Db_Select::COLUMNS);
     foreach ($updatedColumns as $column) {
         $select->columns(!empty($column[2]) ? array($column[2] => $column[1]) : $column[1], $column[0]);
     }
     // add order by columns to group by
     $order = $select->getPart(Zend_Db_Select::ORDER);
     foreach ($order as $column) {
         $field = $column[0];
         if (preg_match('/.*\\..*/', $field) && !in_array($field, $group)) {
             // adds column into group by clause (table.field)
             $group[] = $field;
         }
     }
     $select->reset(Zend_Db_Select::GROUP);
     $select->group($group);
 }

 /**
  * Creates the base model.
  *
  * @return \MUtil_Model_SelectModel
  */
 protected function createModel()
 {
     $groupby['period_1'] = new \Zend_Db_Expr("YEAR({$this->dateFrom})");
     $date = new \MUtil_Date();
     switch ($this->dateType) {
         case 'D':
             $keyCount = 1;
             $groupby['period_1'] = new \Zend_Db_Expr("CONVERT({$this->dateFrom}, DATE)");
             $date->setTime(0);
             $date->addDay($this->dateFactor - $this->dateRange);
             $start = $date->getIso();
             for ($i = -$this->dateRange; $i <= $this->dateRange; $i++) {
                 if (0 == $i) {
                     $this->dateCurrentStart = clone $date;
                     $this->dateCurrentEnd = clone $date;
                     $this->dateCurrentEnd->setTimeToDayEnd();
                 }
                 $values = array();
                 $values['period_1'] = $date->get('yyyy-MM-dd');
                 $values['range'] = $i;
                 $requiredRows[$i] = $values;
                 $date->addDay(1);
             }
             $date->subSecond(1);
             $end = $date->getIso();
             break;
         case 'W':
             $keyCount = 2;
             // Use MONDAY as start of week
             $groupby['period_1'] = new \Zend_Db_Expr("substr(YEARWEEK(gto_valid_from, 3),1,4)");
             //$groupby['period_1'] = new \Zend_Db_Expr("YEAR($this->dateFrom) - CASE WHEN WEEK($this->dateFrom, 1) = 0 THEN 1 ELSE 0 END");
             $groupby['period_2'] = new \Zend_Db_Expr("WEEK({$this->dateFrom}, 3)");
             $date->setWeekday(1);
             $date->setTime(0);
             $date->addWeek($this->dateFactor - $this->dateRange);
             $start = $date->getIso();
             for ($i = -$this->dateRange; $i <= $this->dateRange; $i++) {
                 if (0 == $i) {
                     $this->dateCurrentStart = clone $date;
                     $this->dateCurrentEnd = clone $date;
                     $this->dateCurrentEnd->addWeek(1)->subSecond(1);
                 }
                 $values = array();
                 $values['period_1'] = $date->get(\Zend_Date::YEAR);
                 $values['period_2'] = $date->get(\Zend_Date::WEEK);
                 // When monday is in the previous year, add one to the year
                 if ($date->get(\Zend_Date::DAY_OF_YEAR) > 14 && $date->get(\Zend_Date::WEEK) == 1) {
                     $values['period_1'] = $values['period_1'] + 1;
                 }
                 $values['range'] = $i;
                 $requiredRows[$i] = $values;
                 $date->addWeek(1);
             }
             $date->subSecond(1);
             $end = $date->getIso();
             break;
         case 'M':
             $keyCount = 2;
             $groupby['period_2'] = new \Zend_Db_Expr("MONTH({$this->dateFrom})");
             $date->setDay(1);
             $date->setTime(0);
             $date->addMonth($this->dateFactor - $this->dateRange);
             $start = $date->getIso();
             for ($i = -$this->dateRange; $i <= $this->dateRange; $i++) {
                 if (0 == $i) {
                     $this->dateCurrentStart = clone $date;
                     $this->dateCurrentEnd = clone $date;
                     $this->dateCurrentEnd->addMonth(1)->subSecond(1);
                 }
                 $values = array();
                 $values['period_1'] = $date->get(\Zend_Date::YEAR);
                 $values['period_2'] = $date->get(\Zend_Date::MONTH);
                 $values['range'] = $i;
                 $requiredRows[$i] = $values;
                 $date->addMonth(1);
             }
             $date->subSecond(1);
             $end = $date->getIso();
             break;
         case 'Y':
             $keyCount = 1;
             $date->setDay(1);
             $date->setMonth(1);
             $date->setTime(0);
             $date->addYear($this->dateFactor - $this->dateRange);
             $start = $date->getIso();
             for ($i = -$this->dateRange; $i <= $this->dateRange; $i++) {
                 if (0 == $i) {
                     $this->dateCurrentStart = clone $date;
                     $this->dateCurrentEnd = clone $date;
                     $this->dateCurrentEnd->addYear(1)->subSecond(1);
                 }
                 $values = array();
                 $values['period_1'] = $date->get(\Zend_Date::YEAR);
                 $values['range'] = $i;
//.........这里部分代码省略.........

 protected function _addLabelConcat(Zend_Db_Select $sql, $alias = null)
 {
     $alias = $alias ?: $this->getTableName();
     // have to have array() as the last param or issue_id will get
     // overwritten with a 0 if there are no issues to join
     $sql->joinLeft(array('ill' => 'issue_label_linker'), "{$alias}.issue_id = ill.issue_id", array());
     $sql->columns(array('labels' => 'GROUP_CONCAT(DISTINCT ill.label_id SEPARATOR \' \')'));
     $sql->group($alias . '.issue_id');
     return $sql;
 }

 /**
  * Устанавтивает группировку в запрос
  * @param Zend_Db_Select $select
  * @param string $conditions
  */
 protected function setGroupToSelect(Zend_Db_Select $select, $group)
 {
     if ($group = trim($group)) {
         $select->group($group);
     }
 }

 /**
  * Padrão de Colunas para o relatório de Beneficiários 
  * 
  * @access protected
  * @param Zend_Db_Select $select
  * @return void
  */
 protected function _columnsDefault(Zend_Db_Select $select)
 {
     $select->reset(Zend_Db_Select::COLUMNS);
     $select->columns(array('FEFOP_Contract.id_fefop_contract', 'FEFOP_Contract.num_district', 'FEFOP_Contract.num_program', 'FEFOP_Contract.num_module', 'AddDistrict.id_adddistrict', 'AddDistrict.District', 's.status_description', 'id_perdata' => 'b.id', 'b.code', 'b.name', 'target' => new Zend_Db_Expr("CASE WHEN b.target = 1 THEN 'Sin' ELSE 'Lae' END"), 'module' => new Zend_Db_Expr("CONCAT(FEFOP_Modules.acronym, ' - ', FEFOP_Modules.description)"), 'program' => new Zend_Db_Expr("CONCAT(FEFOP_Programs.acronym, ' - ', FEFOP_Programs.description)"), 'cod_contract' => new Zend_Db_Expr("CONCAT(FEFOP_Contract.num_program, '-', FEFOP_Contract.num_module, '-', FEFOP_Contract.num_district, '-', FEFOP_Contract.num_year, '-', FEFOP_Contract.num_sequence)"), 'disability' => new Zend_Db_Expr('(' . $this->_columnDisability() . ')'), 'gender' => new Zend_Db_Expr('(' . $this->_columnGender() . ')'), 'amount_contracted' => new Zend_Db_Expr('(' . $this->_columnAmountContracted() . ')'), 'amount_payment' => new Zend_Db_Expr('(' . $this->_columnAmouontPayment() . ')'), 'amount_real' => new Zend_Db_Expr('(' . $this->_columnAmountReal() . ')'), 'amount_addcosts' => new Zend_Db_Expr('(' . $this->_columnAdditional() . ')')));
     $select->group(array('FEFOP_Contract.id_fefop_contract'));
 }

 /**
  * @return WeFlex_Db_Model
  */
 public function group($group)
 {
     $this->_selector->group($group);
     return $this;
 }

//.........这里部分代码省略.........
                 break;
             case Condition::MODE_AND:
             default:
                 $this->_select->where($statement);
                 break;
         }
     }
     // Adjust query based on returnCount
     if ($returnCount) {
         if (is_array($returnCount)) {
             if ($subOp) {
             } else {
                 // return count on grouped columns
                 foreach ($returnCount as $key => $property) {
                     $fieldmodifier = null;
                     if ($this->_mapper) {
                         $class = $property->getParent() ? $property->getParent()->getId() : $collection->getDataObject()->getClass();
                         $field = $this->_mapper->propertyToDatastoreName($class, $property->getId());
                     } else {
                         $field = $property->getId();
                     }
                     if ($property instanceof ObjectProperty) {
                         // join with $key if necessary
                         if (strstr($key, '.') !== false) {
                             $leftPart = substr($key, 0, strpos($key, '.'));
                             $intermediateProp = $collection->getDataObject()->getProperty($leftPart);
                             $fieldmodifier = $this->_join($intermediateProp, $table) . '.' . $field;
                         }
                     }
                     // limit date grouping to date part, omitting possible hour part
                     if ($property instanceof DateProperty) {
                         $fieldmodifier = "DATE({$field})";
                     }
                     $this->_select->group($fieldmodifier ? $fieldmodifier : $field);
                     $this->_select->columns(array($field => $fieldmodifier ? $fieldmodifier : $field));
                 }
             }
         } else {
             $this->_select->reset('group');
         }
     } else {
         $this->_select->limit($collection->getBoundaryBatch() != -1 ? $collection->getBoundaryBatch() : null, $collection->getBoundaryOffset());
         /**
          * Sorting part
          */
         foreach ($collection->getSortings() as $sorting) {
             $slUniqext = $slTable = null;
             // Specific cases first
             // @todo find a better way to sort on meta properties
             if ($sorting[0]->getId() == ObjectUri::IDENTIFIER || $sorting[0] instanceof MetaProperty) {
                 $id = Backend::DEFAULT_PKEY;
                 $this->_select->order(new \Zend_Db_Expr($table . '.' . $id . ' ' . $sorting[1]));
                 continue;
             } else {
                 if ($sorting[0] instanceof Property\CollectionProperty) {
                     // handling of conditions based on collection limited to withMembers() and withoutMembers()
                     $leftkey = $sorting[0]->getParameter('keyprop');
                     //$field = $property->getId();
                     $subSelect = $this->_ressource->select();
                     $subseltbl = $this->_mapper ? $this->_mapper->getDatastore($sorting[0]->getParameter('instanceof')) : $this->_getTableFromClass($sorting[0]->getParameter('instanceof'));
                     $subSelect->from($subseltbl, new \Zend_Db_Expr(sprintf("COUNT(%s)", $leftkey)));
                     $join = sprintf("%s.%s = %s", $subseltbl, $leftkey, $pkey);
                     $subSelect->where($join);
                     // $statement = $this->_buildConditionStatement(new \Zend_Db_Expr(sprintf("(%s)", $subSelect)), $condition->getClauses(), $conditionArray[1]);
                     $this->_select->order(new \Zend_Db_Expr('(' . $subSelect->__toString() . ') ' . $sorting[1]));
                     continue;