/** * Render sql select orders * * @return Varien_Data_Collection_Db */ protected function _renderOrders() { $ordersInSelect = $this->_select->getPart(Zend_Db_Select::ORDER); foreach ($this->_orders as $orderExpr) { if (!in_array($orderExpr, $ordersInSelect)) { $this->_select->order($orderExpr); } } return $this; }
/** * Setzt die Headerdaten einsprachig entsprechend des * internen Select-Objekts * */ public function setDefaultHeaderdata() { $selectpart = $this->select->getPart(Zend_DB_Select::COLUMNS); for ($i = 0; $i < count($selectpart); $i++) { if (($columnname = $selectpart[$i][1]) != '*') { $defaultHeaderData[] = array('raw' => $columnname, 'lang_xx' => $columnname, 'lang_de' => $columnname, 'lang_en' => $columnname); } else { throw new Exception('Das Select-Objekt enthält keine Spaltennamen. Es kann kein Default-Header gesetzt werden.'); } } $this->header = $defaultHeaderData; }
/** * * @param \Zend_Db_Select $select * @param string $name Optiona name */ public function __construct(\Zend_Db_Select $select, $name = null) { $this->_select = $select; // Make sure the columns are known to the model foreach ($select->getPart(\Zend_Db_Select::COLUMNS) as $column) { if (isset($column[2])) { $this->set($column[2]); } elseif (is_string($column[1])) { $this->set($column[1]); } } if (null === $name) { $name = 'rnd' . rand(10000, 999999); } parent::__construct($name); }
/** * Returns a specific record given it's offset/index * @param integer $offset Offset of record to fetch */ public function offsetGet($offset) { // PDO does not support scrollable cursor for MySQL, so we can't use the cursor // and offset parameters of _statement->fetch(). // Therefore, our only real option is to put a limit on the list temporarily and // then reset it. $previousLimitCount = $this->_select->getPart(Zend_Db_Select::LIMIT_COUNT); $previousLimitOffset = $this->_select->getPart(Zend_Db_Select::LIMIT_OFFSET); $this->limit(1, $offset); $this->_execute(); $item = $this->_statement->fetch(); $this->reset(Zend_Db_Select::LIMIT_COUNT); $this->reset(Zend_Db_Select::LIMIT_OFFSET); $this->limit($previousLimitCount, $previousLimitOffset); if ($item) { $class = $this->_class; return new $class($item, $this->tableName(), $this->_db); } return false; }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend * @throws Tinebase_Exception_AccessDenied */ public function appendFilterSql($_select, $_backend) { $db = $_backend->getAdapter(); if ($this->_requiredGrants === NULL) { throw new Tinebase_Exception_AccessDenied('No grants have been defined.'); } // it searches select columns // return an array where each element has 3 items: 0 - table, 1 - field/expression, 2 - alias $columns = $_select->getPart(Zend_Db_Select::COLUMNS); // it gets an array with expressions used in having clause $grants = array(); foreach ($columns as $column) { if (!empty($column[2])) { $grants[$column[2]] = $column[1]; } } // it uses into having clause expressions instead of aliases foreach ($this->_requiredGrants as $grant) { $_select->orHaving($db->quoteInto($db->quoteIdentifier($grants[$grant]) . ' = ?', 1, Zend_Db::INT_TYPE)); } }
/** * Constructor. * * @param Zend_Db_Select $select The select query */ public function __construct(Zend_Db_Select $select, $sphinxQuery, $sphinxTableName = null) { $this->_select = $select; $this->_sphinxQuery = $sphinxQuery; $from = $select->getPart(Zend_Db_Select::FROM); if (null !== $sphinxTableName) { $this->_sphinxTableName = $sphinxTableName; } else { foreach ($from as $table => $opts) { if (strpos($table, 'sphinx') !== false) { $this->_sphinxTableName = $table; break; } } if (null === $this->_sphinxTableName) { throw new Zend_Paginator_Exception('Could not detect sphinx table name in select'); } } if (!array_key_exists($this->_sphinxTableName, $from)) { throw new Zend_Paginator_Exception('Select must contain a table/alias "' . $this->_sphinxTableName . '"'); } }
/** * Join the specified groupscatalog index table to the passed select instance * * @param Zend_Db_Select $select * @param string $table The groupscatalog index table * @param int $groupId * @param int $storeId * @param string $entityField The entity table column where the product or category id is stored * @return void */ protected function _addGroupsCatalogFilterToSelect(Zend_Db_Select $select, $table, $groupId, $storeId, $entityField = 'e.entity_id') { // NOTE to self: // Using joinTable() seems to trigger an exception for some users that I can't reproduce (so far). // It is related to the flat catalog (Mage_Catalog_Model_Resource_Category_Flat_Collection missing // joinTable()). Using getSelect()->joinInner() to work around this issue. /* $collection->joinTable( $helper->getIndexTableByEntityType($entityType), // table "catalog_entity_id=entity_id", // primary bind array('group_id' => 'group_id', 'store_id' => 'store_id'), // alias to field mappings for the bind cond. array( // additional bind conditions (see mappings above) 'group_id' => $groupId, 'store_id' => $collection->getStoreId(), ), 'inner' // join type ); */ // Avoid double joins for the wishlist collection. // They clone and clear() the collection each time, but the joins on the // collections select objects persist. This is more reliable then setting // a flag on the collection object. foreach ($select->getPart(Zend_Db_Select::FROM) as $joinedTable) { if ($joinedTable['tableName'] == $table) { // filter join already applied return; } } $select->joinInner($table, "{$table}.catalog_entity_id={$entityField} AND " . $this->_getReadAdapter()->quoteInto("{$table}.group_id=? AND ", $groupId) . $this->_getReadAdapter()->quoteInto("{$table}.store_id=?", $storeId), array()); }
protected static final function _buildPerPageCacheKey(Zend_Db_Select $select) { $cacheKey = sha1(serialize(array($select->getPart(Zend_Db_Select::COLUMNS), $select->getPart(Zend_Db_Select::FROM), $select->getPart(Zend_Db_Select::WHERE), $select->getPart(Zend_Db_Select::GROUP), $select->getPart(Zend_Db_Select::HAVING), $select->getPart(Zend_Db_Select::ORDER)))); return $cacheKey; }
protected function _prepareSelect(Zend_Db_Select $select) { $fromPart = $select->getPart(Zend_Db_Select::FROM); // the recipient table is required! if (!isset($fromPart['recipient'])) { return false; } foreach ($this->_where as $where) { $select->where($where); } $select->columns($this->_columns, 'recipient'); return true; }
/** * Get Select object (Zend_Db_Select) for sorting table records * * @param Zend_Db_Select $select * @param string $order * * @return Zend_Db_Select */ public static function GetSelectForSort($select, $options) { $aliasTable = ''; $order = $options['order']; //-------------------------- $arrOrder = explode('.', $order); // Если в параметре сортировки не задан псевдоним таблицы // то определим его, и если нужно присоединим, // соответствующую таблицу if (count($arrOrder) == 1) { $joinTableForSort = $options['joinTableForSort']; if ($joinTableForSort) { // Определим какие таблицы уже присоединены $fromTables = $select->getPart(Zend_Db_Select::FROM); foreach ($fromTables as $alias => $joinParams) { // Если таблица -> $joinTableForSort уже присоединена // то получим ее псевдоним if ($joinParams['tableName'] == $joinTableForSort) { $aliasTable = $alias; } } if ($aliasTable) { $order = $aliasTable . '.' . $order; } else { // Получим поле сортировки $arrOrder = explode(' ', trim($order)); $field = $arrOrder[0]; $order = 'l.' . $field . ' ' . $arrOrder[1]; } } else { $order = 'l.' . $order; } } $select->order($order); return $select; }
/** * WARNING: Only columns copying supported. * @param Lib_Model_Db_Mysql $model * @param string|null $thisKeyCol may contain table prefix or not * @param string|null $thatKeyCol may contain table prefix or not * @param string $conditions * @return $this */ public function joinFrom($model, $thisKeyCol, $thatKeyCol = null, $conditions = '') { if ($thatKeyCol === null) { debug_assert($thisKeyCol !== null); $thatKeyCol = $model->getPrimaryKey(); } elseif ($thisKeyCol === null) { debug_assert($thatKeyCol !== null); $thisKeyCol = $this->getPrimaryKey(); } $this->prefixColumn($model, $thatKeyCol); $this->prefixColumn($this, $thisKeyCol); $conditions = str_replace('{that}', $model->getAlias(), $conditions); $conditions = str_replace('{this}', $this->getAlias(), $conditions); $this->mapPartWhere($this->addAliasToConditionDg()); $model->mapPartWhere($model->addAliasToConditionDg()); $thisFrom = $this->_select->getPart(Zend_Db_Select::FROM); $modelColumns = array_chain($model->_select->getPart(Zend_Db_Select::COLUMNS), array_group_dg(array_get_dg(return_dg(0))), array_map_val_dg(array_chain_dg(array_map_val_dg(function ($descriptor) { return null === $descriptor[2] ? $descriptor[1] : [$descriptor[2] => $descriptor[1]]; }), function ($columns) { $outArray = []; array_map_val($columns, function ($column) use(&$outArray) { if (is_array($column)) { array_map_val($column, function ($column, $alias) use(&$outArray) { $outArray[$alias] = $column; }); } else { $outArray[] = $column; } }); return $outArray; }))); array_each($model->_select->getPart(Zend_Db_Select::FROM), function ($descriptor, $alias) use($modelColumns, $thisFrom, $model, $thisKeyCol, $thatKeyCol, $conditions) { debug_enforce(!array_key_exists($alias, $thisFrom), "Alias `{$alias}` already used for table `{$descriptor['tableName']}`"); switch ($descriptor['joinType']) { case Zend_Db_Select::FROM: $this->_select->joinLeft([$model->getAlias() => $model->getTable()], "{$thisKeyCol}={$thatKeyCol} " . $conditions, array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']); break; case Zend_Db_Select::INNER_JOIN: $this->_select->joinInner([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']); break; case Zend_Db_Select::LEFT_JOIN: $this->_select->joinLeft([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']); break; case Zend_Db_Select::RIGHT_JOIN: $this->_select->joinRight([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']); break; case Zend_Db_Select::FULL_JOIN: $this->_select->joinFull([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']); break; case Zend_Db_Select::CROSS_JOIN: $this->_select->joinCross([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']); break; case Zend_Db_Select::NATURAL_JOIN: $this->_select->joinNatural([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']); break; default: debug_assert(false, "Unknown join type " . var_dump_human_compact($descriptor['joinType'])); break; } }); $this->settingsJoin($model); return $this; }
protected function _modifySearchQuery(Zend_Db_Select &$select, $model) { $where = $select->getPart(Zend_Db_Select::WHERE); if (!$where) { return; } $select->reset(Zend_Db_Select::WHERE); foreach ($where as $clause) { // Check if it's a search query if (stripos($clause, 'like') !== false) { preg_match('/%.*?%/', $clause, $matches); if (!empty($matches[0])) { $clause = $this->_cleanClause($clause); $clause .= ' OR ' . $this->_joinCmsSearchQuery($model, $select, $matches[0]); } } // re-attach clause $whereBoolType = $this->_determineAndOrOr($clause); $clause = preg_replace('/(^OR|^AND)/', '', $clause); $clause = $this->_cleanClause($clause); if ($whereBoolType === 'OR') { $select->orWhere($clause); continue; } $select->where($clause); } }
/** * Extracts the current table's alias from a composed * query. * * @param Zend_Db_Select $select * @access protected * @return string */ protected function _extractTableAlias($select) { $parts = $select->getPart('from'); foreach ($parts as $alias => $part) { if ($part['tableName'] == $this->_name) { return $alias; } } return $this->_name; }
function combineSubSelectIfWhereIsNotEmpty(Zend_Db_Select $select, Zend_Db_Select $subSelect) { $whereArray = $subSelect->getPart(Zend_Db_Select::WHERE); if (count($whereArray)) { return $select->orWhere(implode(' ', $whereArray)); } return $select; }
/** * @param Zend_Db_Select $select * @return string */ protected function _getMainTableAliasFromSelect(Zend_Db_Select $select) { $tables = $select->getPart(Zend_Db_Select::FROM); return array_key_exists('main_table', $tables) ? 'main_table' : 'e'; }
/** * 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); }
/** * Modify the order clause to reflect the record's weight * @param Zend_Db_Select $select */ public function addOrderClause(Zend_Db_Select &$select) { // Distill the WHERE class from the Select object $where = $select->getPart(Zend_Db_Select::WHERE); // Save the existing ORDER clause. $originalOrder = $select->getPart(Zend_Db_Select::ORDER); $select->reset(Zend_Db_Select::ORDER); $alias = ''; if ($this->_modelAlias) { $alias = $this->_modelAlias . '.'; } /** * If a registered foreign key (see self::_relationConfig) is found, this query is * considered to be a related fetch() command, and an ORDER BY clause is added with * the registered weight column. */ foreach ($where as $w) { foreach ($this->_relationConfig as $model => $modelRelationConfig) { if (strpos($w, $modelRelationConfig[self::FOREIGN_KEY_COLUMN_KEY]) !== false) { $select->order($alias . $modelRelationConfig[self::WEIGHT_COLUMN_KEY] . ' DESC'); } } } // Return the existing ORDER clause, only this time '<weight-column> DESC' will be in front of it foreach ($originalOrder as $order) { // [0] = column, [1] = direction if (is_array($order)) { $order = $order[0] . ' ' . $order[1]; } $select->order($order); } }
/** * Get an alias for a table in a database query * * @param Zend_Db_Select $select * @param string $table * * @return string */ static function getAliasForTable($select, $table) { $aliasTable = ''; //--------------- $fromTables = $select->getPart(Zend_Db_Select::FROM); foreach ($fromTables as $alias => $joinParams) { // Если таблица -> $joinTableForSort уже присоединена // то получим ее псевдоним if ($joinParams['tableName'] == $table) { $aliasTable = $alias; } } return $aliasTable; }
/** * add to/cc/bcc and flags custom filters * * @param Zend_Db_Select $_select * @param Felamimail_Backend_Cache_Sql_Message $_backend * @param array $_filterData * @return void */ protected function _addRecipientAndFlagsSql($_select, $_backend, $_filterData) { $db = $_backend->getAdapter(); $foreignTables = $_backend->getForeignTables(); // add conditions $tablename = $foreignTables[$_filterData['field']]['table']; if ($_filterData['field'] !== 'flags') { $fieldName = $tablename . '.name'; $fieldEmail = $tablename . '.email'; } // add filter value if (!is_array($_filterData['value'])) { $value = '%' . $_filterData['value'] . '%'; } else { $value = array(); foreach ((array) $_filterData['value'] as $customValue) { $value[] = '%' . $customValue . '%'; } } if ($_filterData['field'] == 'flags') { $columns = $_select->getPart(Zend_Db_Select::COLUMNS); $flags = ''; foreach ($columns as $column) { if ($column[2] == 'flags') { $flags = '(' . $column[1] . ')'; break; } } if ($_filterData['operator'] == 'equals' || $_filterData['operator'] == 'contains') { $_select->having($db->quoteInto($flags . ' LIKE ?', $value)); } else { if ($_filterData['operator'] == 'in' || $_filterData['operator'] == 'notin') { if (empty($value)) { $whereString = $flags . ' IS NULL'; } else { $value = (array) $value; $where = array(); $op = $_filterData['operator'] == 'in' ? 'LIKE' : 'NOT LIKE'; $opImplode = $_filterData['operator'] == 'in' ? ' OR ' : ' AND '; foreach ($value as $flag) { $where[] = $db->quoteInto($flags . ' ' . $op . ' ?', $flag); } $whereString = implode($opImplode, $where); if ($_filterData['operator'] == 'notin') { $whereString = '(' . $whereString . ') OR ' . $flags . ' IS NULL'; } } $_select->having($whereString); } else { $_select->having($db->quoteInto($flags . ' NOT LIKE ? OR ' . $flags . ' IS NULL', $value)); } } } else { $_select->where($db->quoteInto($fieldName . ' LIKE ?', $value) . ' OR ' . $db->quoteInto($fieldEmail . ' LIKE ?', $value)); } }
/** * * 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; }
/** * Get Select object (Zend_Db_Select) for sorting table records * * @param Zend_Db_Select $select * @param array $options * * @return Zend_Db_Select */ public static function GetSelectForSort($select, $options) { $aliasTable = ''; $order = $options['order']; //-------------------------- $arrOrder = explode('.', $order); // Если в параметре сортировки не задан псевдоним таблицы // то определим его, и если нужно присоединим, // соответствующую таблицу if (count($arrOrder) == 1) { $joinTableForSort = $options['joinTableForSort']; if ($joinTableForSort) { // Определим какие таблицы уже присоединены $fromTables = $select->getPart(Zend_Db_Select::FROM); foreach ($fromTables as $alias => $joinParams) { // Если таблица -> $joinTableForSort уже присоединена // то получим ее псевдоним if ($joinParams['tableName'] == $joinTableForSort) { $aliasTable = $alias; } } if ($aliasTable) { $order = $aliasTable . '.' . $order; } else { // Получим поле сортировки $arrOrder = explode(' ', trim($order)); $field = $arrOrder[0]; // Присоединим таблицу $joinTable = $joinTableForSort; switch ($joinTable) { case 'blog_info_profile': $select->joinInner(array('i_profile' => $joinTable), 'i_profile.info_id = i.id', array())->where('i_profile.profile_key = ?', $field); $order = 'i_profile.profile_value ' . $arrOrder[1]; break; default: $order = 'i.' . $field . ' ' . $arrOrder[1]; break; } } } else { $order = 'i.' . $order; } } $select->order($order); return $select; }
/** * 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); }
/** * Get Select object (Zend_Db_Select) for sorting table records * * @param Zend_Db_Select $select * @param string $order * * @return Zend_Db_Select */ public static function GetSelectForSort($select, $options) { $aliasTable = ''; $order = $options['order']; //-------------------------- $arrOrder = explode('.', $order); // Если в параметре сортировки не задан псевдоним таблицы // то определим его, и если нужно присоединим, // соответствующую таблицу if (count($arrOrder) == 1) { $joinTableForSort = $options['joinTableForSort']; if ($joinTableForSort) { // Определим какие таблицы уже присоединены $fromTables = $select->getPart(Zend_Db_Select::FROM); foreach ($fromTables as $alias => $joinParams) { // Если таблица -> $joinTableForSort уже присоединена // то получим ее псевдоним if ($joinParams['tableName'] == $joinTableForSort) { $aliasTable = $alias; } } if ($aliasTable) { $order = $aliasTable . '.' . $order; } else { // Получим поле сортировки $arrOrder = explode(' ', trim($order)); $field = $arrOrder[0]; // Присоединим таблицу $joinTable = $joinTableForSort; switch ($joinTable) { case 'blog_posts_profile': $select->joinInner(array('p_profile' => $joinTable), 'p_profile.post_id = p.id', array())->where('p_profile.profile_key = ?', $field); $order = 'p_profile.profile_value ' . $arrOrder[1]; break; case 'blog_posts_images': $select->joinInner(array('img' => $joinTable), 'img.post_id = p.id', array()); $order = 'img.' . $field . ' ' . $arrOrder[1]; break; case 'blog_posts_audio': $select->joinInner(array('au' => $joinTable), 'au.post_id = p.id', array()); $order = 'au.' . $field . ' ' . $arrOrder[1]; break; case 'blog_posts_video': $select->joinInner(array('v' => $joinTable), 'v.post_id = p.id', array()); $order = 'v.' . $field . ' ' . $arrOrder[1]; break; case 'blog_posts_lacations': $select->joinInner(array('l' => $joinTable), 'l.post_id = p.id', array()); $order = 'l.' . $field . ' ' . $arrOrder[1]; break; case 'blog_posts_tags': $select->joinInner(array('t' => $joinTable), 't.post_id = p.id', array()); $order = 't.' . $field . ' ' . $arrOrder[1]; break; case 'blog_posts_comments': $select->joinInner(array('c' => $joinTable), 'c.post_id = p.id', array()); $order = 'c.' . $field . ' ' . $arrOrder[1]; break; case 'users': $select->joinInner(array('u' => $joinTable), 'u.id = p.user_id', array()); $order = 'u.' . $field . ' ' . $arrOrder[1]; break; case 'users_profile': $select->joinInner(array('u_profile' => $joinTable), 'u_profile.user_id = p.user_id', array())->where('u_profile.profile_key = ?', $field); $order = 'u_profile.profile_value ' . $arrOrder[1]; break; default: $order = 'p.' . $field . ' ' . $arrOrder[1]; break; } } } else { $order = 'p.' . $order; } } $select->order($order); return $select; }
public static function fetchAll(Zend_Db_Select $select, &$count = null) { self::init(); $result = false; try { $limit = $select->getPart(Zend_Db_Select::LIMIT_COUNT); $offset = $select->getPart(Zend_Db_Select::LIMIT_OFFSET); $result = $select->query()->fetchAll(); if ($count !== null) { $count = self::getCount($select); } } catch (Exception $e) { $errors = PR_Api_Error::getInstance(); $errors->addError(7, 'SQL Error: ' . $select . " " . $e); error_log($e); $result = false; } return $result; }
/** * Returns current columns in use * * @return Zend_Db_Select */ public function getColumns() { return $this->_select->getPart('columns'); }
/** * Retrieve ID field from entity collection * * @param Varien_Data_Collection_Db $collection * @return string */ public function getTableAlias(Zend_Db_Select $select) { foreach ($select->getPart(Zend_Db_Select::FROM) as $alias => $table) { if ($table['joinType'] === Zend_Db_Select::FROM) { return $alias; } } return null; }
protected function _prepareSelect(Zend_Db_Select $select) { $fromPart = $select->getPart(Zend_Db_Select::FROM); // the recipient table is required! if (!isset($fromPart['recipient'])) { return false; } foreach ($this->_where as $where) { $select->where($where); } /* $select->where('`recipient`.`sent_at` IS NOT NULL'); if($this->_campaignFilter !== null) { $select->where('`recipient`.`campaign_id` = ?', $this->_campaignFilter); } if($this->_variationFilter !== null) { $select->where('`recipient`.`variation_id` = ?', $this->_variationFilter); } */ $select->columns($this->_columns, 'recipient'); return true; }