CodeIgniter Forums

Full Version: A patch for JOINs
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]Sunjith[/eluser]
Hi,

I encountered a problem when selecting from joins. Different tables in the join had columns with same name. Hence selecting those fields from different tables in the select was not possible without specifying dbprefix and table name for each field. Here's a patch (against CI v1.5.3) that would help avoid including of dbprefix with table names everywhere in the select query.

=============================================================
--- DB_active_rec.php 2007-04-15 13:12:38.000000000 +0530
+++ DB_active_rec.php.new 2007-07-01 14:42:09.000000000 +0530
@@ -101,7 +101,7 @@
{
foreach ((array)$from as $val)
{
- $this->ar_from[] = $this->dbprefix.$val;
+ $this->ar_from[] = $this->dbprefix.$val.' AS '.$val;
}
return $this;
}
@@ -135,22 +135,7 @@
}
}

- if ($this->dbprefix)
- {
- $cond = preg_replace('|([\w\.]+)([\W\s]+)(.+)|', $this->dbprefix . "$1$2" . $this->dbprefix . "$3", $cond);
- }
-
- // If a DB prefix is used we might need to add it to the column names
- if ($this->dbprefix)
- {
- // First we remove any existing prefixes in the condition to avoid duplicates
- $cond = preg_replace('|('.$this->dbprefix.')([\w\.]+)([\W\s]+)|', "$2$3", $cond);
-
- // Next we add the prefixes to the condition
- $cond = preg_replace('|([\w\.]+)([\W\s]+)(.+)|', $this->dbprefix . "$1$2" . $this->dbprefix . "$3", $cond);
- }
-
- $this->ar_join[] = $type.'JOIN '.$this->dbprefix.$table.' ON '.$cond;
+ $this->ar_join[] = $type.'JOIN '.$this->dbprefix.$table.' AS '.$table.' ON '.$cond;
return $this;
}

=============================================================

Also, with this dbprefix won't be needed in condition.

Thoughts?

El Forum

[eluser]champs[/eluser]
Been there, done that -- it doesn't work. Active Record isn't psychic, and if you (need to) use table aliases, this approach will absolutely mess things up.

El Forum

[eluser]Sunjith[/eluser]
Thanks Champs.

That would work if no further aliasing is done. Like:
Code:
/**
     * get_property_list
     *
     * Get Property List
     * @author Hermawan Haryanto
     * @access public
     * @param array
     * @return object/false
     */
    function get_property_list ($attributes = array())
    {
        $this->db->select ('suburb.id, suburb.name, COUNT(property.id) total');
        if (count ($attributes) >= 1)
        {
            if (isset ($attributes['type']) AND $attributes['type'] != '')
                $this->db->where ('property.type_code_fk', $attributes['type']);
        }
        $this->db->groupby ('suburb.id');
        $this->db->join ('property', 'suburb.id = property.suburb_id_fk', 'left');
        $query = $this->db->get ('suburb');
        
        print $this->db->last_query();
        
        return ($query->num_rows() >= 1) ? $query->result() : false;
    }
It will return:
Code:
SELECT suburb.id, suburb.name, COUNT(property.id) total FROM mb_suburb AS suburb LEFT JOIN mb_property AS property ON suburb.id = property.suburb_id_fk GROUP BY suburb.id
Wouldn't it?

El Forum

[eluser]champs[/eluser]
We need a one-size-fits-all solution that works *all* the time, so what happens when each row in the table has multiple relationships to the same table?

Code:
<?php
$this->db->select('s.*, t.name AS teachername, p.name AS parentname');
$this->db->from('student AS s');
$this->db->join('adult AS p', 's.parent=p.personid');
$this->db->join('adult AS t', 's.teacher=t.personid');
?>

El Forum

[eluser]Sunjith[/eluser]
Aah! yes. Thanks.
I guess 1.5.1 version of active record is the best. I'm using it currently with the 1.5.3.