Built Report Page

[eluser]sqwk[/eluser]
I have two db tables "enquiries" and "emails" I would like to built a report that shows a paginated list of all enquiries with the relevant emails listed under each enquiry row.

How would I go about doing this in CI?. MySQL cannot handle this by itself as I need to get multiple rows from the emails table. (otherwise I would use correlated subqueries.)

I want to show the data in a nested table like so:

Code:

repeat this for all ENQUIRY rows {
    /------------/-----------------/-----------------/-----------------/
    / Enquiry ID / Enquiry Field 1 / Enquiry Field 2 / Enquiry Field 3 /
    /------------/-----------------/-----------------/-----------------/
    repeat this for all EMAIL rows WHERE the email_enquiry_id = enquiry_id {
        /----------/---------------/---------------/---------------/
        / Email ID / Email Field 1 / Email Field 2 / Email Field 3 /
        /----------/---------------/---------------/---------------/
    }
}

I thought of getting each enquiries and emails individually like so:

Code:

$this->load->model('enquiries');
$enquiries = $this->enquiries->get_enquiries($limit, $offset);
foreach($enquiries->result() as $row)
    $enquiry_ids[] =  $row->enquiry_id;
$enquiry_ids = array_unique($enquiry_ids);

$this->load->model('email_db');
$emails = $this->email_db->get_emails($enquiry_ids);

Where get_enquiries would get a selection of rows and get_emails only gets the rows that are linked to any of the enquiry rows.

How can I link the two together in a viewfile? Or even better built a nested array out of them?

[eluser]sqwk[/eluser]
Slightly changed this:
http://ellislab.com/forums/viewthread/87994/P0/

into:

Code:

function get_enquiries() {
        $result = $this->db->get('enquiries');

        // loop through the parents
        foreach($result->result() as $row ) {
            $enquiry_id = $row->enquiry_id;
            $this->db->where('email_enquiry_id', $enquiry_id);
            $row->enquiry_emails = $this->db->get('emails');
        }
        return $result;
    }