User's Post count from specific category [Wordpress]

Question

Hello,

I want to show user's post count from specific category. Currently, I can only be able to query all posts. My code is like this

<?php $userpost_count = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->posts WHERE post_status = 'publish' AND post_type ='post' AND post_author = '".$curauth->ID."'");?>
<?php echo "<span>Total post: </b></span>".$userpost_count.""?>

I know that, I need to join two table which is post table and term_relationships, but i don't know how to get it. Please kindly help me with that. Thank you.

Answer 1

Use your CATEGORY_ID below and try this:

<?php $userpost_count = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->posts
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE $wpdb->term_taxonomy.term_id = CATEGORY_ID
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->posts.post_status = 'publish'
AND post_author = '".$curauth->ID."'");?>