How to get related tags based on a category?

Question

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?

Thanks.

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
    FROM
        {$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)
    WHERE
        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:

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...