Welcome Guest, Not a member yet? Register   Sign In
dbprefix on multiple join conditions
#1

[eluser]Unknown[/eluser]
I have dbprefix set to TEST_ and a join statement on the form:
$this->db->join('table', 'cond1 AND cond2', 'left');

It is only the table names in cond1 that gets prefixed with the dbprefix.

Is there an alternative CI way of specifying multiple conditions on a join that is easier for CI to parse?
#2

[eluser]Unknown[/eluser]
Hi:

My solution was adding dbprefix manually:
Code:
$this->db->join('table',
  't1.field1 = t2.field2 AND '.$this->db->dbprefix.'t1.field3 = '.$this->db->dbprefix.'t2.field4', 'left');
Edited: better yet: using _protect_identifiers

Code:
$this->db->join('table',
  't1.field1 = t2.field2 AND'.
  $this->db->_protect_identifiers('t1.field3').
  '='.
  $this->db->_protect_identifiers('t2.field4'), 'left');
#3

[eluser]Unknown[/eluser]
I use the codeigniter 2.1.3
I modified the code add the lines.
file:
DB_active_rec.php
function JOIN
latest or original

Code:
public function join($table, $cond, $type = '')
{
  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 (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;
}

new or modified function

Code:
public function join($table, $cond, $type = '', $proteger=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);

  if ($proteger) //here
  {
   // Strip apart the condition and protect the identifiers
   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.') '; //here

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

  return $this;
}


Now I call the function:
$this->db->join('tasks', 'projects.id = tasks.project_id AND tasks.deleted=0', 'left', false);

good look




Theme © iAndrew 2016 - Forum software by © MyBB