Question 166574 in Wordpress Question posted in
The official Wordpress documentation can be found here.

How do I get custom post_type post counts in taxonomy.php template using WordPress & SQL Query?

In my WordPress v5.7 I have the below code in my taxonomy.php template to get a custom post_type posts count in that taxonomy term.

// CURRENT TERM
$term = get_queried_object();

$bookArgs = array(
    'post_type' => 'book',
    'post_status' => 'publish', // get only publish posts
    'posts_per_page' => -1, // get all posts
    'tax_query' => array(
        'relation' => 'AND',
        array(
            'taxonomy' => $term->taxonomy,
            'field' => 'term_id',
            'terms' => $term->term_id
        )
    )
);
$total_book_arg = new WP_Query($bookArgs);
$total_book_count = $total_book_arg->post_count;

The above query does the job and gets me the total custom post_type count published in the current term.

However, this query seems get all the posts data, just to count the numbers and I have multiple queries in the same taxonomy.php page for 4 more other custom post_type.

As read somewhere a SQL query can save a lot of time and better performance, so I have tried with below SQL Query for the same custom post_type counts for the term in a taxonomy.php template. (I have very minimal SQL skills).

The below SQL seems not working in terms of giving me the count:

$SQLquery = "SELECT COUNT * FROM $wpdb->posts
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE $wpdb->term_taxonomy.term_id IN ($term->term_id)
AND $wpdb->term_taxonomy.taxonomy = $term->taxonomy
AND $wpdb->posts.post_type = 'book'
AND $wpdb->posts.post_status = 'publish'";
echo $wpdb->get_var($SQLquery);

How can I make the above SQL Query work for me in getting the custom post_type count?

Tags:

2

Answers


  1. 0

    Try this below query.

    global $wpdb;

$SQLquery = "
    SELECT $wpdb->posts.ID FROM $wpdb->posts 
    LEFT JOIN $wpdb->term_relationships 
    ON ( $wpdb->posts.ID = $wpdb->term_relationships.object_id  ) 
    WHERE 1=1 
    AND ( $wpdb->term_relationships.term_taxonomy_id IN ( $term->term_id ) ) 
    AND $wpdb->posts.post_type = 'product' 
    AND ( ( $wpdb->posts.post_status = 'publish' ) ) 
    GROUP BY $wpdb->posts.ID ORDER BY $wpdb->posts.post_date DESC
";

$SQLqueryCount = $wpdb->get_results($SQLquery,ARRAY_A);
echo count($SQLqueryCount);
    Login or Signup to reply.
  2. 0

    Saying that SQL queries are better and faster is a myth and complete nonsense. The WP_query is just a parameters wrapper, it’s made to be user friendly but it is by far as efficient (even better) as a regular SQL query.

    You need to use parameters in the right way. There is a difference between post_countand found_post.

    • post_count will ONLY return posts per page, it’s inefficient and will force you to load every single article first to be able to count them. A practical use case scenario would be results per page on a search.php page.
    • found_posts is what you’re looking for; It will get the post count from your associated query regardless of number of posts loaded. Straight from the table.

    One efficient way to right your query would be the following:

    <?php
if ( get_queried_object() instanceof WP_Term ) {
    $args = array(
        'post_type' => 'book',
        get_queried_object()->taxonomy => get_queried_object()->slug,
    );
    $query = new WP_Query( $args );
    echo $query->found_posts;
    wp_reset_postdata();
};
?>
    • get_queried_object() instanceof WP_Term as you’re on a taxonomy.php page, you should check if it is indeed a WP_Term object.
    • 'post_status' => 'publish' isn’t required, by default it is set to 'publish'.
    • you can simplify a term query with $taxonomy => $term.
    • wp_reset_postdata() is mandatory. It prevent your query from messing with the main one.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search