List Recent Comments from Across a Multi-site Network

2 comments

1. 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="<?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.

   Log in to Reply

2. try this:

   $sqlstr = '';
   $blog_list = get_blog_list( 0, 'all' );
   $sqlstr = "SELECT 1 as blog_id, comment_date, comment_id, comment_post_id, comment_content, comment_date_gmt, comment_author, comment_author_email from ".$table_prefix ."comments where comment_approved = 1 ";
   $uni = '';
   foreach ($blog_list AS $blog) {
       $uni = ' union ';
       $sqlstr .= $uni . " SELECT ".$blog['blog_id']." as blog_id, comment_date, comment_id, comment_post_id, comment_content, comment_date_gmt, comment_author, comment_author_email   from ".$table_prefix .$blog['blog_id']."_comments where comment_approved = 1 ";                
   }
   $limit = ''; //set your limit
   $limit = ' LIMIT 0, '. (int)$wgt_count;
   $sqlstr .= " ORDER BY comment_date_gmt desc " . $limit; 
   $comm_list = $wpdb->get_results($sqlstr, ARRAY_A);
   

   and you might want to have a look at diamond multisite widgets plugin
   witch already implements this a a widget and as a shortcode.

   hopes this helps.

   Log in to Reply