Implement _compile_order_by()

6 files changed, 78 insertions, 57 deletions

diff --git a/system/database/DB_driver.php b/system/database/DB_driver.php
index 43ea10023..a327f4ad9 100644
--- a/system/database/DB_driver.php
+++ b/system/database/DB_driver.php
@@ -1128,8 +1128,8 @@ abstract class CI_DB_driver {
 		}
 
 		return 'UPDATE '.$table.' SET '.implode(', ', $valstr)
-			.$this->_compile_where()
-			.(empty($this->qb_orderby) ? '' : ' ORDER BY '.implode(', ', $this->qb_orderby))
+			.$this->_compile_wh('qb_where')
+			.$this->_compile_order_by()
 			.($this->qb_limit ? ' LIMIT '.$this->qb_limit : '');
 	}
 
diff --git a/system/database/DB_query_builder.php b/system/database/DB_query_builder.php
index 6c247f957..416132e16 100644
--- a/system/database/DB_query_builder.php
+++ b/system/database/DB_query_builder.php
@@ -948,54 +948,50 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	 * Sets the ORDER BY value
 	 *
 	 * @param	string
-	 * @param	string	direction: asc or desc
+	 * @param	string	direction: ASC or DESC
 	 * @param	bool	enable field name escaping
 	 * @return	object
 	 */
 	public function order_by($orderby, $direction = '', $escape = NULL)
 	{
-		if (strtolower($direction) === 'random')
+		$direction = trim($direction);
+
+		if (strtolower($direction) === 'random' OR $orderby === $this->_random_keyword)
+		{
+			// Random ordered results don't need a field name
+			$orderby = $this->_random_keyword;
+			$direction = '';
+		}
+		elseif (empty($orderby))
 		{
-			$orderby = ''; // Random results want or don't need a field name
-			$direction = $this->_random_keyword;
+			return $this;
 		}
-		elseif (trim($direction) !== '')
+		elseif ($direction !== '')
 		{
-			$direction = in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE) ? ' '.$direction : ' ASC';
+			$direction = in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE) ? ' '.$direction : '';
 		}
 
 		is_bool($escape) OR $escape = $this->_protect_identifiers;
 
-		if ($escape === TRUE && strpos($orderby, ',') !== FALSE)
+		if ($escape === FALSE)
 		{
-			$temp = array();
-			foreach (explode(',', $orderby) as $part)
-			{
-				$part = trim($part);
-				if ( ! in_array($part, $this->qb_aliased_tables))
-				{
-					$part = preg_match('/^(.+)\s+(ASC|DESC)$/i', $part, $matches)
-						? $this->protect_identifiers(rtrim($matches[1])).' '.$matches[2]
-						: $this->protect_identifiers($part);
-				}
-
-				$temp[] = $part;
-			}
-
-			$orderby = implode(', ', $temp);
+			$qb_orderby[] = array(array('field' => $orderby, 'direction' => $direction, $escape => FALSE));
 		}
-		elseif ($direction !== $this->_random_keyword && $escape === TRUE)
+		else
 		{
-			$orderby = preg_match('/^(.+)\s+(ASC|DESC)$/i', $orderby, $matches)
-				? $this->protect_identifiers(rtrim($matches[1])).' '.$matches[2]
-				: $this->protect_identifiers($orderby);
+			$qb_orderby = array();
+			foreach (explode(',', $orderby) as $field)
+			{
+				$qb_orderby[] = ($direction === '' && preg_match('/\s+(ASC|DESC)$/i', rtrim($field), $match, PREG_OFFSET_CAPTURE))
+					? array('field' => ltrim(substr($field, 0, $match[0][1])), 'direction' => ' '.$match[1][0], 'escape' => TRUE)
+					: array('field' => trim($field), 'direction' => $direction, 'escape' => TRUE);
+			}
 		}
 
-		$this->qb_orderby[] = $orderby_statement = $orderby.$direction;
-
+		$this->qb_orderby = array_merge($this->qb_orderby, $qb_orderby);
 		if ($this->qb_caching === TRUE)
 		{
-			$this->qb_cache_orderby[] = $orderby_statement;
+			$this->qb_cache_orderby = array_merge($this->qb_cache_orderby, $qb_orderby);
 			$this->qb_cache_exists[] = 'orderby';
 		}
 
@@ -2023,20 +2019,10 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 			$sql .= "\n".implode("\n", $this->qb_join);
 		}
 
-		// WHERE
-		$sql .= $this->_compile_wh('qb_where');
-
-		// GROUP BY
-		$sql .= $this->_compile_group_by();
-
-		// HAVING
-		$sql .= $this->_compile_wh('qb_having');
-
-		// ORDER BY
-		if (count($this->qb_orderby) > 0)
-		{
-			$sql .= "\nORDER BY ".implode(', ', $this->qb_orderby);
-		}
+		$sql .= $this->_compile_wh('qb_where')
+			.$this->_compile_group_by()
+			.$this->_compile_wh('qb_having')
+			.$this->_compile_order_by(); // ORDER BY
 
 		// LIMIT
 		if ($this->qb_limit)
@@ -2137,6 +2123,41 @@ abstract class CI_DB_query_builder extends CI_DB_driver {
 	// --------------------------------------------------------------------
 
 	/**
+	 * Compile ORDER BY
+	 *
+	 * Escapes identifiers in ORDER BY statements at execution time.
+	 *
+	 * Required so that aliases are tracked properly, regardless of wether
+	 * order_by() is called prior to from(), join() and dbprefix is added
+	 * only if needed.
+	 *
+	 * @return	string	SQL statement
+	 */
+	protected function _compile_order_by()
+	{
+		if (count($this->qb_orderby) > 0)
+		{
+			$sql = "\nORDER BY ";
+
+			for ($i = 0, $c = count($this->qb_orderby); $i < $c; $i++)
+			{
+				if ($this->qb_orderby[$i]['escape'] !== FALSE)
+				{
+					$this->qb_orderby[$i]['field'] = $this->protect_identifiers($field);
+				}
+
+				$this->qb_orderby[$i] = $this->qb_orderby[$i]['field'].$this->qb_orderby[$i]['direction'];
+			}
+
+			$sql .= implode(', ', $this->qb_orderby);
+		}
+
+		return '';
+	}
+
+	// --------------------------------------------------------------------
+
+	/**
 	 * Object to Array
 	 *
 	 * Takes an object as input and converts the class variables to array key/vals
diff --git a/system/database/drivers/mssql/mssql_driver.php b/system/database/drivers/mssql/mssql_driver.php
index bfc82a6c2..edc6a8480 100644
--- a/system/database/drivers/mssql/mssql_driver.php
+++ b/system/database/drivers/mssql/mssql_driver.php
@@ -453,13 +453,13 @@ class CI_DB_mssql_driver extends CI_DB {
 		// however an ORDER BY clause is required for it to work
 		if (version_compare($this->version(), '9', '>=') && $this->qb_offset && ! empty($this->qb_orderby))
 		{
-			$orderby = trim($this->_compile_order_by());
+			$orderby = $this->_compile_order_by();
 
 			// We have to strip the ORDER BY clause
-			$sql = trim(substr($sql, 0, strrpos($sql, 'ORDER BY '.$orderby)));
+			$sql = trim(substr($sql, 0, strrpos($sql, $orderby)));
 
 			return 'SELECT '.(count($this->qb_select) === 0 ? '*' : implode(', ', $this->qb_select))." FROM (\n"
-				.preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.$orderby.') AS '.$this->escape_identifiers('CI_rownum').', ', $sql)
+				.preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.trim($orderby).') AS '.$this->escape_identifiers('CI_rownum').', ', $sql)
 				."\n) ".$this->escape_identifiers('CI_subquery')
 				."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit;
 		}
diff --git a/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php b/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php
index ad699ce23..d6465cda2 100644
--- a/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php
+++ b/system/database/drivers/pdo/subdrivers/pdo_dblib_driver.php
@@ -222,13 +222,13 @@ class CI_DB_pdo_dblib_driver extends CI_DB_pdo_driver {
 		// however an ORDER BY clause is required for it to work
 		if (version_compare($this->version(), '9', '>=') && $this->qb_offset && ! empty($this->qb_orderby))
 		{
-			$orderby = trim($this->_compile_order_by());
+			$orderby = $this->_compile_order_by();
 
 			// We have to strip the ORDER BY clause
-			$sql = trim(substr($sql, 0, strrpos($sql, 'ORDER BY '.$orderby)));
+			$sql = trim(substr($sql, 0, strrpos($sql, $orderby)));
 
 			return 'SELECT '.(count($this->qb_select) === 0 ? '*' : implode(', ', $this->qb_select))." FROM (\n"
-				.preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.$orderby.') AS '.$this->escape_identifiers('CI_rownum').', ', $sql)
+				.preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.trim($orderby).') AS '.$this->escape_identifiers('CI_rownum').', ', $sql)
 				."\n) ".$this->escape_identifiers('CI_subquery')
 				."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit;
 		}
diff --git a/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php b/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php
index 399d134af..4b5747d90 100644
--- a/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php
+++ b/system/database/drivers/pdo/subdrivers/pdo_sqlsrv_driver.php
@@ -256,13 +256,13 @@ class CI_DB_pdo_sqlsrv_driver extends CI_DB_pdo_driver {
 		// An ORDER BY clause is required for ROW_NUMBER() to work
 		if ($this->qb_offset && ! empty($this->qb_orderby))
 		{
-			$orderby = trim($this->_compile_order_by());
+			$orderby = $this->_compile_order_by();
 
 			// We have to strip the ORDER BY clause
-			$sql = trim(substr($sql, 0, strrpos($sql, 'ORDER BY '.$orderby)));
+			$sql = trim(substr($sql, 0, strrpos($sql, $orderby)));
 
 			return 'SELECT '.(count($this->qb_select) === 0 ? '*' : implode(', ', $this->qb_select))." FROM (\n"
-				.preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.$orderby.') AS '.$this->escape_identifiers('CI_rownum').', ', $sql)
+				.preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.trim($orderby).') AS '.$this->escape_identifiers('CI_rownum').', ', $sql)
 				."\n) ".$this->escape_identifiers('CI_subquery')
 				."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit;
 		}
diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php
index 4eb12f910..badbb8e90 100644
--- a/system/database/drivers/sqlsrv/sqlsrv_driver.php
+++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php
@@ -454,13 +454,13 @@ class CI_DB_sqlsrv_driver extends CI_DB {
 		// An ORDER BY clause is required for ROW_NUMBER() to work
 		if ($this->qb_offset && ! empty($this->qb_orderby))
 		{
-			$orderby = trim($this->_compile_order_by());
+			$orderby = $this->_compile_order_by();
 
 			// We have to strip the ORDER BY clause
-			$sql = trim(substr($sql, 0, strrpos($sql, 'ORDER BY '.$orderby)));
+			$sql = trim(substr($sql, 0, strrpos($sql, $orderby)));
 
 			return 'SELECT '.(count($this->qb_select) === 0 ? '*' : implode(', ', $this->qb_select))." FROM (\n"
-				.preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.$orderby.') AS '.$this->escape_identifiers('CI_rownum').', ', $sql)
+				.preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.trim($orderby).') AS '.$this->escape_identifiers('CI_rownum').', ', $sql)
 				."\n) ".$this->escape_identifiers('CI_subquery')
 				."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit;
 		}