0

I'm working on a WordPress blog filter function that shows all categories with posts. When the user clicks a category I want to show the number of posts at each categoryname that have the active category AND this category. Cat A (5), Cat B (7), etc I've archived this with WPs get_post and some PHP but it takes several seconds, as I have >5000 posts.

That's why I need a SQL query to do it as the DB is probably much faster in calculating the number of posts with all given categories.

I found a SQL (MySQL Query to Retrieve Category from wp_posts) by @bbruman that does almost what I need, but it checks only for one category ID or, if coma separated, for all posts that have ANY of the given category IDs. I deed this to show return only posts that have ALL of the given category IDs.

SELECT *
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_term_taxonomy.term_id IN (307)
GROUP BY wp_posts.ID

1 Answer 1

0

There is a working example that only counts posts that are associated with both categories. This is tested with 3 posts, not 5000.

function count_posts_with_categories($cat_ids) {

    global $wpdb;
    $cat_count = 0;

    // A subquery to get all posts that are assigned to each category
    $subquery = "SELECT object_id FROM $wpdb->term_relationships WHERE term_taxonomy_id IN (" . implode(',', $cat_ids) . ") GROUP BY object_id HAVING COUNT(DISTINCT term_taxonomy_id) = " . count($cat_ids);

    // Use the subquery to count the number of posts that are assigned to both categories
    $cat_count = $wpdb->get_var(
        "SELECT COUNT(*) 
         FROM $wpdb->posts 
         WHERE post_status = 'publish' 
         AND post_type = 'post' 
         AND ID IN ($subquery)"
    );

    return $cat_count;
}

$cat_array = array(1, 2); // Replace with actual category IDs
$count = count_posts_with_categories($cat_array);
echo "Number of posts: " . $count;
1
  • You're a genius. Thank you! Works perfectly and is lightning fast.
    – phenomenia
    Mar 29, 2023 at 22:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.