Using $wpdb Class to Pull Recent Comments Across a Network

by

Been looking for a clean and simple way to produce comments from across a network using the $wpdb database class.

I did find this answer, which has been protected, and is four years old.

I wrapped it in a function and attempted to call it on the front page of my network, but it didn’t work. I also played with the mysql statement, but could not get it to produce any output.

Here’s what I have:

function display_sitewide_comments() {

global $wpdb;
$number = 20; // maximum number of comments to display
$selects = array();
$args = array();
foreach (wp_get_sites( $args ) 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);

$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/141619/<?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;
}

I’m wondering, since the post is 4 years old, if something has changed in core, or the database structure to make the query obsolete? Or possibly there is an error that I cannot see. In debug mode it does not throw any errors. I also declared and used an empty $args array as was suggested in the original post.

Thought this was a very useful function and that more people than me were having trouble with it.

Thanks.

1 Answer
1

Why not use get_comments(), instead of trying to roll your own with $wpdb?

function display_sitewide_comments() {
    $sites = wp_get_sites();
    $network_comments = array();
    $max = 20;
    foreach( $sites as $site ) {
        switch_to_blog( $site->blog_id );    
        $args = array(
        'number' => $max,
            'status' => 'approved',
    );
        $network_comments[$site->blog_id] = get_comments( $args );
        restore_current_blog();
    }
    // inspect the comments
    var_dump( $network_comments );
}

Edited to add:

If you just want the 20 most recent comments, you could change $network_comments[$site->blog_id] = get_comments( $args ); to something like

$comments = get_comments( $args );
foreach( $comments as $comments ) {
    $network_comments[] = $comment;
}

Then, at the end of the loop, use usort() to sort the comments based on $comment->comment_date_gmt and then truncate it to $max values.

References

  • wp_get_sites()
  • switch_to_blog() & restore_current_blog()
  • get_comments()

Leave a Comment