Welcome Guest, Not a member yet? Register   Sign In
Active Record join condition subquery escaped SELECT

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 :
$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:


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


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

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 = '';
    $type .= ' ';

  // Extract any aliases that might exist.  We use this information
  // in the _protect_identifiers to know whether to add a table prefix

  // 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 :
$this->db->join(t$able, $condition, '', FALSE);

None of my other joins are impacted.

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!

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.

Theme © iAndrew 2016 - Forum software by © MyBB