Active Record join condition subquery escaped SELECT

[eluser]Unknown[/eluser]
When I try to join with a subquery as a condition, the SELECT statement from that subquery is escaped what leads to a database error.

Example :

Code:

$this->db->select('*');
$this->db->from('blogs');
$this->db->join('comments', 'comments.id = (SELECT id FROM blogs WHERE ....)');

$query = $this->db->get();

// Produces:
// SELECT * FROM `blogs`
// JOIN `comments` ON `comments`.`id` = (`SELECT` id FROM blogs WHERE ...)

Note that SELECT is the only word that is escaped from that subquery.

[eluser]Mark Croxton[/eluser]
This may help explain the problem with back ticks:

https://gist.github.com/2923657

[eluser]Unknown[/eluser]
[quote author="Mark Croxton" date="1340437162"]This may help explain the problem with back ticks:

https://gist.github.com/2923657[/quote]

Thanks for bringing this issue to my attention, here's what I did :

Edit file 'system/database/DB_active_rec.php'
Modify function 'join', add a fourth parameter '$escape_cond' which will skip the condition identifiers protection when set to FALSE

Code:

public function join($table, $cond, $type = '', $escape_cond = TRUE)
{
  if ($type != '')
  {
   $type = strtoupper(trim($type));

   if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER')))
   {
    $type = '';
   }
   else
   {
    $type .= ' ';
   }
  }

  // Extract any aliases that might exist.  We use this information
  // in the _protect_identifiers to know whether to add a table prefix
  $this->_track_aliases($table);

  // Strip apart the condition and protect the identifiers
  if ($escape_cond)
  {
   if (preg_match('/([\w\.]+)([\W\s]+)(.+)/', $cond, $match))
   {
    $match[1] = $this->_protect_identifiers($match[1]);
    $match[3] = $this->_protect_identifiers($match[3]);

    $cond = $match[1].$match[2].$match[3];
   }
  }

  // Assemble the JOIN statement
  $join = $type.'JOIN '.$this->_protect_identifiers($table, TRUE, NULL, FALSE).' ON '.$cond;

  $this->ar_join[] = $join;
  if ($this->ar_caching === TRUE)
  {
   $this->ar_cache_join[] = $join;
   $this->ar_cache_exists[] = 'join';
  }

  return $this;
}

And join like this :

Code:

$this->db->join(t$able, $condition, '', FALSE);

None of my other joins are impacted.

[eluser]Josepzin[/eluser]
HighKickX: good solution!! i do not like to modify the System files, but i do not understand why do not exist the $escape_cond param!

[eluser]Aken[/eluser]
All this does is circumvent the actual purpose of active record: to make writing simple queries easier and database-agnostic. If AR is limiting your ability to create more advanced queries, then I don't think you should be using AR in the first place. There's nothing wrong with using $this->db->query() with your own custom queries.