count number of user comments with a specific comment meta value

Question

I am using the code below to count the number of comments by a certain user:

global $wpdb;

        $count = $wpdb->get_var(
          'SELECT COUNT( comment_id ) FROM '. $wpdb->comments .'
          WHERE user_id = '.$user->ID.'
          AND comment_approved = "1"
          AND comment_type NOT IN ("pingback", "trackback" )'
        );

I am in addition, using a comment meta field ('consider') which takes 0 or 1 as values. To get its value I use:

get_comment_meta($comment_id, 'consider',true);

How can I merge these two operations so that I get the number of comments submitted by the user and which have the meta value 'consider' equal to 1? I am not an SQL user and it seems that I need to use inside my SQL query the table wp_commentmeta?

Your help is always appreciated.

Answer 1

Your basic question is a pure SQL question.

$count = $wpdb->get_var(
  'SELECT COUNT( comments.comment_ID ) FROM '. $wpdb->comments .' as comments
  LEFT JOIN '.$wpdb->commentmeta.' AS cmeta ON comments.comment_ID = cmeta.comment_id
  WHERE user_id = 1
  AND comment_approved = "1"
  AND comment_type NOT IN ("pingback", "trackback" )
  AND cmeta.meta_key = "rating"
  AND cmeta.meta_value = 5'
);

I cleaned that up a bit to be more readable.

What you are doing with that SQL is pretty close, possibly identical to, what you'd get with ....

$args = array(
  'type' => '',
  'user_id' => '1',
  'count' => true,
  'meta_key' => 'rating',
  'meta_value' => '5',
);
$count = get_comments($args);

You aren't explicitly excluding trackbacks and pingbacks with that but I am pretty sure that trackbacks and pingbacks will never have an associated user ID, so you are excluding them by that mechanism.

Answer 2

Try this :

SELECT
    COUNT(*) AS RESULT
FROM
    wp_commentmeta a,
    wp_comments b
WHERE
    a.comment_id = b.comment_ID
AND a.meta_value = 1
AND user_id = 2

Hope it helps!