/** * 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) { // quote field identifier, set action and replace wildcards $field = $this->_getQuotedFieldName($_backend); $action = $this->_opSqlMap[$this->_operator]; $value = $this->_replaceWildcards($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); } if (is_array($value) && empty($value)) { $_select->where('1=' . (substr($this->_operator, 0, 3) == 'not' ? '1/* empty query */' : '0/* impossible query */')); return; } $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', 'contains', 'startswith', 'endswith', 'in')) && $value === '') { $where = "( {$where} OR {$field} IS NULL)"; } // finally append query to select object $_select->where($where); }
/** * 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; $requiredRows[$i] = $values; $date->addYear(1); } $date->subSecond(1); $end = $date->getIso(); break; default: throw new \Gems_Exception_Coding('Incorrect date_type value: ' . $this->dateType); } $where = "{$this->dateFrom} BETWEEN '{$start}' AND '{$end}'"; for ($i = -$this->dateRange; $i <= $this->dateRange; $i++) { $requiredRows[$i]['date_factor'] = $this->dateFactor + $i; $requiredRows[$i]['df_link'] = null; $requiredRows[$i]['df_label'] = null; } if ($this->dateRange > 0) { $requiredRows[-$this->dateRange]['df_link'] = $this->dateFactor - $this->dateRange * 2; $requiredRows[-$this->dateRange]['df_label'] = $this->_('<<'); $requiredRows[$this->dateRange]['df_link'] = $this->dateFactor + $this->dateRange * 2; $requiredRows[$this->dateRange]['df_label'] = $this->_('>>'); if ($this->dateRange > 1) { $i = intval($this->dateRange / 2); $requiredRows[-$i]['df_link'] = $this->dateFactor - 1; $requiredRows[-$i]['df_label'] = $this->_('<'); $requiredRows[$i]['df_link'] = $this->dateFactor + 1; $requiredRows[$i]['df_label'] = $this->_('>'); } $requiredRows[0]['df_link'] = $this->dateFactor ? '0' : null; $requiredRows[0]['df_label'] = $this->_('Now!'); } if ($this->dateFactor) { $today = new \MUtil_Date(); $this->dateFactorChanges['D'] = $this->dateCurrentStart->diffDays($today); $this->dateFactorChanges['W'] = $this->dateCurrentStart->diffWeeks($today); $this->dateFactorChanges['M'] = $this->dateCurrentStart->diffMonths($today); $this->dateFactorChanges['Y'] = $this->dateCurrentStart->diffYears($today); } else { $this->dateFactorChanges = array_fill_keys(array('D', 'W', 'M', 'Y'), 0); } // \MUtil_Echo::track($requiredRows); // \MUtil_Echo::rs($start, $end, $where); $select = new \Zend_Db_Select($this->db); $select->from($this->dataTableName, $groupby + $this->getDbFields()); $select->where($where); $select->group($groupby); $this->processSelect($select); // \MUtil_Echo::r((string) $select); $model = new \MUtil_Model_SelectModel($select, $this->dataTableName); // Display by column cannot use formatFunction as it is a simple repeater // $model->set('duration_avg', 'formatFunction', $this->util->getLocalized()->formatNumber); $transformer = new \MUtil_Model_Transform_RequiredRowsTransformer(); $transformer->setDefaultRow($this->getDefaultRow()); $transformer->setRequiredRows($requiredRows); $transformer->setKeyItemCount($keyCount); $model->addTransformer($transformer); return $model; }
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; }
/** * Returns an array of objects queried from the given t41_Object_Collection instance parameters * * The given collection is populated if it comes empty of members. * * In any other case, this method doesn't directly populate the collection. This action is under the responsability of * the caller. For example, the t41_Object_Collection::find() method takes care of it. * * @param t41\ObjectModel\Collection $collection * @param boolean|array $returnCount true = counting, array = stats on listed properties * @param string $subOp complex operation like SUM or AVG * @return array */ public function find(ObjectModel\Collection $collection, $returnCount = false, $subOp = null) { $this->_class = $class = $collection->getDataObject()->getClass(); $table = $this->_getTableFromClass($class); if (!$table) { throw new Exception('MISSING_DBTABLE_PARAM'); } // primary key is either part of the mapper configuration or 'id' $pkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : \t41\Backend::DEFAULT_PKEY; if (is_array($pkey)) { $composite = array(); /* @var $obj t41\Backend\Key */ foreach ($pkey as $obj) { $composite[] = sprintf('TRIM(%s)', $table . '.' . $obj->getName()); $composite[] = Backend\Mapper::VALUES_SEPARATOR; } $pkey = sprintf("CONCAT(%s) AS %s", implode(',', $composite), Backend::DEFAULT_PKEY); } else { $pkey = $table . '.' . $pkey; } $this->_connect(); /* @var $select \Zend_Db_Select */ $this->_select = $this->_ressource->select(); // detect if query is of stat-kind if ($returnCount) { switch ($subOp) { case ObjectModel::CALC_SUM: $expressions = array(); foreach ($returnCount as $propKey => $property) { $prop = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $propKey) : $propKey; $expressions[] = sprintf('SUM(%s.%s)', $table, $prop); } $subOpExpr = implode('+', $expressions); break; case ObjectModel::CALC_AVG: $subOpExpr = sprintf('AVG(%s)', $returnCount); break; default: $subOpExpr = 'COUNT(*)'; break; } $this->_select->from($table, new \Zend_Db_Expr($subOpExpr . " AS " . \t41\Backend::MAX_ROWS_IDENTIFIER)); } else { $this->_select->distinct(); $this->_select->from($table, $pkey); } $this->_alreadyJoined = array(); /* @var $condition t41\Backend\Condition */ foreach ($collection->getConditions() as $conditionArray) { // combo conditions if ($conditionArray[0] instanceof Condition\Combo) { $statement = array(); foreach ($conditionArray[0]->getConditions() as $condition) { $statement[] = $this->_parseCondition($condition[0], $this->_select, $table); } $statement = implode(' OR ', $statement); switch ($conditionArray[1]) { case Condition::MODE_OR: $this->_select->orWhere($statement); break; case Condition::MODE_AND: default: $this->_select->where($statement); break; } continue; } // optional table where the column may be $jtable = ''; // condition object is in the first key $condition = $conditionArray[0]; /* does condition contain another condition object ? */ if ($condition->isRecursive()) { while ($condition->isRecursive()) { $property = $condition->getProperty(); $parent = $property->getParent() ? $property->getParent()->getId() : $table; $condition = $condition->getCondition(); if ($jtable) { $parentTable = $jtable; } else { if ($parent) { $parentTable = $this->_mapper ? $this->_mapper->getDatastore($parent) : $parent; } else { $parentTable = $table; } } $jtable = $this->_mapper ? $this->_mapper->getDatastore($property->getParameter('instanceof')) : $this->_getTableFromClass($property->getParameter('instanceof')); /* column name in left table */ $jlkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $property->getId()) : $property->getId(); $uniqext = $jtable . '__joined_for__' . $jlkey; if (in_array($uniqext, $this->_alreadyJoined)) { $class = $property->getParameter('instanceof'); $jtable = $uniqext; continue; } /* pkey name in joined table */ $jpkey = $this->_mapper ? $this->_mapper->getPrimaryKey($property->getParameter('instanceof')) : Backend::DEFAULT_PKEY; $join = sprintf("%s.%s = %s.%s", $parentTable, $jlkey, $uniqext, $jpkey); $this->_select->joinLeft($jtable . " AS {$uniqext}", $join, array()); $this->_alreadyJoined[$jtable] = $uniqext; //$jtable; $jtable = $uniqext; $class = $property->getParameter('instanceof'); } } $property = $condition->getProperty(); if ($property instanceof Property\ObjectProperty) { // no join if object is stored in a different backend ! // @todo improve this part if (ObjectModel::getObjectBackend($property->getParameter('instanceof'))->getAlias() != $this->_uri->getAlias()) { $clauses = $condition->getClauses(); if ($clauses[0]['value'] != Condition::NO_VALUE) { $clauses[0]['operator'] = Condition::OPERATOR_ENDSWITH | Condition::OPERATOR_EQUAL; $condition->setClauses($clauses); } $field = $this->_mapper ? $this->_mapper->propertyToDatastoreName($this->_class, $property->getId()) : $property->getId(); } else { // which table to join with ? (in case of condition is last element of a recursion) $jtable2 = $jtable ? $jtable : $table; $jtable = $this->_mapper ? $this->_mapper->getDatastore($property->getParameter('instanceof')) : $this->_getTableFromClass($property->getParameter('instanceof')); $leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $property->getId()) : $property->getId(); $field = $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($property->getParameter('instanceof')) : Backend::DEFAULT_PKEY; $uniqext = $jtable . '__joined_for__' . $leftkey; if (!in_array($uniqext, $this->_alreadyJoined)) { $join = sprintf("%s.%s = %s.%s", $jtable2, $leftkey, $uniqext, is_array($rightkey) ? $rightkey[0] : $rightkey); $this->_select->joinLeft($jtable . " AS {$uniqext}", $join, array()); $this->_alreadyJoined[$jtable] = $uniqext; } $jtable = $uniqext; } } else { if ($property instanceof Property\CollectionProperty) { // handling of conditions based on collection limited to withMembers() and withoutMembers() $leftkey = $property->getParameter('keyprop'); $field = $property->getId(); $subSelect = $this->_ressource->select(); $subseltbl = $this->_mapper ? $this->_mapper->getDatastore($property->getParameter('instanceof')) : $this->_getTableFromClass($property->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->where($statement); continue; } else { $field = $property->getId(); if ($this->_mapper) { $field = $this->_mapper->propertyToDatastoreName($class, $field); } } } /* convert identifier tag to the valid primary key */ if ($field == ObjectUri::IDENTIFIER) { // @todo handle multiple keys from mapper $field = $table . '.'; $key = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY; $field .= is_array($key) ? $key[0] : $key; } else { if ($jtable) { if (array_key_exists($jtable, $this->_alreadyJoined)) { $field = $this->_alreadyJoined[$jtable] . '.' . $field; } else { $tmp = $jtable . '.'; $tmp .= is_array($field) ? $field[0] : $field; $field = $tmp; } } else { if (array_key_exists($table, $this->_alreadyJoined)) { $field = $this->_alreadyJoined[$table] . '.' . $field; } else { $field = $table . '.' . $field; } } } if ($field instanceof Key) { $field = $table . '.' . $field->getName(); } // protect DateProperty() with setted timepart parameter from misuse if ($property instanceof DateProperty && $property->getParameter('timepart') == true) { $field = "DATE({$field})"; } $statement = $this->_buildConditionStatement($field, $condition->getClauses(), $conditionArray[1]); switch ($conditionArray[1]) { case Condition::MODE_OR: $this->_select->orWhere($statement); 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; } else { if ($sorting[0] instanceof Property\ObjectProperty) { // find which property to sort by if ($sorting[0]->getParameter('sorting')) { $sprops = array_keys($sorting[0]->getParameter('sorting')); } else { // try to sort with properties used to display value if (substr($sorting[0]->getParameter('display'), 0, 1) == '[') { // @todo extract elements of pattern to order from them ? $sprops = array('id'); } else { $sprops = explode(',', $sorting[0]->getParameter('display')); } } // sorting property belongs to a second-level join if ($sorting[0]->getParent()->getClass() != $collection->getClass()) { $leftkey = 'commande'; //$this->_mapper ? $this->_mapper->propertyToDatastoreName($collection->getDataObject()->getClass(), $sorting[0]->getParent()getId()) : $sorting[0]->getId(); $class = $sorting[0]->getParent()->getClass(); $stable = $this->_getTableFromClass($class); $sbackend = ObjectModel::getObjectBackend($class); // Property to sort from is in a different backend from current one if ($sbackend->getAlias() != $this->getAlias()) { // We presume that the current backend is allowed to connect to the remote one // Should we raise an exception instead ? $stable = $sbackend->getUri()->getDatabase() . '.' . $stable; } $field = $sorting[0]->getId(); $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY; $uniqext = $stable . '__joined_for__' . $leftkey; if (!in_array($uniqext, $this->_alreadyJoined)) { if (is_array($rightkey)) { foreach ($rightkey as $rightkeyObj) { $join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkeyObj->getName()); } } else { $join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkey); } $this->_select->joinLeft("{$stable} AS {$uniqext}", $join, array()); $this->_alreadyJoined[$stable] = $uniqext; } $slTable = $this->_getTableFromClass($sorting[0]->getParameter('instanceof')); $slUniqext = $uniqext; } $leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($collection->getDataObject()->getClass(), $sorting[0]->getId()) : $sorting[0]->getId(); $class = $sorting[0]->getParameter('instanceof'); $stable = isset($slTable) ? $slTable : $this->_getTableFromClass($class); $sbackend = ObjectModel::getObjectBackend($class); // Property to sort from is in a different backend from current one if ($sbackend->getAlias() != $this->getAlias()) { // We presume that the current backend is allowed to connect to the remote one // Should we raise an exception instead ? $stable = $sbackend->getUri()->getDatabase() . '.' . $stable; } $field = $sorting[0]->getId(); $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY; $uniqext = $stable . '__joined_for__' . $leftkey; if (!in_array($uniqext, $this->_alreadyJoined)) { if (is_array($rightkey)) { foreach ($rightkey as $rightkeyObj) { $join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkeyObj->getName()); } } else { $join = sprintf("%s.%s = %s.%s", isset($slUniqext) ? $slUniqext : $table, $leftkey, $uniqext, $rightkey); } $this->_select->joinLeft("{$stable} AS {$uniqext}", $join, array()); $this->_alreadyJoined[$stable] = $uniqext; } foreach ($sprops as $sprop) { if ($this->_mapper) { $sfield = $this->_mapper->propertyToDatastoreName($class, $sprop); } else { $sfield = $sprop; } $sortingExpr = $this->_alreadyJoined[$stable] . '.' . $sfield; if (isset($sorting[2]) && !empty($sorting[2])) { $sortingExpr = sprintf('%s(%s)', $sorting[2], $sortingExpr); } $this->_select->order(new \Zend_Db_Expr($sortingExpr . ' ' . $sorting[1])); } continue; } } } // default sorting on a different table $class = $sorting[0]->getParent() ? $sorting[0]->getParent()->getClass() : $collection->getDataObject()->getClass(); $stable = $this->_getTableFromClass($class); if ($this->_mapper) { $sfield = $this->_mapper->propertyToDatastoreName($class, $sorting[0]->getId()); } else { $field = $sorting[0]; $sfield = $field->getId(); } // add a left join if the sorting field belongs to a table not yet part of the query if ($stable != $table) { // get the property id from the class name $tfield = isset($sorting[3]) ? $sorting[3] : $collection->getDataObject()->getObjectPropertyId($class); $leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $tfield) : $tfield; $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($field->getParameter('instanceof')) : Backend::DEFAULT_PKEY; $uniqext = $stable . '__joined_for__' . $leftkey; if (!in_array($uniqext, $this->_alreadyJoined)) { $join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkey); $this->_select->joinLeft("{$stable} AS {$uniqext}", $join, array()); $this->_alreadyJoined[$stable] = $uniqext; } $sortingExpr = $this->_alreadyJoined[$stable] . '.' . $sfield; } else { $sortingExpr = $stable . '.' . $sfield; } if (isset($sorting[2]) && !empty($sorting[2])) { $sortingExpr = sprintf('%s(%s)', $sorting[2], $sortingExpr); } $this->_select->order(new \Zend_Db_Expr('TRIM(' . $sortingExpr . ') ' . $sorting[1])); } } $result = array(); $context = array('table' => $table); try { if (true && $returnCount == false) { $this->_select->columns($this->_getColumns($collection->getDataObject())); } $result = $this->_ressource->fetchAll($this->_select); } catch (\Zend_Db_Exception $e) { $context['error'] = $e->getMessage(); $this->_setLastQuery($this->_select->__toString(), $this->_select->getPart('where'), $context); return false; } $this->_setLastQuery($this->_select->__toString(), $this->_select->getPart('where'), $context); if ($returnCount !== false) { return is_array($returnCount) ? $result : $result[0][Backend::MAX_ROWS_IDENTIFIER]; } // convert array of primary keys to strings foreach ($result as $key => $val) { // $result[$key] = implode(Backend\Mapper::VALUES_SEPARATOR, $val); } /* prepare base of object uri */ $uri = new ObjectModel\ObjectUri(); $uri->setBackendUri($this->_uri); $uri->setClass($collection->getDataObject()->getClass()); $uri->setUrl($this->_database . '/' . $table . '/'); return $collection->populate($result, $uri); //return $this->_populateCollection($result, $collection, $uri); }
/** * * Public service for grouping treatment * @param Zend_Db_Adapter $adapter * @param string $tablePrefix * @param Zend_Db_Select $select */ public static function traitGroup($adapter, $tablePrefix, Zend_Db_Select $select) { $group = $select->getPart(Zend_Db_Select::GROUP); //if (empty($group)) return; $order = $select->getPart(Zend_Db_Select::ORDER); $columns = $select->getPart(Zend_Db_Select::COLUMNS); try { //$column is an array where 0 is table, 1 is field and 2 is alias foreach ($columns as $column) { $field = implode('.', $column); if (!in_array($field, $group)) { // replaces * by each name of column if ($column[1] == '*') { $tableFields = $adapter->describeTable($tablePrefix . $column[0]); foreach ($tableFields as $columnName => $schema) { // adds columns into group by clause (table.field) // checks if field has a function (that must be an aggregation) $element = "{$column[0]}.{$columnName}"; if (!in_array($element, $group) && !preg_match('/\\(.*\\)/', $element)) { $group[] = $element; } } } else { // adds column into group by clause (table.field) $element = "{$column[0]}.{$column[1]}"; if (!preg_match('/\\(.*\\)/', $element)) { $group[] = $element; } else { if (substr($column[1], 0, 10) == "(CASE WHEN") { $group[] = $column[1]; } } } } } 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); } catch (Exception $e) { Tinebase_Core::getLogger()->debug(__METHOD__ . '::' . __LINE__ . ' Exception: ' . $e->getMessage() . ' Trace: ' . $e->getTraceAsString()); } return $select; }