CodeIgniter Forums
Question about a 3 table join, and calling a model method from a view. - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Question about a 3 table join, and calling a model method from a view. (/showthread.php?tid=23513)



Question about a 3 table join, and calling a model method from a view. - El Forum - 10-13-2009

[eluser]phpnoob[/eluser]
Hello, I have a question.

I have 3 tables.

Table1 fields(table1_id)
Table2 fields(table1_id,table3_id) <= lookup table
Table3 fields(table3_id,info)

In Table2 I store many to many relationship between table1 and 3. So I have many table1_id's to table2_id's. This is my method in my model:

Code:
function getWhatever() {
    $data = array();
    $this->db->select('*');
    $this->db->from('table1');
    $this->db->join('table2', 'table2.table1_id = table1.table1_id');
    $this->db->join('table3', 'table3.table3_id = table2.table3_id');
    $query = $this->db->get();
    
    if ($query->num_rows() > 0)
    {
        foreach ($Q->result_array() as $row)
        {
            $data[] = $row;
        }
    }
    
    $Q->free_result();
    return $data;    
}


I only get one record returned from table3, but I specify many table1_id's to table3_id's in table2.

So Im forced to call a method to return all table3 records from my view. I want to fix the problem above, I know calling models from views is not good.


Question about a 3 table join, and calling a model method from a view. - El Forum - 10-13-2009

[eluser]jedd[/eluser]
Do you have a working query in raw SQL you can share with us?


Question about a 3 table join, and calling a model method from a view. - El Forum - 10-14-2009

[eluser]phpnoob[/eluser]
Yes here is a raw SQL:

Code:
SELECT *
FROM table1 RIGHT OUTER JOIN
     table2 ON table1.table1_id = table2.table1_id LEFT OUTER JOIN
     table3 ON table2.table3_id = table3.table3_id


I can then do an optional WHERE. But Im just looping through each record in table1 and getting all the records associated with it from table3. table2 is a many to many between the two.