List Recent Comments from Across a Multi-site Network

I’m look for a custom SQL query that will let me pull in the latest comments from across a WP multi-site install.

The end result will be identical to a regular recent comments widget, but from all sites within the installation.

Ideas?

Thanks

Ok, I did some research based on בניית אתרים’s solution here, as I’m interested in this too.

First you need to get a list of blog IDs, and get_blog_list() is deprecated because it seems to be a “suicidal database query” 🙂 Anyway looks like there will be a alternative in WP 3.2 called wp_get_sites(). So use this function instead. I suggest you pass the 'sort_column => 'last_updated' argument, and 'limit' the results to 20 or something like that. This would make the next query much faster.

So:

global $wpdb;
$number = 20; // maximum number of comments to display
$selects = array();
foreach (wp_get_sites() as $blog)
   // select only the fields you need here!
   $selects[] = "(SELECT comment_post_ID, comment_author, comment_author_email, comment_date_gmt, comment_content, post_title, {$blog['blog_id']} as blog_id FROM {$wpdb->base_prefix}{$blog['blog_id']}_comments
      LEFT JOIN {$wpdb->base_prefix}{$blog['blog_id']}_posts
      ON comment_post_id = id
      WHERE post_status="publish"
        AND post_password = ''
        AND comment_approved = '1'
        AND comment_type=""
       ORDER BY comment_date_gmt DESC LIMIT {$number})"; // real number is (number * # of blogs)

  $comments = $wpdb->get_results(implode(" UNION ALL ", $selects)." ORDER BY comment_date_gmt DESC", OBJECT);

Then render the output:

<ul>
<?php
$count = 0;
foreach((array)$comments as $comment):
  $count++;
  if($count == $number+1) break; 
  ?>
  <li>
   <?php echo get_avatar($comment->comment_author_email, 32); ?>
   <a href="https://wordpress.stackexchange.com/questions/7014/<?php echo get_blog_permalink($comment->blog_id, $comment->comment_post_ID); ?>" title="commented on <?php echo strip_tags($comment->post_title); ?>">
   <?php echo $comment->comment_author; ?> wrote: 
   <?php echo convert_smilies(wp_trim_excerpt($comment->comment_content)); ?>
   (<?php echo human_time_diff(strtotime("{$comment->comment_date_gmt}")); ?>)  
   </a>
  </li>
<?php
endforeach;
?>
</ul>

You should also cache the results, and flush the cache like once every 10 minutes or so.