Getting results from more than one table...to join or not to join.

[eluser]Taff[/eluser]
I have (as so often) got myself in a predicament which I thought I would be able to solve with join.

I would like to get a list of lists (just to make it complicated) from a table which are then output like this:

Code:

[0] => stdClass Object
        (
            [user_id] => 2
            [list_id] => 2
            [l_title] => test title
            [l_notes] => with some notes
            [l_milestone_id] => 5
            [l_responsible] => 0
            [l_tracked] => 0
        )

    [1] => stdClass Object
        (
            [user_id] => 2
            [list_id] => 4
            [l_title] => A second to do list
            [l_notes] => with some more notes
            [l_milestone_id] => 5
            [l_responsible] => 1
            [l_tracked] => 0
        )

For each of these lists I have a varying number of items in a different table with a field called list_id.

My first approach (which failed miserably) was this:

Code:

function get_todo_list($who){    
        $this->db->select('*');
        $this->db->from('todo_list');
        $this->db->join('todo_item','todo_list.list_id = todo_item.list_id');
        $this->db->where('user_id',$who);
        $query = $this->db->get();
}

Which I was hoping would produce something like:

Code:

[0] => stdClass Object
        (
            [user_id] => 2
            [list_id] => 2
            [l_title] => test title
            [l_notes] => with some notes
            [l_milestone_id] => 5
            [l_responsible] => 0
            [l_tracked] => 0
            [0]=>
             (
             [item_id] => 1
             [list_id] => 2
             )
             [1]=>
             (
             [item_id] => 2
             [list_id] => 2
             )
        )

    [1] => stdClass Object
        (
            [user_id] => 2
            [list_id] => 4
            [l_title] => A second to do list
            [l_notes] => with some more notes
            [l_milestone_id] => 5
            [l_responsible] => 1
            [l_tracked] => 0
             [0]=>
             (
             [item_id] => 1
             [list_id] => 4
             )
             [1]=>
             (
             [item_id] => 2
             [list_id] => 4
             )
        )

So do I need to get the results just from the todo_list and then loop through them somehow and make another database query for each of the results?

Any help or advice would be greatly appreciated.

Thanks in advance,
Taff

EDIT:Just to clear up I would like to do this:
[0] => stdClass Object
(
[user_id] => 2
[list_id] => 2 //Using this id I would like to query a table and return all the results as either an oject or a array somewhere here
[l_title] => test title
[l_notes] => with some notes
[l_milestone_id] => 5
[l_responsible] => 0
[l_tracked] => 0
)

[1] => stdClass Object
(
[user_id] => 2
[list_id] => 4//Using this id I would like to query a table and return all the results as either an oject or a array somewhere here
[l_title] => A second to do list
[l_notes] => with some more notes
[l_milestone_id] => 5
[l_responsible] => 1
[l_tracked] => 0
)

So that I can output something like this:

Code:

echo "To do list title: ".$data['l_title'];
echo "Notes: ".$data['l_notes'];
echo "Contains the following items:";
foreach ($data['sublist']->result as $row){
echo $row['item_title'];
}
echo "List assigned to ".$data['l_responsible'];

//grab the next item in a similar way

Hope somebody has done something like this in the past.

Taff

[eluser]Taff[/eluser]
My solution was to duplicate my query, manipulate it, and reassign it to the original data:

Code:

$data['query'] = $this->model->get_list();
$tmp_arr = $data['query'];
$len = sizeof($tmp_arr);
for($i=0;$i<$len;$i++){
    $tmp=$this->model->get_items($tmp_arr[$i]['list_id']);
    $tmp_arr[$i]['sublink']=$tmp->result_array();
}
$data['query']=$tmp_arr;