The Geek’s Blog

WordPress MU is an interesting platform, because the database schema makes it very difficult to do anything in an aggregated fashion. Instead of having all data across all blogs in a single table, every blog has it's own set of tables like wp_1_posts, wp_2_posts, wp_3_posts and so on for each table type in the database. The root of any WordPress MU site is also it's own blog, so in this case blogs.howtogeek.com is blog #1, which means it defaults to it's own tables of wp_1_posts, etc.

So I began the search for the best method to create an aggregated feed on the root blog. After a lot of googling around, I found that most people are using RSS aggregator plugins to pull in all of the feeds of the sub-blogs into the main blog. That solution just didn't sit right with me… I mean, the data is already there, why should I duplicate it and add another layer of complexity that has to be managed? What happens if it can't parse the incoming feed?

I spent some time discussing with Steve, the database wizard at work, who told me to just use a SQL UNION to join the tables and do a global ORDER BY to just get the latest. After all, there probably won't be hundreds of blogs hosted here, although anything is possible.

So the search began for the correct WordPress filter to use in a plugin to completely replace the RSS logic on the root blog. Sadly, the documentation is sorely lacking in explaining what filters actually do, and I really wasn't interested in hacking the source. I have done that on the main How-To Geek site, and upgrading becomes a very ugly task, although I've been working on moving all the custom logic into plugins (I'm about halfway there)

Then it occurred to me… a simple .htaccess rewrite rule would solve the entire problem. I can redirect the /feed/ URL into a separate php file and do all the logic there without touching the main source at all. I added the following rule, and that problem was solved:

RewriteRule ^feed/$ rssagg.php [L]

So now I just had to build the logic to pull in all of the posts across all the blogs. First, I needed to enumerate the blogs, which are stored in the wp_blogs table. Simple enough:

$blog_list = $wpdb->("select * from wp_blogs where public='1′ and blog_id > 1″);

You'll note that I'm excluding blog #1 from the list, because I don't want the root blog posts to show up in the feed. You could add more logic if you wanted a customized feed here.

So now I just needed to loop through the blogs in the list and build a SQL statement that will pull all the latest posts across all the blogs. I'm sure there's a better way to do this, but my PHP skills are very rusty.

$i = 0;   
foreach($blog_list as $theblog){
    if($i>0){$sqlstring .= " UNION ";}
    $sqlstring .= "select *,".$theblog['blog_id']." as blog_id from wp_".$theblog['blog_id']."_posts where post_type='post'";
    $i = 1;
}
$posts = $wpdb->get_results($sqlstring." order by post_date desc limit 20″,OBJECT);

Here's an example of what the SQL statement would be if there were 2 blogs. You'll note that I added in the blog_id manually to the sql statement so we can tell which blog each post is coming from.

select *,2 as blog_id from wp_2_posts where post_type='post'
   UNION
select *,3 as blog_id from wp_3_posts where post_type='post'
   order by post_date desc limit 20

With some caching, this should scale pretty well. Now all I had to do was loop through the posts and build the RSS XML.