Best way to approach subboards on custom coded forums?

[idkwhy]

Just wondering what some of you say would be best practices for adding subboards to custom coded forums.

 

I'm not a fan of nested queries (looping through parent forums then looping through child forums). But I also can't join subboards with one query since there will usually be more than one child forum to a parent.

 

What would be a better alternative? Are nested queries the only appropriate trade off?

 

Thanks for all help in advance!

[laffin]

Yep, hated them nested queries, but how often to you actually change/add/remove forums?

My answer, very few... So doing queries to do this for every user wasnt really an option.

So what I did, was built a multidimensional array with the db id's in php and save it somewhere (I used a plaintext file). You can store it under other mechanisms, but I just wanted something simple.

So anytime I needed the id's of parent/child forums. I would load up this cache file, and quickly grab the entries for the db.

[idkwhy]

Thank you Laffin! I thought of cacheing it in a not-so-similar way as well (could have sworn I said that in my post, oops...) but the idea felt hacky and that's why I wanted to post here. At any rate I am glad we're on the same page with nested queries. I am really not a fan. :/

 

Anyway, I am going to leave this unsovled since I'm interested in hearing other thoughts still. As I said I worry a caching system is a little hacky and hair pulling to maintain (while unlikely that I'll need to, there's still a possibility) I feel like there could be more aside from what's been said.

 

Anyone want to add on?

Edited February 17, 2015 by idkwhy

[scootstah]

Something like this?

 

SELECT b.id, b.name
FROM boards b
LEFT JOIN boards sub_b ON sub_b.parent_id = b.id

Which will give results like:

$results = array(
    array('id' => 1, 'name' => 'Parent Board',  'parent_id' => null),
    array('id' => 2, 'name' => 'Child Board 1', 'parent_id' => 1),
    array('id' => 3, 'name' => 'Child Board 2', 'parent_id' => 1),
);

Which you can then do something like:

$boards = array();

foreach ($results as $result) {
    if ($result['parent_id'] !== null) {
        $parentId = $result['parent_id'];
    } else {
        $parentId = $result['id'];
    }

    if (!isset($boards[$parentId])) {
        $boards[$parentId] = array('children' => array());
    }

    if ($result['parent_id'] !== null) {
        $boards[$result['parent_id']]['children'][] = $result;
    } else {
        $boards[$result['id']] += $result;
    }
}

foreach ($boards as $board) {
    echo '<h1>' . $board['name'] . '</h1>';

    if (!empty($board['children'])) {
        foreach ($board['children'] as $child) {
            echo '<h2>' . $child['name'] . '</h2>';
        }
    }
}

I just scribbled this out quickly. It works, but could be more optimized I'm sure.

[CroNiX]

I like to use the MPTT technique for hierarchial data. You can retrieve it all in a single query. The inserts/updates are a little slower than the adjacency list technique, but the reads are very quick. We do way more reads than writes to the hierarchy, so this works well for us. Our hierarchies are very large with thousands of entries and read any point in the "tree" lightning quick.