User's total comment count excluding comments on their own posts

Question

I'm currently working on a plugin that I hinted at here and so far I have managed to code everything, only missing one piece of the puzzle.

This is how I get the total comment count for the current_user:

global $wpdb, $current_user;
get_currentuserinfo();
$userID = $current_user->ID;

$where = 'WHERE comment_approved = 1 AND user_id = ' . $userID ;
$comment_count = $wpdb->get_var("SELECT COUNT( * ) AS total 
     FROM {$wpdb->comments}
     {$where}");

echo 'Total Comments: ' . $comment_count;

I want to echo the current_user's total comment count on other users posts, so I don't want to count the comments on the author's own posts. Is such thing possible? And if so, how?

Answer 1

I'm not aware of any WordPress functions that do this, so you can try to play with this kind of query (untested):

function get_custom_user_comments_count( $uid ){
    global $wpdb;

    $sql = "SELECT COUNT(*) as total 
            FROM {$wpdb->comments} as c  
            JOIN {$wpdb->posts} as p ON p.ID = c.comment_post_ID 
            WHERE c.comment_approved = '1' 
            AND p.post_status ='publish'  
            AND p.post_type ='post'  
            AND p.post_author != c.user_id            
            AND c.user_id = %d";

    $comment_count = $wpdb->get_var( $wpdb->prepare( $sql, $uid ) );
    return $comment_count;
}

and use it like this:

global $current_user;
get_currentuserinfo();
echo 'Total Comments: ' . get_custom_user_comments_count( $current_user->ID );

This assumes that the comments are written when the users are logged in, so the user_id column in the wp_comments table is populated.

Update:

Another approach would be to collect the comments count per user in the user meta. You could try for example to hook into the comment_post action. This approach could be implemented before the users write comments on the site.