Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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

share|improve this question
    
the problem is that you don't need just a SQL query to get the comments, but you also need to build the links to the comment author/blog post. And for that you probably need to make other queries... A easy but very slow way to do this is to use switch_to_blog(x)... –  onetrickpony Jan 15 '11 at 2:53
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
Excellent! Worked almost perfect the first time. The only thing I had to do was to pass an empty $args to wp_get_sites(), otherwise WP freaked out about missing arguments. –  Pippin Jan 17 '11 at 1:24
add comment

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.

share|improve this answer
    
get_blog_list() is considered a suicidal function. –  Pippin Jan 17 '11 at 1:24
add comment

protected by toscho Mar 27 '13 at 10:16

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.