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};
}