Get it All

As I’ve come to discover, both from my own experience and also from reading here, there doesn’t seem to be any particularly easy way to aggregate data across multiple blogs in WordPress MU. To some extent, I suppose, this makes sense: the ‘Pressers had anticipated using MU for web hosts that wanted to provide easy-setup blogs for their customers. But for those of us looking to fuse the multi-blog concept into a cohesive whole, this is a bit frustrating.

MU saves each new blog in the database by creating a new set of standard WordPress tables, adding a prefix that includes a number representing the order in which it was created. For example, the first blog created would use the prefix wp_1_* and the next would be wp_2_. That’s great for dB organization, but not so great for combining the blogs with an eye towards creating anything useful.

What is really quite strange about that is the fact that there *is* a table called wp_blogs which contains some small amounts of metadata regarding each blog. But this table seems to have been created for the sole purpose of marking blogs as “adult” or “spam.” Stranger still is the fact that these classifications are their own columns in the table! If I were looking to improve the performance of WPMU, the first place I would start would be abstracting these concepts so that admins could add their own classifications, such as those I propose to add for my own uses.

Speaking of adding my own classifications, I’m now at a bit of an impasse inasmuch as I’m not sure whether I want to add my classification as an option in each individual blog’s _options table, or as a new column in the wp_blogs table. The advantage of using the _options table is that I won’t need to modify the database at all, just add a new entry. In terms of creating a plugin that will be shared with the rest of the world, that’s helpful. But in terms of the most efficient use of database queries, identifying and filtering by classification in the first query would be best.

Consider this: you have five blogs classified as “community” blogs that anyone can post to and ones for each individual author on the site, a total of ten user blogs. You want a running list in the side bar of all new posts from private blogs, but not from the community blog. The first thing you’re going to need to do is identify, based on the wp_blogs table, how many blogs are available so you can loop through them later, looking for updated content.

Then you need to write the routine that’s going to loop through all the blogs you found in the previous query. If you don’t have the classification in the wp_blogs table, you end up with fifteen blogs, then cycle through them looking to see whether they’re community blogs or not. If you do have the classification in the wp_blogs table, then you cut your queries down to ten.

So, I imagine that I’m going to end up making the classification it’s own separate column in the wp_blogs table. That’ll make for an interesting challenge in terms of writing the interface for it. . . .