Added the ability to use aliases with joins in Active Record.
diff --git a/system/database/DB_active_rec.php b/system/database/DB_active_rec.php
index d48d1c5..4bf3098 100644
--- a/system/database/DB_active_rec.php
+++ b/system/database/DB_active_rec.php
@@ -42,7 +42,7 @@
var $ar_orderby = array();
var $ar_set = array();
var $ar_wherein = array();
-
+ var $ar_aliased_tables = array();
/**
@@ -103,8 +103,10 @@
{
foreach ((array)$from as $val)
{
+ $this->_track_aliases($val);
$this->ar_from[] = $this->dbprefix.$val;
}
+
return $this;
}
@@ -137,11 +139,6 @@
}
}
- 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 @@
// 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 @@
$val = trim($val);
if ($val != '')
- $this->ar_groupby[] = $val;
+ $this->ar_groupby[] = $this->dbprefix.$val;
}
return $this;
}
@@ -713,6 +713,7 @@
{
if ($table != '')
{
+ $this->_track_aliases($table);
$this->from($table);
}
@@ -744,6 +745,7 @@
{
if ($table != '')
{
+ $this->_track_aliases($table);
$this->from($table);
}
@@ -778,6 +780,7 @@
{
if ($table != '')
{
+ $this->_track_aliases($table);
$this->from($table);
}
@@ -871,7 +874,7 @@
* @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 @@
// --------------------------------------------------------------------
/**
+ * 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 @@
}
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 @@
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 @@
$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 9187589..d8bdc79 100644
--- a/user_guide/changelog.html
+++ b/user_guide/changelog.html
@@ -82,6 +82,7 @@
<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>