[eluser]Unknown[/eluser]
Hi,
I've coded a function that sort the JOIN array (ar_join) so that the select request will join the tables without trying to access a field that doesn't exist yet.
Here is an example :
Code:
$this->db->select('*')->from('invoice_line')
->join('client', 'invoice.client_id = client.id')
->join('invoice', 'invoice_line.invoice_id = invoice.id', 'left')
->get();
This request will return an error cause you try to access to client information before you access to invoice information.
The following function sort the joined tables to avoid a request error. Add this function inside the DB_active_rec.php file :
Code:
/**
* Sort the JOIN array
*
* Sort the join array in order to make an SQL query valid in case of
* a table is used before it has been joined.
* @return boolean true if success.
*/
function sortJoin()
{
$aJoinTables = array();
// 1 : list the joined tables (or their new name if they are renamed
foreach ($this->ar_join as $curJoin)
{
$matches = array();
preg_match("/.*JOIN (`[^`]+`) +(`?[a-zA-Z0-9_-]*`?)? *ON.*/", $curJoin, $matches);
$matches[2] = trim($matches[2]);
if (empty($matches[2]))
{
if (!in_array($matches[1], $this->ar_from))
{
$aJoinTables [] = $matches[1];
}
}
else
{
if (substr($matches[2], 0, 1) != "`")
{
$matches[2] = "`".$matches[2]."`";
}
if (!in_array($matches[2], $this->ar_from))
{
$aJoinTables [] = $matches[2];
}
}
}
// 2 : sort them
$max = pow(count($this->ar_join), 2);
$cpt = 0;
do
{
$permut = false;
$i = 0;
while ($i < count($this->ar_join) - 1)
{
$j = $i + 1;
while ($j < count($this->ar_join))
{
$cpt++;
if (preg_match("/.*ON +.*".$aJoinTables[$j]."\..*/", $this->ar_join[$i]))
{
// we found that 'j' junction contains the table called in a previous junction, the "i" one.
$permut = true;
$temp = $this->ar_join[$i];
$this->ar_join[$i] = $this->ar_join[$j];
$this->ar_join[$j] = $temp;
$temp = $aJoinTables[$i];
$aJoinTables[$i] = $aJoinTables[$j];
$aJoinTables[$j] = $temp;
break;
}
$j++;
}
if ($permut)
{
break;
}
$i++;
}
if ($cpt > $max)
{
// Infinite loop due to junctions that invert themselves permanently -> SQL problem
$permut = false;
}
} while ($permut);
return true;
}
Add the execution line around line 1746, inside the "_compile_select" function ; your code should look something like that :
Code:
if (count($this->ar_join) > 0)
{
$sql .= "\n";
$this->sortJoin();
$sql .= implode("\n", $this->ar_join);
}
Very usefull if you develop a very big application and add junctions between tables at multiple locations. You don't need to verify the join order
Enjoy !