Arrange SQL results

[eluser]pollo[/eluser]
Hi everyone i´m doing a forum like this one and i have these tables relationships:

Code:

category has many forum
forum belongs to category
forum has many thread
thread belongs to forum

I want to retrieve all categories and for each category all forums and for each forum the number of threads.

This is the query i´m doing:

Code:

$this->db->select('c.title as category_title, f.title, f.description, f.slug, COUNT(t.id) AS 'num_threads');
$this->db->from('category c');
$this->db->join('forum f', 'c.id = f.category_id', 'left');
$this->db->join('thread t', 'f.id = t.forum_id', 'left');
$this->db->group_by('f.id');
$query = $this->db->get();
$query->result();

And it returns something like this:

Code:

Array
(
    [0] => Array
        (
            [category_title] => The CodeIgniter Lounge
            [title] => Introduce Yourself!
            [description] => Use this forum to introduce yourself to the CodeIgniter community, or to announce your new CI powered site.

            [slug] => introduce-yourself
            [num_threads] => 2
        )

    [1] => Array
        (
            [category_title] => The CodeIgniter Lounge
            [title] => The Lounge
            [description] => CodeIgniter's social forum where you can discuss anything not related to development. No topics off limits... but be civil.

            [slug] => the-lounge
            [num_threads] => 0
        )
    ...
)

But what i want is something like this:

Code:

Array
(
    [0] => Array
        (
            [category_title] => The CodeIgniter Lounge
            [forums] => Array
                (
                  [0] => Array
                      (
                         [title] => Introduce Yourself!
                         [description] => Use this forum to introduce yourself to the CodeIgniter community, or to announce your new CI powered site.
                         [slug] => introduce-yourself
                         [num_threads] => 2
                       )
                  [1] => Array
                      (
                         [title] => The Lounge
                         [description] => CodeIgniter's social forum where you can discuss anything not related to development. No topics off limits... but be civil.
                         [slug] => the-lounge
                         [num_threads] => 0
                       )
                )
        )

    ...
)

There is a way to do this?

[eluser]Krzemo[/eluser]
Just curious - why would you need it this way?
Standard sql queries are two dimensional and either you write it to get not normalized result (redundancy of data) or you just run subqueries and build your desired object.
Anyway - to get results like above you will have to run more then one query and build that structure manualy through iterations - so IMHO there is no gain. Just waist of time and processing power...

Regs

[eluser]pollo[/eluser]
Well, it´s easier to loop this way.

[eluser]Krzemo[/eluser]
I guess thats the only way do it (correct me if im wrong):

Code:

$categories = $this->model->get_categories();
foreach($categories as $category)
{
    $category->forums = $this->model->get_forums($category->id);
}

[eluser]jedd[/eluser]
[quote author="pollo" date="1261089210"]Well, it´s easier to loop this way.[/quote]

Can I suggest you learn new looping patterns?

As Cshamoh observes, SQL will return what is effectively a list of results - an array with an incremental numeric key, containing arrays of actual data. It will be easier if you learn to use those in their raw state, than be double-handling them all the time.

Try this thread for starters:
[url="http://ellislab.com/forums/viewreply/622401/"]http://ellislab.com/forums/viewreply/622401/[/url]

Give it a go, and if you get stuck you can post some code with where you're up to.

[eluser]pollo[/eluser]
Thanks everyone i tried your´s suggestions and also i tried this:

Code:

$vars['categories'] = $this->forum_model->get_categories();
$vars['forums'] = $this->forum_model->get_forums();
for ($i = 0; $i < count($vars['categories']); $i++) {
    $vars['categories'][$i]['forums'] = array();
    foreach ($vars['forums'] as $forum) {
        if ($vars['categories'][$i]['id'] == $forum['category_id']) {
        $vars['categories'][$i]['forums'][] = $forum;
    }
    }
}

and worked fine, but i think jedd loop pattern is best in perfomance.