1

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.

  • Why not just make Google, Facebook etc. subcategories of Tech? – Jacob Peattie Jun 26 '18 at 7:10
0

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:

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? – Jacob Peattie Jun 26 '18 at 7:16
  • 1
    Yes, of course. I was still editing the answer ;) – Krzysiek Dróżdż Jun 26 '18 at 7:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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