This wordpress sql query counts posts or custom posts in a taxonomy under multiple terms, just add more terms separated by commas where term-slug and term-slug2 are.
This is very useful because the get_terms() and get_terms_by() functions return the count of posts associated with the queried term, and this includes posts in the trash bin. So to just count the published posts in a custom taxonomy use this sql query.
This is very useful because the get_terms() and get_terms_by() functions return the count of posts associated with the queried term, and this includes posts in the trash bin. So to just count the published posts in a custom taxonomy use this sql query.
<?php
global $wpdb;
$query = "
SELECT COUNT( DISTINCT cat_posts.ID ) AS post_count
FROM wp_term_taxonomy AS cat_term_taxonomy INNER JOIN wp_terms AS cat_terms ON
cat_term_taxonomy.term_id = cat_terms.term_id
INNER JOIN wp_term_relationships AS cat_term_relationships
ON cat_term_taxonomy.term_taxonomy_id = cat_term_relationships.term_taxonomy_id
INNER JOIN wp_posts AS cat_posts
ON cat_term_relationships.object_id = cat_posts.ID
WHERE cat_posts.post_status = 'publish'
AND cat_posts.post_type = 'post'
AND cat_term_taxonomy.taxonomy = 'my_taxonomy'
AND cat_terms.slug IN ('term_slug','term-slug2')
";
return $wpdb->get_var($query);
?>
Very useful snippit of code. I put it in a function and slightly improved it so that it would work if wordpress install has a database without the prefix of wp_
function count_posts_in_term($taxonomy, $term,$type="post"){
global $wpdb;
$query = "
SELECT COUNT( DISTINCT cat_posts.ID ) AS post_count
FROM $wpdb->term_taxonomy AS cat_term_taxonomy INNER JOIN $wpdb->terms AS cat_terms ON
cat_term_taxonomy.term_id = cat_terms.term_id
INNER JOIN $wpdb->term_relationships AS cat_term_relationships
ON cat_term_taxonomy.term_taxonomy_id = cat_term_relationships.term_taxonomy_id
INNER JOIN $wpdb->posts AS cat_posts
ON cat_term_relationships.object_id = cat_posts.ID
WHERE cat_posts.post_status = 'publish'
AND cat_posts.post_type = '".$type."'
AND cat_term_taxonomy.taxonomy = '".$taxonomy."'
AND cat_terms.slug IN ('".$term."')
";
return $wpdb->get_var($query);
}
Sweet, feel free to store your own snippets on the site if you need somewhere to store them
hey i am in trouble can you help me. my custom taxonomies show draft+published posts but i only want publish post counts
WHERE cat_posts.post_status = 'publish' - make sure your query has a publish
can you please tell me how to use this code to use in my custom taxonomy
do i need to replace something?
replace my_taxonomy and the term slugs below it with your info, if you have more trouble please consult the wordpress codex
actually my prob is that i use custom taxonomies and display their posts counts along with their terms
but these show all the posts that are published as well as drafted.
while the wordpress default taxonomy shows only the published post.
this issue is only happening with custom taxonomy
can you please provide code. and step to install that code
Just insert this query within your functions.php or template file. I cant provide much more help than that, these snippets are for reference and not tested after posting