EllisLab text mark
Advanced Search
     
Active Record join condition subquery escaped SELECT
 
HighKickX
Posted: 22 June 2012 10:17 PM   [ Ignore ]
Joined: 2012-06-22
2 posts

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->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.
 
Mark Croxton
Posted: 23 June 2012 03:39 AM   [ Ignore ]   [ # 1 ]   [ Rating: 0 ]
Avatar
Joined: 2002-10-08
256 posts

This may help explain the problem with back ticks:

https://gist.github.com/2923657
 
HighKickX
Posted: 23 June 2012 06:13 PM   [ Ignore ]   [ # 2 ]   [ Rating: 0 ]
Joined: 2012-06-22
2 posts
Mark Croxton - 23 June 2012 03:39 AM

This may help explain the problem with back ticks:

https://gist.github.com/2923657

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 = '';
   }
   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 :

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

None of my other joins are impacted.
 
Josepzin
Posted: 21 September 2012 06:09 AM   [ Ignore ]   [ # 3 ]   [ Rating: 0 ]
Avatar
Joined: 2008-09-08
30 posts

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!

 Signature 

Retro Invaders : Ungoliante blog : Glest Free Open Source RTS
 
Aken
Posted: 03 October 2012 05:06 AM   [ Ignore ]   [ # 4 ]   [ Rating: 0 ]
Avatar
Joined: 2007-11-28
2435 posts

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.