CI, MVC and m:n table-relationships

[eluser]eff_kay[/eluser]
Hi again!

The image attached to this post shows my tables and how they're connected. As you can see, there are three many-to-many relationships. Basically scenario has many ob -> ob has many oe -> oe has many tc.

As for the UI, the user will be presented with a list of available scenarios. When the user selects a scenario, I want to display everything belonging to this scenario.

Using CodeIgniter, I figured I could make either a model, or a function within a model, for each of the tables. This is the model I have thus far:

Code:

//scenario_model.php
class Scenario_model extends Model{

    function Scenario_model()
    {
        parent::Model();
    }
    
    function getScenarios()
    {
        $query = $this->db->get('scenario');

        return $query;
    }
    
    function getObs($scenarioId)
    {    
        $this->db->select('scenario.name, ob.id, ob.type, ob.description');
        $this->db->from('ob');
        $this->db->join('scenario_has_ob', 'scenario_has_ob.ob_id = ob.id');
        $this->db->join('scenario', 'scenario_has_ob.scenario_id = scenario.id');
        $this->db->where('scenario.id = ' . $scenarioId);
        
        $query = $this->db->get();

        return $query;
    }
    
    function getOes($obId)
    {
        $this->db->select('oe.id, oe.description, oe.situation, oe.difficulty');
        $this->db->from('oe');
        $this->db->join('ob_has_oe', 'ob_has_oe.oe_id = oe.id');
        $this->db->join('ob', 'ob_has_oe.ob_id = ob.id');
        $this->db->where('ob.id = ' . $obId);
        
        $query = $this->db->get();
        
        return $query;
    }

}

And my controller:

Code:

//scenario.php
class Scenario extends Controller {

    function Scenario()
    {
        parent::Controller();
        
        $this->load->helper('url');        
        $this->load->model('Scenario_model');
    }
    
    function index()
    {    
        $data['scenario_data'] = $this->Scenario_model->getScenarios();
    
        $this->load->view('scenario_view', $data);
    }
    
    function ob($scenarioId = null)
    {
        $data['ob_data'] = $this->Scenario_model->getObs($scenarioId);
        
        $this->load->view('ob_view', $data);
    }

}

And the view for scenarios:

Code:

//scenario_view.php
...
<h1>Please select a scenario from the list:</h1>
&lt;?php
echo "<ul>";

foreach ($scenario_data->result() as $row):
    
    echo "<li>" . anchor('scenario/ob/'.$row->id, $row->name) . "</li>";
    
endforeach;

echo "</ul>";
?&gt;
...

This gives me a link-list of the scenarios existing in table "scenario". So when a user clicks one of the links, the function "ob" is called and the scenarioid is passed to it in the link. It then uses this to look up the ob's belonging to the scenario. The functions in the model takes a parameter $id. This id is used to fetch the elements belonging to the different tables.

Thats fine and it works. But I want to use information from the other tables as well (oe, and tc). So I'm stuck in ob_view.php:

Code:

//ob_view.php
...
<?php
foreach ($ob_data->result() as $ob):

    echo "<h2>" . $ob->type . " - " . $ob->description . "</h2>";
        
        // THIS is where i want data from the other tables (oe and tc)
    
    echo "<hr />";

endforeach;
?&gt;
...

Basically what I want function ob to do, is get ALL the data belonging to a scenario given only the scenario id using the models and send this to ob_view.

If it's still unclear, I could provide you guys with the procedural equivalent just for clarity. I'm going to leave it out for the time being, tho.

Thank you in advance! :c)

Edit:
Oh yeah, btw. Thanks for the tip, jedd. I modified the code now :c)