/** * Render sql select conditions * * @return Varien_Data_Collection_Db */ protected function _renderFilters() { if ($this->_isFiltersRendered) { return $this; } $this->_renderFiltersBefore(); foreach ($this->_filters as $filter) { switch ($filter['type']) { case 'or': $condition = $this->_conn->quoteInto($filter['field'] . '=?', $filter['value']); $this->_select->orWhere($condition); break; case 'string': $this->_select->where($filter['value']); break; case 'public': $field = $this->_getMappedField($filter['field']); $condition = $filter['value']; $this->_select->where($this->_getConditionSql($field, $condition), null, Varien_Db_Select::TYPE_CONDITION); break; default: $condition = $this->_conn->quoteInto($filter['field'] . '=?', $filter['value']); $this->_select->where($condition); } } $this->_isFiltersRendered = true; return $this; }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend */ 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); if (in_array($this->_operator, array('in', 'notin')) && !is_array($value)) { $value = explode(' ', $this->_value); } if (in_array($this->_operator, array('equals', 'greater', 'less', 'in', 'notin'))) { $value = str_replace(array('%', '\\_'), '', $value); if (is_array($value) && empty($value)) { $_select->where('1=' . (substr($this->_operator, 0, 3) == 'not' ? '1/* empty query */' : '0/* impossible query */')); } elseif ($this->_operator == 'equals' && ($value === '' || $value === NULL || $value === false)) { $_select->where($field . 'IS NULL'); } else { // finally append query to select object $_select->where($field . $action['sqlop'], $value, $this->valueType); } } else { // finally append query to select object $_select->where($field . $action['sqlop'], $value); } if (in_array($this->_operator, array('not', 'notin')) && $value !== '') { $_select->orWhere($field . ' IS NULL'); } }
/** * 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) { $this->_options['ignoreAcl'] = TRUE; $this->_resolve(); $quotedDisplayContainerIdentifier = $_backend->getAdapter()->quoteIdentifier('attendee.displaycontainer_id'); $_select->where($this->_getQuotedFieldName($_backend) . ' IN (?)', empty($this->_containerIds) ? " " : $this->_containerIds); $_select->orWhere($quotedDisplayContainerIdentifier . ' IN (?)', empty($this->_containerIds) ? " " : $this->_containerIds); }
/** * see where * @return WeFlex_Db_Model */ public function orWhere($conditions) { if (is_array($conditions)) { $conditions = $this->_translateToZendWhere($conditions); } $this->_selector->orWhere($conditions); return $this; }
/** * 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) { $this->_options['ignoreAcl'] = TRUE; $this->_resolve(); $quotedDisplayContainerIdentifier = $_backend->getAdapter()->quoteIdentifier('attendee.displaycontainer_id'); $where = empty($this->_containerIds) ? Tinebase_Backend_Sql_Command::getFalseValue($_backend->getAdapter()) : $_select->getAdapter()->quoteInto($this->_getQuotedFieldName($_backend) . ' IN (?)', $this->_containerIds); $orWhere = empty($this->_containerIds) ? Tinebase_Backend_Sql_Command::getFalseValue($_backend->getAdapter()) : $_select->getAdapter()->quoteInto($quotedDisplayContainerIdentifier . ' IN (?)', $this->_containerIds); $_select->where($where); $_select->orWhere($orWhere); }
/** * Retorna uma query com informações do empreendimentos cadastrados * * @param number $filter * @throws Zend_Db_Exception * @return mixed|boolean */ public function selectQueryList($filter = 1, $like = NULL) { try { $select = new Zend_Db_Select($this->db); $select->from($this->name); $select->where('state=?', $filter); if (!is_null($like)) { $columns = array('nome', 'categoria', 'logradouro', 'incorporadora'); $select->where($columns[0] . ' LIKE ?', '%' . $like . '%'); $select->orWhere($columns[1] . ' LIKE ?', '%' . $like . '%'); $select->orWhere($columns[2] . ' LIKE ?', '%' . $like . '%'); $select->orWhere($columns[3] . ' LIKE ?', '%' . $like . '%'); } $select->order('id DESC'); return $select; } catch (Zend_Db_Exception $e) { throw new Zend_Db_Exception($e->getMessage()); return false; } }
/** * appends sql to given select statement * * @param Zend_Db_Select $_select * @param Tinebase_Backend_Sql_Abstract $_backend * * @todo to be removed once we split filter model / backend */ public function appendFilterSql($_select, $_backend) { $now = new Tinebase_DateTime(); $now->setHour(0)->setMinute(0)->setSecond(0); $db = Tinebase_Core::getDb(); if ($this->_value == 1) { $_select->where($db->quoteInto('(' . $db->quoteIdentifier('employment_end') . ' >= ? ', $now, 'datetime') . ' OR ' . $db->quoteIdentifier('employment_end') . ' IS NULL )'); $_select->where($db->quoteInto('( ' . $db->quoteIdentifier('employment_begin') . ' <= ? ', $now, 'datetime') . ' OR ( ' . $db->quoteIdentifier('employment_begin') . ' IS NULL ))'); } else { $_select->where($db->quoteInto($db->quoteIdentifier('employment_end') . ' < ? ', $now, 'datetime')); $_select->orWhere($db->quoteInto($db->quoteIdentifier('employment_begin') . ' > ? ', $now, 'datetime')); } }
/** * @param number $filterState * @param string $userIds * @throws Zend_Exception */ public function selectByUsersIds($filterState = 1, $ids, $like = NULL) { try { $select = new Zend_Db_Select($this->db); $select->from('' . $this->name . ''); $select->where('state=?', $filterState); $select->where('created_user_id IN ( ' . $ids . ' )'); if (!is_null($like)) { $columns = array('cpf', 'dados_cliente'); $select->where($columns[0] . ' LIKE ?', '%' . $like . '%'); $select->orWhere($columns[1] . ' LIKE ?', '%' . $like . '%'); } $select->order('id DESC'); return $select; } catch (Zend_Exception $e) { throw new Zend_Exception($e->getMessage()); return false; } }
/** * Render sql select conditions * * @return Varien_Data_Collection_Db */ protected function _renderFilters() { if ($this->_isFiltersRendered) { return $this; } foreach ($this->_filters as $filter) { switch ($filter['type']) { case 'or': $condition = $this->_conn->quoteInto($filter['field'] . '=?', $filter['value']); $this->_select->orWhere($condition); break; case 'string': $this->_select->where($filter['value']); break; default: $condition = $this->_conn->quoteInto($filter['field'] . '=?', $filter['value']); $this->_select->where($condition); } } $this->_isFiltersRendered = true; return $this; }
/** * Recupera dados da tabela #__clientes * * @param Zend_DB::FETCH $mode * @return array */ public function selectAll($filterState = 1, $like = NULL) { try { $select = new Zend_Db_Select($this->db); $select->from(array('u' => $this->name), array('id', 'nome', 'email', 'id_perfil', 'acesso', 'state')); $select->joinLeft(array('p' => $this->namePerfis), 'u.id_perfil = p.id', 'role'); $select->where('u.state = ?', $filterState); if (!is_null($like)) { $columns = array('u.nome', 'u.email', 'p.role'); $select->where($columns[0] . ' LIKE ?', '%' . $like . '%'); $select->orWhere($columns[1] . ' LIKE ?', '%' . $like . '%'); $select->orWhere($columns[2] . ' LIKE ?', '%' . $like . '%'); } $select->order('u.id DESC'); return $select; //return $result; } catch (Zend_Db_Adapter_Exception $e) { throw new Zend_Exception($e->getMessage()); } }
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; }
protected function applyWheres(Zend_Db_Select $select, $wheres) { foreach ($wheres as $where) { @(list($condition, $value, $type) = $where); if ($type == 'or') { $select->orWhere($condition, $value); } else { $select->where($condition, $value); } } return $select; }
/** * List Volumes likely to need replacement from age or errors * bconsole -> query -> 16 * @return rows */ function getVolumesNeedReplacement() { $db = Zend_Registry::get('db_bacula'); // make select from multiple tables $select = new Zend_Db_Select($db); $select->from('Media', array("MediaId", 'PoolId', 'StorageId', 'VolumeName', 'VolStatus', 'VolJobs', 'MediaType', 'VolMounts', 'VolErrors', 'VolWrites', 'VolBytes')); $select->orWhere("VolErrors > 0"); $select->orWhere("VolStatus = 'Error'"); $select->orWhere("VolMounts > 50"); // Number of time media mounted $select->orWhere("VolStatus = 'Disabled'"); $select->orWhere("VolWrites > 3999999"); // Number of writes to media $select->order(array('VolStatus ASC', 'VolErrors', 'VolMounts', 'VolumeName DESC')); //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!! $stmt = $select->query(); return $stmt->fetchAll(); }
/** * Add's a new condition to the curretn query * * @param string $filter Filter to apply * @param string $op Condition option * @param array $completeField All fields options * * @return void */ public function addCondition($filter, $op, $completeField) { $op = strtolower($op); $explode = explode('.', $completeField['field']); $field = end($explode); $simpleField = false; $columns = $this->getColumns(); foreach ($columns as $value) { if ($field == $value[2]) { if (is_object($value[1])) { $field = $value[1]->__toString(); $simpleField = true; } else { $field = $value[0] . '.' . $value[1]; } break; } elseif ($field == $value[0]) { $field = $value[0] . '.' . $value[1]; break; } } if (strpos($field, '.') === false && $simpleField === false) { $field = $completeField['field']; } /** * Reserved words from myslq dont contain any special charaters. * But select expressions may. * * SELECT IF(City.Population>500000,1,0).... * * We can not quoteIdentifier this fields... * */ if (strpos($field, '(') !== false) { $field = $this->_getDb()->quoteIdentifier($field); } $func = 'where'; if (strpos($field, '(') !== false) { $func = 'having'; } switch ($op) { case 'sqlexp': $this->_select->{$func}(new Zend_Db_Expr($filter)); break; case 'empty': $this->_select->{$func}($field . " = '' "); break; case 'isnull': $this->_select->{$func}(new Zend_Db_Expr($field . ' IS NULL ')); break; case 'isnnotull': $this->_select->{$func}(new Zend_Db_Expr($field . ' IS NOT NULL ')); break; case 'equal': case '=': $this->_select->{$func}($field . ' = ?', $filter); break; case 'rege': $this->_select->{$func}($field . " REGEXP " . $this->_getDb()->quote($filter)); break; case 'rlike': $this->_select->{$func}($field . " LIKE " . $this->_getDb()->quote($filter . "%")); break; case 'llike': $this->_select->{$func}($field . " LIKE " . $this->_getDb()->quote("%" . $filter)); break; case '>=': $this->_select->{$func}($field . " >= ?", $filter); break; case '>': $this->_select->{$func}($field . " > ?", $filter); break; case '<>': case '!=': $this->_select->{$func}($field . " <> ?", $filter); break; case '<=': $this->_select->{$func}($field . " <= ?", $filter); break; case '<': $this->_select->{$func}($field . " < ?", $filter); break; case 'in': $filter = explode(',', $filter); $this->_select->{$func}($field . " IN (?)", $filter); break; case 'flag': $this->_select->{$func}($field . " & ? <> 0", $filter); break; case '||': $this->_select->orWhere($field . " LIKE " . $this->_getDb()->quote("%" . $filter . "%")); break; case 'range': case '&': case 'and': $start = substr($filter, 0, strpos($filter, '<>')); $end = substr($filter, strpos($filter, '<>') + 2); $this->_select->{$func}($field . " between " . $this->_getDb()->quote($start) . " and " . $this->_getDb()->quote($end)); break; case 'like': default: $this->_select->{$func}($field . " LIKE " . $this->_getDb()->quote("%" . $filter . "%")); break; } }
/** * 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); }
/** * Add's a new condition to the curretn query * * @param string $filter Filter to apply * @param string $op Condition option * @param array $completeField All fields options * * @return void */ public function addCondition($filter, $op, $completeField) { $op = strtolower($op); // TODO $completeField should contain key ($grid->_data['fields'][$key]), // cleaner would be to pass key as parameter and provide access to field definition from grid class $explode = explode('.', $completeField['field']); $field = end($explode); $simpleField = false; // mark the value as Zend_Db_Expr $sqlExpr = false; $columns = $this->getColumns(); // TODO it would be worth to rework this inefficient algorithm foreach ($columns as $value) { if ($field == $value[2]) { if ($value[1] instanceof Zend_Db_Expr) { $sqlExpr = true; $field = $value[1]->__toString(); } elseif (is_object($value[1])) { $field = $value[1]->__toString(); $simpleField = true; } else { $field = $value[0] . '.' . $value[1]; } break; } elseif ($field == $value[0]) { $field = $value[0] . '.' . $value[1]; break; } elseif ($value[1] instanceof Zend_Db_Expr && $completeField['field'] == $value[1]->__toString()) { $sqlExpr = true; $field = $completeField['field']; break; } } if (!$sqlExpr && strpos($field, '.') === false && $simpleField === false) { $field = $completeField['field']; } /** * Reserved words from myslq dont contain any special charaters. * But select expressions may. * * SELECT IF(City.Population>500000,1,0).... * * We can not quoteIdentifier this fields... * */ // TODO expressions should always be passed as Zend_Db_Expr, then this should be not needed if (!$sqlExpr && strpos($field, '(') !== false) { $field = $this->_getDb()->quoteIdentifier($field); } $func = 'where'; // TODO having should be used only on fields declared in GROUP BY part /*if ($sqlExpr || strpos($field, '(') !== false) { $func = 'having'; }*/ switch ($op) { case 'sqlexp': $this->_select->{$func}(new Zend_Db_Expr($filter)); break; case 'empty': $this->_select->{$func}($field . " = '' "); break; case 'isnull': $this->_select->{$func}(new Zend_Db_Expr($field . ' IS NULL ')); break; case 'isnotnull': $this->_select->{$func}(new Zend_Db_Expr($field . ' IS NOT NULL ')); break; case 'equal': case '=': $this->_select->{$func}($field . ' = ?', $filter); break; case 'rege': $this->_select->{$func}($field . " REGEXP " . $this->_getDb()->quote($filter)); break; case 'rlike': $this->_select->{$func}($field . " LIKE " . $this->_getDb()->quote($filter . "%")); break; case 'llike': $this->_select->{$func}($field . " LIKE " . $this->_getDb()->quote("%" . $filter)); break; case '>=': $this->_select->{$func}($field . " >= ?", $filter); break; case '>': $this->_select->{$func}($field . " > ?", $filter); break; case '<>': case '!=': $this->_select->{$func}($field . " <> ?", $filter); break; case '<=': $this->_select->{$func}($field . " <= ?", $filter); break; case '<': $this->_select->{$func}($field . " < ?", $filter); break; case 'in': $filter = explode(',', $filter); $this->_select->{$func}($field . " IN (?)", $filter); break; case 'flag': $this->_select->{$func}($field . " & ? <> 0", $filter); break; case '||': $this->_select->orWhere($field . " LIKE " . $this->_getDb()->quote("%" . $filter . "%")); break; case 'range': case '&': case 'and': $start = substr($filter, 0, strpos($filter, '<>')); $end = substr($filter, strpos($filter, '<>') + 2); $this->_select->{$func}($field . " between " . $this->_getDb()->quote($start) . " and " . $this->_getDb()->quote($end)); break; case 'like': default: $this->_select->{$func}($field . " LIKE " . $this->_getDb()->quote("%" . $filter . "%")); break; } }
/** * Applies the given array of where statements to the given select */ public function applyWhereToSelect($where, Zend_Db_Select $select) { foreach ($where as $field => $value) { if ($value instanceof Zend_Db_Expr && is_int($field)) { $select->where($value); } else { if (is_string($field) && is_array($value)) { // we have an in clause $in = ''; $sep = ''; foreach ($value as $val) { $in .= $sep . $this->proxied->quote($val); $sep = ','; } $fieldVal = new Zend_Db_Expr($field . ' in (' . $in . ')'); $select->where($fieldVal); } else { if (strpos(mb_strtolower($field), 'or ') === 0) { $field = substr($field, 3); $select->orWhere($field . ' ?', $value); } else { $select->where($field . ' ?', $value); } } } } return $select; }