CodeIgniter Forums
new function to sort JOIN array inside active record class - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Libraries & Helpers (https://forum.codeigniter.com/forum-22.html)
+--- Thread: new function to sort JOIN array inside active record class (/thread-51202.html)



new function to sort JOIN array inside active record class - El Forum - 04-24-2012

[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 !