WordPress Multisite table hell
With the release of WordPress 3.0, the multisite functionality of WPMU was rolled into the main WordPress codebase. Before this, I hadn’t done any work with WPMU, so I was utterly dumbfounded to discover that when adding a new site to a WordPress network, WordPress adds another set of tables for each blog. That’s right. Rather than doing what you or I would do – adding a blog_id field to the existing tables – it creates an unwieldy mess of tables. As an example, one site I’ve been developing currently has 32 blogs, which all together add up to an astonishing 318 tables.
One of the requirements of this site was to have a custom post type for game reviews, using two custom taxonomies (platform and game). On an individual review on the main site, hte page should display the number of times that particular game has been reviewed by site users, along with the average score out of 10. With a sensible database schema this would be a relatively simple matter. With the way WordPress is set up, I ended up having to do this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | <?php function aggregate_score($post) { global $wpdb; $game = wp_get_object_terms($post->ID, 'game'); $blogs = $wpdb->get_col("SELECT blog_id FROM $wpdb->blogs WHERE public = '1' AND archived = '0' AND mature = '0' AND spam = '0' AND deleted = '0' AND blog_id != '1'"); $first = array_shift($blogs); $querystr = " SELECT DISTINCT master.meta_key, COUNT(*) as count, AVG(master.meta_value) as average FROM (SELECT * FROM (SELECT wposts.ID, wpostmeta.meta_key, wpostmeta.meta_value FROM " . $wpdb->base_prefix . $first . "_postmeta wpostmeta LEFT JOIN " . $wpdb->base_prefix . $first . "_posts wposts ON(wpostmeta.post_id = wposts.ID) LEFT JOIN " . $wpdb->base_prefix . $first . "_term_relationships wrelationships ON(wposts.ID = wrelationships.object_id) LEFT JOIN " . $wpdb->base_prefix . $first . "_term_taxonomy wtaxonomy ON(wrelationships.term_taxonomy_id = wtaxonomy.term_taxonomy_id) LEFT JOIN " . $wpdb->base_prefix . $first . "_terms wterms ON(wterms.term_id = wtaxonomy.term_id) WHERE wposts.post_type = 'reviews' AND wpostmeta.meta_key = 'review_score' AND wtaxonomy.taxonomy = 'game' AND wterms.name = '" . $game[0]->name . "' AND wpostmeta.meta_value IS NOT NULL AND wposts.post_status = 'publish') as derived1"; foreach ($blogs as $blog) { $querystr .= " UNION SELECT * FROM (SELECT wposts.ID, wpostmeta.meta_key, wpostmeta.meta_value FROM " . $wpdb->base_prefix . $blog . "_postmeta wpostmeta LEFT JOIN " . $wpdb->base_prefix . $blog . "_posts wposts ON(wpostmeta.post_id = wposts.ID) LEFT JOIN " . $wpdb->base_prefix . $blog . "_term_relationships wrelationships ON(wposts.ID = wrelationships.object_id) LEFT JOIN " . $wpdb->base_prefix . $blog . "_term_taxonomy wtaxonomy ON(wrelationships.term_taxonomy_id = wtaxonomy.term_taxonomy_id) LEFT JOIN " . $wpdb->base_prefix . $blog . "_terms wterms ON(wterms.term_id = wtaxonomy.term_id) WHERE wposts.post_type = 'reviews' AND wpostmeta.meta_key = 'review_score' AND wtaxonomy.taxonomy = 'game' AND wterms.name = '" . $game[0]->name . "' AND wpostmeta.meta_value IS NOT NULL AND wposts.post_status = 'publish') AS derived" . $blog . " "; } $querystr .= " ) as master GROUP BY master.meta_key ORDER BY master.meta_key DESC "; $userscores = $wpdb->get_results($querystr); ?> <p class="score"><?php echo $userscores[0]->average; ?>/10</p> <p><?php echo $userscores[0]->count; ?> Reviews</p> <? } ?> |
It’s butt-ugly, but I can’t see another way of making it work.
global $wpdb;
$game = wp_get_object_terms($post->ID, ‘game’);
$blogs = $wpdb->get_col(“SELECT blog_id FROM $wpdb->blogs WHERE
public = ‘1’ AND archived = ‘0’ AND mature = ‘0’ AND spam = ‘0’ AND deleted = ‘0’ AND blog_id != ‘1’”);
$first = array_shift($blogs);
$querystr = ”
SELECT DISTINCT master.meta_key, COUNT(*) as count, AVG(master.meta_value) as average FROM
(SELECT * FROM
(SELECT wposts.ID, wpostmeta.meta_key, wpostmeta.meta_value
FROM ” . $wpdb->base_prefix . $first . “_postmeta wpostmeta
LEFT JOIN ” . $wpdb->base_prefix . $first . “_posts wposts ON(wpostmeta.post_id = wposts.ID)
LEFT JOIN ” . $wpdb->base_prefix . $first . “_term_relationships wrelationships ON(wposts.ID = wrelationships.object_id)
LEFT JOIN ” . $wpdb->base_prefix . $first . “_term_taxonomy wtaxonomy ON(wrelationships.term_taxonomy_id = wtaxonomy.term_taxonomy_id)
LEFT JOIN ” . $wpdb->base_prefix . $first . “_terms wterms ON(wterms.term_id = wtaxonomy.term_id)
WHERE wposts.post_type = ‘reviews’
AND wpostmeta.meta_key = ‘review_score’
AND wtaxonomy.taxonomy = ‘game’
AND wterms.name = ‘” . $game[0]->name . “‘
AND wpostmeta.meta_value IS NOT NULL
AND wposts.post_status = ‘publish’) as derived1″;
foreach ($blogs as $blog) {
$querystr .= ”
UNION
SELECT * FROM
(SELECT wposts.ID, wpostmeta.meta_key, wpostmeta.meta_value
FROM ” . $wpdb->base_prefix . $blog . “_postmeta wpostmeta
LEFT JOIN ” . $wpdb->base_prefix . $blog . “_posts wposts ON(wpostmeta.post_id = wposts.ID)
LEFT JOIN ” . $wpdb->base_prefix . $blog . “_term_relationships wrelationships ON(wposts.ID = wrelationships.object_id)
LEFT JOIN ” . $wpdb->base_prefix . $blog . “_term_taxonomy wtaxonomy ON(wrelationships.term_taxonomy_id = wtaxonomy.term_taxonomy_id)
LEFT JOIN ” . $wpdb->base_prefix . $blog . “_terms wterms ON(wterms.term_id = wtaxonomy.term_id)
WHERE wposts.post_type = ‘reviews’
AND wpostmeta.meta_key = ‘review_score’
AND wtaxonomy.taxonomy = ‘game’
AND wterms.name = ‘” . $game[0]->name . “‘
AND wpostmeta.meta_value IS NOT NULL
AND wposts.post_status = ‘publish’) AS derived” . $blog . ”
“;
}
$querystr .= ”
) as master
GROUP BY master.meta_key
ORDER BY master.meta_key DESC
“;
$userscores = $wpdb->get_results($querystr);
?>
<p class=”score”><?php echo $userscores[0]->average; ?>/10</p>
<p><?php echo $userscores[0]->count; ?> Reviews</p>
<?
}
?>
Leave a Reply