From 0116b568bfb1ac3aaeaa4ba2a226e274a27fbdf6 Mon Sep 17 00:00:00 2001 From: Andrey Kutejko Date: Mon, 30 Sep 2013 22:38:35 +0300 Subject: [PATCH] no limit subqueries --- ipf/orm/query.php | 188 +--------------------------------------------- 1 file changed, 1 insertion(+), 187 deletions(-) diff --git a/ipf/orm/query.php b/ipf/orm/query.php index 3bbea7b..7a8a410 100644 --- a/ipf/orm/query.php +++ b/ipf/orm/query.php @@ -56,7 +56,6 @@ class IPF_ORM_Query extends IPF_ORM_Query_Abstract implements Countable, Seriali protected $_subqueryAliases = array(); protected $_aggregateAliasMap = array(); protected $_pendingAggregates = array(); - protected $_needsSubquery = false; protected $_isSubquery; protected $_neededTables = array(); protected $_pendingSubqueries = array(); @@ -79,7 +78,6 @@ class IPF_ORM_Query extends IPF_ORM_Query_Abstract implements Countable, Seriali $this->_neededTables = array(); $this->_expressionMap = array(); $this->_subqueryAliases = array(); - $this->_needsSubquery = false; } public function createSubquery() @@ -748,16 +746,10 @@ class IPF_ORM_Query extends IPF_ORM_Query_Abstract implements Countable, Seriali return false; } - $needsSubQuery = false; - $subquery = ''; $map = reset($this->_queryComponents); $table = $map['table']; $rootAlias = key($this->_queryComponents); - if (!empty($this->_sqlParts['limit']) && $this->_needsSubquery) { - $needsSubQuery = true; - } - $sql = array(); if ( ! empty($this->_pendingFields)) { foreach ($this->_queryComponents as $alias => $map) { @@ -792,197 +784,22 @@ class IPF_ORM_Query extends IPF_ORM_Query_Abstract implements Countable, Seriali } } - $modifyLimit = true; - if ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) { - if ($needsSubQuery) { - $subquery = $this->getLimitSubquery(); - // what about composite keys? - $idColumnName = $table->getColumnName($table->getIdentifier()); - switch (strtolower($this->_conn->getDriverName())) { - case 'Mysql': - // mysql doesn't support LIMIT in subqueries - $list = $this->_conn->execute($subquery, $params)->fetchAll(IPF_ORM::FETCH_COLUMN); - $subquery = implode(', ', array_map(array($this->_conn, 'quote'), $list)); - break; - case 'Pgsql': - // pgsql needs special nested LIMIT subquery - $subquery = 'SELECT ipf_orm_subquery_alias.' . $idColumnName . ' FROM (' . $subquery . ') AS ipf_orm_subquery_alias'; - break; - } - - $field = $this->getSqlTableAlias($rootAlias) . '.' . $idColumnName; - - // only append the subquery if it actually contains something - if ($subquery !== '') { - array_unshift($this->_sqlParts['where'], $this->_conn->quoteIdentifier($field) . ' IN (' . $subquery . ')'); - } - - $modifyLimit = false; - } - } - $q .= ( ! empty($this->_sqlParts['where']))? ' WHERE ' . implode(' AND ', $this->_sqlParts['where']) : ''; $q .= ( ! empty($this->_sqlParts['groupby']))? ' GROUP BY ' . implode(', ', $this->_sqlParts['groupby']) : ''; $q .= ( ! empty($this->_sqlParts['having']))? ' HAVING ' . implode(' AND ', $this->_sqlParts['having']): ''; $q .= ( ! empty($this->_sqlParts['orderby']))? ' ORDER BY ' . implode(', ', $this->_sqlParts['orderby']) : ''; - if ($modifyLimit) { - $q = $this->_conn->modifyLimitQuery($q, $this->_sqlParts['limit'], $this->_sqlParts['offset']); - } + $q = $this->_conn->modifyLimitQuery($q, $this->_sqlParts['limit'], $this->_sqlParts['offset']); // return to the previous state if ( ! empty($string)) { array_pop($this->_sqlParts['where']); } - if ($needsSubQuery) { - array_shift($this->_sqlParts['where']); - } $this->_sql = $q; return $q; } - public function getLimitSubquery() - { - $map = reset($this->_queryComponents); - $table = $map['table']; - $componentAlias = key($this->_queryComponents); - - // get short alias - $alias = $this->getTableAlias($componentAlias); - // what about composite keys? - $primaryKey = $alias . '.' . $table->getColumnName($table->getIdentifier()); - - // initialize the base of the subquery - $subquery = 'SELECT DISTINCT ' . $this->_conn->quoteIdentifier($primaryKey); - - $driverName = $this->_conn->getDriverName(); - - // pgsql needs the order by fields to be preserved in select clause - if ($driverName == 'Pgsql') { - foreach ($this->_sqlParts['orderby'] as $part) { - $part = trim($part); - $e = $this->_tokenizer->bracketExplode($part, ' '); - $part = trim($e[0]); - - if (strpos($part, '.') === false) { - continue; - } - - // don't add functions - if (strpos($part, '(') !== false) { - continue; - } - - // don't add primarykey column (its already in the select clause) - if ($part !== $primaryKey) { - $subquery .= ', ' . $part; - } - } - } - - if ($driverName == 'Mysql' || $driverName == 'Pgsql') { - foreach ($this->_expressionMap as $dqlAlias => $expr) { - if (isset($expr[1])) { - $subquery .= ', ' . $expr[0] . ' AS ' . $this->_aggregateAliasMap[$dqlAlias]; - } - } - } - - $subquery .= ' FROM'; - - foreach ($this->_sqlParts['from'] as $part) { - // preserve LEFT JOINs only if needed - if (substr($part, 0, 9) === 'LEFT JOIN') { - $e = explode(' ', $part); - - if (empty($this->_sqlParts['orderby']) && empty($this->_sqlParts['where'])) { - continue; - } - } - - $subquery .= ' ' . $part; - } - - // all conditions must be preserved in subquery - $subquery .= ( ! empty($this->_sqlParts['where']))? ' WHERE ' . implode(' AND ', $this->_sqlParts['where']) : ''; - $subquery .= ( ! empty($this->_sqlParts['groupby']))? ' GROUP BY ' . implode(', ', $this->_sqlParts['groupby']) : ''; - $subquery .= ( ! empty($this->_sqlParts['having']))? ' HAVING ' . implode(' AND ', $this->_sqlParts['having']) : ''; - - $subquery .= ( ! empty($this->_sqlParts['orderby']))? ' ORDER BY ' . implode(', ', $this->_sqlParts['orderby']) : ''; - - // add driver specific limit clause - $subquery = $this->_conn->modifyLimitSubquery($table, $subquery, $this->_sqlParts['limit'], $this->_sqlParts['offset']); - - $parts = $this->_tokenizer->quoteExplode($subquery, ' ', "'", "'"); - - foreach ($parts as $k => $part) { - if (strpos($part, ' ') !== false) { - continue; - } - - $part = str_replace(array('"', "'", '`'), "", $part); - - if ($this->hasSqlTableAlias($part)) { - $parts[$k] = $this->_conn->quoteIdentifier($this->generateNewSqlTableAlias($part)); - continue; - } - - if (strpos($part, '.') === false) { - continue; - } - - preg_match_all("/[a-zA-Z0-9_]+\.[a-z0-9_]+/i", $part, $m); - - foreach ($m[0] as $match) { - $e = explode('.', $match); - - // Rebuild the original part without the newly generate alias and with quoting reapplied - $e2 = array(); - foreach ($e as $k2 => $v2) { - $e2[$k2] = $this->_conn->quoteIdentifier($v2); - } - $match = implode('.', $e2); - - // Generate new table alias - $e[0] = $this->generateNewSqlTableAlias($e[0]); - - // Requote the part with the newly generated alias - foreach ($e as $k2 => $v2) { - $e[$k2] = $this->_conn->quoteIdentifier($v2); - } - - $replace = implode('.' , $e); - - // Replace the original part with the new part with new sql table alias - $parts[$k] = str_replace($match, $replace, $parts[$k]); - } - } - - if ($driverName == 'Mysql' || $driverName == 'Pgsql') { - foreach ($parts as $k => $part) { - if (strpos($part, "'") !== false) { - continue; - } - if (strpos($part, '__') == false) { - continue; - } - - preg_match_all("/[a-zA-Z0-9_]+\_\_[a-z0-9_]+/i", $part, $m); - - foreach ($m[0] as $match) { - $e = explode('__', $match); - $e[0] = $this->generateNewTableAlias($e[0]); - - $parts[$k] = str_replace($match, implode('__', $e), $parts[$k]); - } - } - } - - $subquery = implode(' ', $parts); - return $subquery; - } - public function parseDqlQuery($query, $clear = true) { if ($clear) { @@ -1122,9 +939,6 @@ class IPF_ORM_Query extends IPF_ORM_Query_Abstract implements Countable, Seriali 'parent' => $parent, 'relation' => $relation, 'map' => null); - if ( ! $relation->isOneToOne()) { - $this->_needsSubquery = true; - } $localAlias = $this->getTableAlias($parent, $table->getTableName()); $foreignAlias = $this->getTableAlias($componentAlias, $relation->getTable()->getTableName()); -- 2.49.0