To better explain my question, there is an example:

I have 3 posts, all of them have category "Tech".

Post A is tagged with "Google". Post B is tagged with "Facebook". Post C is tagged with "Google".

then, on Tech category page, I want to display relative tags and order them by count. so the result should be like this "Google, Facebook".

I found a way:

  1. get all of post ids under category "Tech".

  2. then get tags by function wp_get_object_terms($object_ids, 'post_tag');

but this method has two disadvantages:

  1. it will be very slow if there is two many posts.

  2. the count order is total post count of a tag, not count that under a category.

Is there a MySQL way can do this job?


  • Why not just make Google, Facebook etc. subcategories of Tech? Commented Jun 26, 2018 at 7:10

1 Answer 1


You can get such tags using plain SQL query:

global $wpdb;
$wpdb->get_results( $wpdb->prepare(
    "SELECT tags.*, COUNT(tags_rel.object_id) as posts_count
        {$wpdb->prefix}terms tags
        INNER JOIN {$wpdb->prefix}term_taxonomy tags_tax ON (tags_tax.term_id = tags.term_id)
        INNER JOIN {$wpdb->prefix}term_relationships tags_rel ON (tags_tax.term_taxonomy_id = tags_rel.term_taxonomy_id)
        INNER JOIN {$wpdb->prefix}posts posts ON (tags_rel.object_id = posts.ID)
        INNER JOIN {$wpdb->prefix}term_relationships cats_rel ON (posts.ID = cats_rel.object_id)
        INNER JOIN {$wpdb->prefix}term_taxonomy cats_tax ON (cats_rel.term_taxonomy_id = cats_tax.term_taxonomy_id)
        INNER JOIN {$wpdb->prefix}terms cats ON (cats.term_id = cats_tax.term_id)
        tags_tax.taxonomy = 'post_tag'
        AND cats_tax.taxonomy = 'category'
        AND posts.post_type = 'post'
        AND posts.post_status = 'publish'
        AND cats.term_id = %d
    GROUP BY tags_tax.term_id",
    <CATEGORY_TERM_ID>  // <-- here goes the category id of current category
) );

It will give you result like that:

Query results

It still isn't very efficient way of getting these tags, but it will be much better than your approach.

On the other hand this is pretty nice opportunity to do some caching. These related tags won't change themselves. So you can compute them once and them use those cached values. This way they will be computed only during saving of posts...

  • 1
    Should cats.term_id = 4 be a placeholder? Commented Jun 26, 2018 at 7:16
  • 1
    Yes, of course. I was still editing the answer ;) Commented Jun 26, 2018 at 7:17
  • Just upvote for $wpdb->prefix Commented Sep 7, 2022 at 10:43

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.