Welcome Guest, Not a member yet? Register   Sign In
Question about a 3 table join, and calling a model method from a view.
#1

[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.
#2

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

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




Theme © iAndrew 2016 - Forum software by © MyBB