Get total number of comments in wordpress category

I'm trying to figure out a way in Wordpress to get the total number of comments in a specific category. I've read the official docs and function references without success. I however came up with the code below but unfortunately, it only selects one $termid (i.e it picks the first termid of the first category) and displays the result in all categories. Please help.

<?php
  $categories = get_categories( array(
    'hide_empty'   => 0,
    'hierarchical' => 0,
    'exclude' => '1' //exclude uncategorised
  ));
 foreach($categories as $category): ?>   
 global $wpdb;

 $catid = $category->cat_ID;
 $catname = $category->name;

$count = "SELECT COUNT(*) FROM $wpdb->comments, $wpdb->terms WHERE term_id=$category->term_id";
    $result = $wpdb->get_var($count);
?>

Answers


I modified a query from the WordPress forum to get what you want. The "big" advantage with this setup is it will only make one request to the database. However, it does mean you will need to modify your script, but I don't think that is a big deal.

Here's the query

-- selects the comment count and term (category) name 
SELECT SUM(p.comment_count) AS count, t.name FROM wp_posts p
JOIN wp_term_relationships tr ON tr.object_id = p.ID
JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
JOIN wp_terms t ON t.term_id = tt.term_id
WHERE t.term_id in (1,2,3,4,5...)
AND p.post_status = 'publish'
GROUP BY t.term_id

And here's how I would write your code above.

<?php

global $wpdb;

$categories = get_categories(array(
  'hide_empty'   => 0,
  'hierarchical' => 0,
  'exclude' => '1' //exclude uncategorised
));

// create a comma separated string of category ids
// used for SQL `WHERE IN ()`
$category_ids = implode(',', array_map(function($cat) {
  return $cat->term_id;
}, $categories));

$query = "SELECT SUM(p.comment_count) AS count, t.name FROM wp_posts p
JOIN wp_term_relationships tr ON tr.object_id = p.ID
JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
JOIN wp_terms t ON t.term_id = tt.term_id
WHERE t.term_id in ($category_ids)
AND p.post_status = 'publish'
GROUP BY t.term_id";

$categories = $wpdb->get_results($query);

echo '<ul>';
foreach( $categories as $category ) {
  printf("<li>the %s category has %s comments</li>", $category->name, $category->count);
}
echo '</ul>';

Need Your Help

What type of pointer should I return from static member method

c++ oop pointers static-methods static-members

I come mostly from the world of Java, but have recently been writing a bunch of c++ and still don't have a great understanding of how to use pointers or what type of pointers to use where. I will g...

Android 3.0 Loader stays forever

java android android-3.0-honeycomb

What I want to achieve is having a ListFragment which displays data loaded from an external source. I want to use the Android 3.0 SDK for this purpose and come as close to the native user experienc...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.