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

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:

function getWhatever() {
    $data = array();
    $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;
    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.

Do you have a working query in raw SQL you can share with us?

Yes here is a raw SQL:

     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