public function reset($part = null) { if (empty($part)) { $parts = array('table', 'quantifier', 'columns', 'joins', 'where', 'group', 'having', 'limit', 'offset', 'order', 'combine'); foreach ($parts as $part) { $this->zendSelect->reset($part); } return null; } return $this->zendSelect->reset($part); }
/** * Setting ordering */ protected function order() { $column = $this->getParamAdapter()->getColumn(); $order = $this->getParamAdapter()->getOrder(); if ($column) { $this->select->reset('order'); $this->select->order($column . ' ' . $order); } }
/** * @testdox unit test: Test reset() resets internal stat of Select object, based on input * @covers Zend\Db\Sql\Select::reset */ public function testReset() { $select = new Select(); // table $select->from('foo'); $this->assertEquals('foo', $select->getRawState(Select::TABLE)); $select->reset(Select::TABLE); $this->assertNull($select->getRawState(Select::TABLE)); // columns $select->columns(array('foo')); $this->assertEquals(array('foo'), $select->getRawState(Select::COLUMNS)); $select->reset(Select::COLUMNS); $this->assertEmpty($select->getRawState(Select::COLUMNS)); // joins $select->join('foo', 'id = boo'); $this->assertEquals(array(array('name' => 'foo', 'on' => 'id = boo', 'columns' => array('*'), 'type' => 'inner')), $select->getRawState(Select::JOINS)); $select->reset(Select::JOINS); $this->assertEmpty($select->getRawState(Select::JOINS)); // where $select->where('foo = bar'); $where1 = $select->getRawState(Select::WHERE); $this->assertEquals(1, $where1->count()); $select->reset(Select::WHERE); $where2 = $select->getRawState(Select::WHERE); $this->assertEquals(0, $where2->count()); $this->assertNotSame($where1, $where2); // group $select->group(array('foo')); $this->assertEquals(array('foo'), $select->getRawState(Select::GROUP)); $select->reset(Select::GROUP); $this->assertEmpty($select->getRawState(Select::GROUP)); // having $select->having('foo = bar'); $having1 = $select->getRawState(Select::HAVING); $this->assertEquals(1, $having1->count()); $select->reset(Select::HAVING); $having2 = $select->getRawState(Select::HAVING); $this->assertEquals(0, $having2->count()); $this->assertNotSame($having1, $having2); // limit $select->limit(5); $this->assertEquals(5, $select->getRawState(Select::LIMIT)); $select->reset(Select::LIMIT); $this->assertNull($select->getRawState(Select::LIMIT)); // offset $select->offset(10); $this->assertEquals(10, $select->getRawState(Select::OFFSET)); $select->reset(Select::OFFSET); $this->assertNull($select->getRawState(Select::OFFSET)); // order $select->order('foo asc'); $this->assertEquals(array('foo asc'), $select->getRawState(Select::ORDER)); $select->reset(Select::ORDER); $this->assertNull($select->getRawState(Select::ORDER)); }
/** * Sets sort order of database query * * @param Select $select * @param string|array $sort * @return Select */ public function setSortOrder(Select $select, $sort) { if ($sort === '' || null === $sort || empty($sort)) { return $select; } $select->reset('order'); if (is_string($sort)) { $sort = explode(' ', $sort); } $order = []; foreach ($sort as $column) { if (strchr($column, '-')) { $column = substr($column, 1, strlen($column)); $direction = Select::ORDER_DESCENDING; } else { $direction = Select::ORDER_ASCENDING; } $order[] = $column . ' ' . $direction; } return $select->order($order); }
public function applyParamsToTableEntriesSelect(array $params, Select $select, array $schema, $hasActiveColumn = false) { $tableName = $this->getTable(); if (isset($params['group_by'])) { $select->group($tableName . '.' . $params['group_by']); } else { $select->group($tableName . '.' . $this->primaryKeyFieldName); } //If this is a relational order, than it is an array. if (is_array($params['orderBy'])) { $select->join(array('jsort' => $params['orderBy']['junction_table']), 'jsort.' . $params['orderBy']['jkeyRight'] . ' = ' . $tableName . '.' . $this->primaryKeyFieldName, array(), $select::JOIN_LEFT); $select->join(array('rsort' => $params['orderBy']['related_table']), 'rsort.id = jsort.' . $params['orderBy']['jkeyLeft'], array(), $select::JOIN_LEFT); $select->order('rsort.title', $params['orderDirection']); } else { $select->order(implode(' ', array($params['orderBy'], $params['orderDirection']))); } if (isset($params['perPage']) && isset($params['currentPage'])) { $select->limit($params['perPage'])->offset($params['currentPage'] * $params['perPage']); } // Are we sorting on a relationship? foreach ($schema as $column) { if ($column['column_name'] != $params['orderBy']) { continue; } // Must have defined table_related if (!isset($column['relationship']) || !is_array($column['relationship']) || !isset($column['relationship']['table_related'])) { break; } // Must have defined visible_column if (!isset($column['options']) || !is_array($column['options']) || !isset($column['options']['visible_column'])) { break; } $relatedTable = $column['relationship']['table_related']; $visibleColumn = $column['options']['visible_column']; $keyLeft = $params['table_name'] . "." . $params['orderBy']; // @todo it's wrong to assume PKs are "id" but this is currently endemic to directus6 $keyRight = $relatedTable . ".id"; $joinedSortColumn = $relatedTable . "." . $visibleColumn; $select->reset(Select::ORDER)->join($relatedTable, "{$keyLeft} = {$keyRight}", array(), Select::JOIN_LEFT)->order("{$joinedSortColumn} " . $params['orderDirection']); break; } // Note: be sure to explicitly check for null, because the value may be // '0' or 0, which is meaningful. if (null !== $params[STATUS_COLUMN_NAME] && $hasActiveColumn) { $haystack = is_array($params[STATUS_COLUMN_NAME]) ? $params[STATUS_COLUMN_NAME] : explode(",", $params[STATUS_COLUMN_NAME]); if (!isset($params['table_name']) || empty($params['table_name'])) { $tableName = $this->getTable(); } else { $tableName = $params['table_name']; } $select->where->in($tableName . '.' . STATUS_COLUMN_NAME, $haystack); } // Select only ids from the ids if provided if (array_key_exists('ids', $params)) { $entriesIds = array_filter(explode(',', $params['ids']), 'is_numeric'); if (count($entriesIds) > 0) { $select->where->in($this->getTable() . '.' . $this->primaryKeyFieldName, $entriesIds); } } // Where $select->where->nest->expression('-1 = ?', $params[$this->primaryKeyFieldName])->or->equalTo($tableName . '.' . $this->primaryKeyFieldName, $params[$this->primaryKeyFieldName])->unnest; // very very rudimentary ability to supply where conditions to fetch... // at the moment, only 'equalTo' and 'between' are supported... also, the 'total' key returned // in the json does not reflect these filters... // -MG if (array_key_exists('where', $params)) { $outer = $select->where->nest; foreach ($params['where'] as $whereCond) { $type = $whereCond['type']; $column = $whereCond['column']; if ($type == 'equalTo') { $val = $whereCond['val']; if (is_array($val)) { $where = $select->where->nest; foreach ($val as $currentval) { $where->equalTo($column, $currentval); if ($currentval != end($val)) { $where->or; } } $where->unnest; } else { $outer->equalTo($column, $val); } } else { if ($type == 'between') { $val1 = $whereCond['val1']; $val2 = $whereCond['val2']; $outer->between($column, $val1, $val2); } } } $outer->unnest; } //@TODO: Make this better if (isset($params['adv_where'])) { $select->where($params['adv_where']); } if (isset($params['adv_search']) && !empty($params['adv_search'])) { $i = 0; foreach ($params['adv_search'] as $search_col) { $target = array(); foreach ($schema as $col) { if ($col['id'] == $search_col['id']) { $target = $col; break; } } if (empty($target)) { continue; } // TODO: fix this, it must be refactored if (isset($target['relationship']) && $target['relationship']['type'] == "MANYTOMANY") { $relatedTable = $target['relationship']['table_related']; $relatedAliasName = $relatedTable . "_" . $i; if ($target['relationship']['type'] == "MANYTOMANY") { $junctionTable = $target['relationship']['junction_table']; $jkl = $target['relationship']['junction_key_left']; $jkr = $target['relationship']['junction_key_right']; $keyleft = $params['table_name'] . ".id"; $keyRight = $junctionTable . '.' . $jkl; $jkeyleft = $junctionTable . '.' . $jkr; $jkeyright = $relatedAliasName . ".id"; $select->join($junctionTable, "{$keyleft} = {$keyRight}", array(), Select::JOIN_INNER); } else { $select->join(array($relatedAliasName => $relatedTable), $tableName . '.' . $target['column_name'] . " = " . $relatedAliasName . ".id", array(), Select::JOIN_INNER); } $relatedTableMetadata = TableSchema::getSchemaArray($relatedTable); if ($search_col['type'] == "like") { $select->join(array($relatedAliasName => $relatedTable), "{$jkeyleft} = {$jkeyright}", array(), Select::JOIN_INNER); $search_col['value'] = "%" . $search_col['value'] . "%"; if (isset($target['options']['filter_column'])) { $targetCol = $target['options']['filter_column']; } else { $targetCol = $target['options']['visible_column']; } foreach ($relatedTableMetadata as $col) { if ($col['id'] == $targetCol) { if ($col['type'] == 'VARCHAR' || $col['type'] == 'INT') { $where = $select->where->nest; $columnName = $this->adapter->platform->quoteIdentifier($col['column_name']); $columnName = $relatedAliasName . "." . $columnName; $like = new Predicate\Expression("LOWER({$columnName}) LIKE ?", $search_col['value']); $where->addPredicate($like, Predicate\Predicate::OP_OR); $where->unnest; } } } } else { $select->where($jkeyleft . ' = ' . $this->adapter->platform->quoteValue($search_col['value'])); } } elseif (isset($target['relationship']) && $target['relationship']['type'] == "MANYTOONE") { $relatedTable = $target['relationship']['table_related']; $keyLeft = $this->getTable() . "." . $target['relationship']['junction_key_left']; $keyRight = $relatedTable . ".id"; $filterColumn = $target['options']['filter_column']; $joinedFilterColumn = $relatedTable . "." . $filterColumn; // do not let join this table twice // TODO: do a extra checking in case it's being used twice // and none for sorting if ($target['column_name'] != $params['orderBy']) { $select->join($relatedTable, "{$keyLeft} = {$keyRight}", array(), Select::JOIN_LEFT); } if ($search_col['type'] == 'like') { $searchLike = '%' . $search_col['value'] . '%'; $spec = function (Where $where) use($joinedFilterColumn, $searchLike) { $where->like($joinedFilterColumn, $searchLike); }; $select->where($spec); } else { $select->where($search_col['id'] . " " . $search_col['type'] . " " . $this->adapter->platform->quoteValue($search_col['value'])); } } else { if ($target['type'] == "DATETIME" && strpos($search_col['value'], " ") == false) { $select->where('date(' . $tableName . '.' . $search_col['id'] . ") = " . $this->adapter->platform->quoteValue($search_col['value'])); } else { if ($search_col['type'] == "like") { $select->where($tableName . '.' . $search_col['id'] . " " . $search_col['type'] . " " . $this->adapter->platform->quoteValue("%" . $search_col['value'] . "%")); } else { $select->where($tableName . '.' . $search_col['id'] . " " . $search_col['type'] . " " . $this->adapter->platform->quoteValue($search_col['value'])); } } } $i++; } } else { if (isset($params['search']) && !empty($params['search'])) { $params['search'] = "%" . $params['search'] . "%"; $where = $select->where->nest; foreach ($schema as $col) { if ($col['type'] == 'VARCHAR' || $col['type'] == 'INT') { $columnName = $this->adapter->platform->quoteIdentifier($col['column_name']); $like = new Predicate\Expression("LOWER({$columnName}) LIKE ?", strtolower($params['search'])); $where->addPredicate($like, Predicate\Predicate::OP_OR); } } $where->unnest; } } return $select; }
/** * @param string $part * @return Select * @throws Exception\InvalidArgumentException */ public function reset($part) { switch ($part) { case self::TABLE: if ($this->tableReadOnly) { throw new Exception\InvalidArgumentException('Since this object was created with a table and/or schema in the constructor, it is read only.'); } $this->table = null; break; case self::WITHINGROUPORDER: $this->withinGroupOrder = array(); break; case self::OPTION: $this->option = array(); break; case self::ORDER: $this->order = array(); break; default: parent::reset($part); } return $this; }
/** * Sets sort order of database query * * @param Select $select * @param string|array $sort * @return Select */ public function setSortOrder(Select $select, $sort) { if ($sort === '' || null === $sort || empty($sort)) { return $select; } $select->reset('order'); if (is_string($sort)) { $sort = explode(' ', $sort); } $order = []; foreach ($sort as $column) { if (strchr($column, '-')) { $column = substr($column, 1, strlen($column)); $direction = Select::ORDER_DESCENDING; } else { $direction = Select::ORDER_ASCENDING; } // COLLATE NOCASE // fix the sort order to make case insensitive for sqlite database. if ('sqlite' == $this->getAdapter()->getPlatform()->getName()) { $direction = 'COLLATE NOCASE ' . $direction; } $order[] = $column . ' ' . $direction; } return $select->order($order); }
/** * Common operations for string/number/date filter functions * * This method determines the table and column to search and adds them to * $select if necessary. * * @param \Zend\Db\Sql\Select $select Object to apply the filter to * @param string $model Model class (without namespace) containing property * @param string $property Property to search in * @param bool $addSearchColumns Add columns with search criteria * @return array Table gateway and column of search criteria */ protected function _filter($select, $model, $property, $addSearchColumns) { // Determine table name and column alias switch ($model) { case 'Client': $table = 'Clients'; $hydrator = $this->_serviceLocator->get('Database\\Table\\Clients')->getHydrator(); $column = $hydrator->extractName($property); $columnAlias = $column; break; case 'CustomFields': $table = 'CustomFields'; $column = $this->_serviceLocator->get('Model\\Client\\CustomFieldManager')->getColumnMap()[$property]; $columnAlias = 'customfields_' . $column; $fk = 'hardware_id'; break; case 'Registry': $table = 'RegistryData'; $column = 'regvalue'; $columnAlias = 'registry_content'; $select->where(array('registry.name' => $property)); $fk = 'hardware_id'; break; case 'Windows': $table = 'WindowsInstallations'; $hydrator = $this->_serviceLocator->get('Database\\Table\\WindowsInstallations')->getHydrator(); $column = $hydrator->extractName($property); $columnAlias = 'windows_' . $column; $fk = 'client_id'; break; default: $tableGateway = $this->_serviceLocator->get('Model\\Client\\ItemManager')->getTable($model); $column = $tableGateway->getHydrator()->extractName($property); $columnAlias = strtolower($model) . '_' . $column; $fk = 'hardware_id'; } if (!isset($tableGateway)) { $tableGateway = $this->_serviceLocator->get("Database\\Table\\{$table}"); } $table = $tableGateway->getTable(); if ($table == 'clients') { if ($addSearchColumns) { // Add column if not already present with the same alias $columns = $select->getRawState(Select::COLUMNS); if (@$columns[$columnAlias] != $column) { $columns[$columnAlias] = $column; $select->columns($columns); } } } else { // Join table if not already present $rewriteJoins = false; $joinedTables = $select->getRawState(Select::JOINS)->getJoins(); $tablePresent = false; foreach ($joinedTables as $joinedTable) { if ($joinedTable['name'] == $table) { $tablePresent = true; break; } } if (!$tablePresent) { $rewriteJoins = true; $joinedTable = array('name' => $table, 'on' => "{$table}.{$fk} = clients.id", 'columns' => array(), 'type' => Select::JOIN_INNER); } // Add column if not already present with the same alias if ($addSearchColumns and @$joinedTable['columns'][$columnAlias] != $column) { $rewriteJoins = true; $joinedTable['columns'][$columnAlias] = $column; } // Rewrite joins if ($rewriteJoins) { $select->reset(Select::JOINS); if (!$tablePresent) { $joinedTables[] = $joinedTable; } foreach ($joinedTables as $table) { if ($table['name'] == $joinedTable['name']) { // Existing spec is out of date for updated tables. // Always replace with new spec. $table = $joinedTable; } $select->join($table['name'], $table['on'], $table['columns'], $table['type']); } } } return array($tableGateway, $column); }
protected function fetchCount(Select $select) { $select->limit(1); $select->offset(null); //NOTE: no method could reset order here //$select->order(array()); $select->reset('order'); $countColumnName = self::ROW_COUNT_COLUMN; if ($this->primaryKey && is_string($this->primaryKey)) { $select->columns(array($countColumnName => new Expression("COUNT({$this->primaryKey})"))); } else { $select->columns(array($countColumnName => new Expression('COUNT(*)'))); } //p($select->getSqlString()); $resultSet = $this->selectWith($select); if (false === $this->enableCount) { $this->lastSelectString = $select->getSqlString(); $this->reset(); } if (!$resultSet) { return 0; } $resultSet = $resultSet->current(); return $this->lastSelectCount = $resultSet->{$countColumnName}; }