Added the ability to use aliases with joins in Active Record.

2 files changed, 95 insertions, 13 deletions

diff --git a/system/database/DB_active_rec.php b/system/database/DB_active_rec.php
index d48d1c5bd..4bf3098d9 100644
--- a/system/database/DB_active_rec.php
+++ b/system/database/DB_active_rec.php
@@ -42,7 +42,7 @@ class CI_DB_active_record extends CI_DB_driver {
 	var $ar_orderby		= array();

 	var $ar_set			= array();	

 	var $ar_wherein		= array();

-

+	var $ar_aliased_tables		= array();

 

 

 	/**

@@ -103,8 +103,10 @@ class CI_DB_active_record extends CI_DB_driver {
 	{

 		foreach ((array)$from as $val)

 		{

+			$this->_track_aliases($val);

 			$this->ar_from[] = $this->dbprefix.$val;

 		}

+

 		return $this;

 	}

 	

@@ -137,11 +139,6 @@ class CI_DB_active_record extends CI_DB_driver {
 			}

 		}

 

-		if ($this->dbprefix)

-		{

-			$cond = preg_replace('|([\w\.]+)([\W\s]+)(.+)|', $this->dbprefix . "$1$2" . $this->dbprefix . "$3", $cond);

-		}

-		

 		// If a DB prefix is used we might need to add it to the column names

 		if ($this->dbprefix)

 		{

@@ -150,8 +147,11 @@ class CI_DB_active_record extends CI_DB_driver {
 			

 			// Next we add the prefixes to the condition

 			$cond = preg_replace('|([\w\.]+)([\W\s]+)(.+)|', $this->dbprefix . "$1$2" . $this->dbprefix . "$3", $cond);

-		}

-		

+

+			$this->_track_aliases($table);

+

+		}	

+

 		$this->ar_join[] = $type.'JOIN '.$this->dbprefix.$table.' ON '.$cond;

 		return $this;

 	}

@@ -511,7 +511,7 @@ class CI_DB_active_record extends CI_DB_driver {
 			$val = trim($val);

 		

 			if ($val != '')

-				$this->ar_groupby[] = $val;

+				$this->ar_groupby[] = $this->dbprefix.$val;

 		}

 		return $this;

 	}

@@ -713,6 +713,7 @@ class CI_DB_active_record extends CI_DB_driver {
 	{

 		if ($table != '')

 		{

+			$this->_track_aliases($table);

 			$this->from($table);

 		}

 		

@@ -744,6 +745,7 @@ class CI_DB_active_record extends CI_DB_driver {
 	{

 		if ($table != '')

 		{

+			$this->_track_aliases($table);

 			$this->from($table);

 		}

 		

@@ -778,6 +780,7 @@ class CI_DB_active_record extends CI_DB_driver {
 	{

 		if ($table != '')

 		{

+			$this->_track_aliases($table);

 			$this->from($table);

 		}

 

@@ -871,7 +874,7 @@ class CI_DB_active_record extends CI_DB_driver {
 	 * @param	mixed	the where clause

 	 * @return	object

 	 */

-	function update($table = '', $set = NULL, $where = null, $limit = NULL)

+	function update($table = '', $set = NULL, $where = NULL, $limit = NULL)

 	{

 		if ( ! is_null($set))

 		{

@@ -1021,6 +1024,63 @@ class CI_DB_active_record extends CI_DB_driver {
 	// --------------------------------------------------------------------

 

 	/**

+	 * Track Aliases

+	 *

+	 * Used to track SQL statements written with aliased tables.

+	 *

+	 * @access	private

+	 * @param	string	The table to inspect

+	 * @return	string

+	 */	

+	function _track_aliases($table)

+	{

+		// if a table alias is used we can recognize it by a space

+		if (strpos($table, " ") !== FALSE)

+		{

+			// if the alias is written with the AS keyowrd, get it out

+			$table = preg_replace('/AS/i', '', $table); 

+

+			$this->ar_aliased_tables[] = trim(strrchr($table, " ") . '.');

+		}

+

+		return $this->dbprefix.$table;

+	}

+

+	// --------------------------------------------------------------------

+

+	/**

+	 * Filter Table Aliases

+	 *

+	 * Intelligently removes database prefixes from aliased tables

+	 *

+	 * @access	private

+	 * @param	array	An array of compiled SQL

+	 * @return	array	Cleaned up statement with aliases accounted for

+	 */	

+	function _filter_table_aliases($statements)

+	{

+		$filter_tables_with_aliases = array();

+

+		foreach ($statements as $statement)

+		{

+			$tables_with_dbprefix = array();			

+

+			foreach ($this->ar_aliased_tables as $k => $v)

+			{

+				$tables_with_dbprefix[$k] = '/'.$this->dbprefix.str_replace('.', '', $v).'\./';

+			}

+

+			$statement = preg_replace($tables_with_dbprefix, $this->ar_aliased_tables, $statement);

+			

+			$filter_tables_with_aliases[] = $statement;

+		}

+

+		return $filter_tables_with_aliases;

+	}

+

+	// --------------------------------------------------------------------

+

+	/**

 	 * Compile the SELECT statement

 	 *

 	 * Generates a query string based on which functions were used.

@@ -1047,9 +1107,19 @@ class CI_DB_active_record extends CI_DB_driver {
 		}

 

 		if (count($this->ar_join) > 0)

-		{		

+		{

 			$sql .= "\n";

-			$sql .= implode("\n", $this->ar_join);

+

+			// special consideration for table aliases

+			if (count($this->ar_aliased_tables) > 0 && $this->dbprefix)

+			{

+				$sql .= implode("\n", $this->_filter_table_aliases($this->ar_join));

+			}

+			else

+			{

+				$sql .= implode("\n", $this->ar_join);

+			}

+

 		}

 

 		if (count($this->ar_where) > 0 OR count($this->ar_like) > 0)

@@ -1071,8 +1141,18 @@ class CI_DB_active_record extends CI_DB_driver {
 		

 		if (count($this->ar_groupby) > 0)

 		{

+

 			$sql .= "\nGROUP BY ";

-			$sql .= implode(', ', $this->ar_groupby);

+			

+			// special consideration for table aliases

+			if (count($this->ar_aliased_tables) > 0 && $this->dbprefix)

+			{

+				$sql .= implode(", ", $this->_filter_table_aliases($this->ar_groupby));

+			}

+			else

+			{

+				$sql .= implode(', ', $this->ar_groupby);

+			}

 		}

 		

 		if (count($this->ar_having) > 0)

@@ -1154,6 +1234,7 @@ class CI_DB_active_record extends CI_DB_driver {
 		$this->ar_order		= FALSE;

 		$this->ar_orderby	= array();

 		$this->ar_wherein	= array();

+		$this->ar_aliased_tables = array();

 	}

 	

 	// --------------------------------------------------------------------

diff --git a/user_guide/changelog.html b/user_guide/changelog.html
index 918758901..d8bdc7996 100644
--- a/user_guide/changelog.html
+++ b/user_guide/changelog.html
@@ -82,6 +82,7 @@ Change Log
     <li>Added <kbd>quotes_to_entities()</kbd> function to <a href="./helpers/string_helper.html">string helper</a>.</li>

     <li>Added <kbd>form_reset()</kbd> function to <a href="./helpers/form_helper.html">form helper</a>.</li>

     <li>Added <kbd>count_all_results()</kbd> function to <a href="./database/active_record.html">Active Record</a>.</li>

+    <li>Added the ability to use aliases with joins in <a href="./database/active_record.html">Active Record</a>.</li>

     <li>Added a language entry for valid_ip validation error.</li>

     <li>Added a third parameter to Active Record's <kbd>like()</kbd> clause to control where the wildcard goes. </li>

     <li>Moved the safe mode and auth checks for the Email library into the constructor. </li>