count number of user comments with a specific comment meta value

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:

Read More
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.

Related posts

2 comments

  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.

  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!

Comments are closed.