questions

WordPress SQL Query to get custom post_type posts counts in taxonomy.php template

Rate this post

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?

 

✔️Solution:

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);

Leave a Reply

Your email address will not be published.

Back to top button