1

I've tagged some products with 'tag1', 'tag2' or sometimes both.

I want to list the categories that contain products with a specific tag, so 'Cat1' will only be listed if it has a product that is tagged 'tag1'.

  • Hi @seanuk, how did you go with this? You have two answers to choose from now - would love your feedback or a ‘tick’ on either :) – Tim Malone Apr 10 '18 at 21:19
1

There's probably a few ways to do this. The key will be cutting down on the number of loops you need to make it more efficient.

I think this will work:

$query = new WP_Query( array(
    'post_type' => 'product',
    'posts_per_page' => '-1', // unlimited posts
    'tax_query' => array(
       array(
         'taxonomy' => 'product_tag',
         'field' => 'slug',
         'terms' => array( 'tag1' ),
       ),
    ),
) );

$categories = array();

while( $query->have_posts() ){
   $query->the_post();
   $post_categories = get_the_terms( $post, 'product_cat' );
    foreach( $post_categories as $_cat ){
       if( ! isset( $categories[$_cat->term_id] ) ){
         $categories[$_cat->term_id] = $_cat;
       }
    }
}

wp_reset_postdata();

foreach ($categories as $category){
  echo $category->name . '<br />';
}

This is completely untested and rather quickly put together, but I think it should do what you want. You'll need to replace product_cat and product_tag with the correct taxonomy names if those aren't right (I don't use WooCommerce regularly).

Basically, using a tax_query, we're first finding all the posts that have the required tag. We then loop through those posts and construct a list of categories, finally looping through those categories to print them out.

This results in a list of categories that contain products with that specific tag.

  • Wouldn’t it be easier and more effective, if you used some SQL query instead of these loops? What if there are 1000 posts with given tag? Obtaining them all from DB will be very inefficient... – Krzysiek Dróżdż Mar 14 '18 at 7:37
  • @KrzysiekDróżdż Yes, there is most likely better ways to do it. You’re welcome to submit an alternative :) One thing to be aware of with a custom SQL query though is it’s usually not as future proof as using the abstraction that’s available through WP_Query (eg if the DB schema changes). But it might be possible to do it with WP_Query? – Tim Malone Mar 14 '18 at 7:41
  • No, you won’t do this with WP_Query, since you want to query taxonomy and not posts. There is nothing wrong with custom SQL. WP DB is pretty stable and it doesn’t change its structure very often. So yes, maybe it won’t work in 5 years from now, but it will work efficient and will work even if there are thousands of posts and terms :) (I’ll post it in few minutes, I guess) – Krzysiek Dróżdż Mar 14 '18 at 7:46
0

OK, so most efficient way to obtain such list will be a custom SQL query. You have to do 6 JOINS in it, but this way the database will do all the work and you won't need to get all posts, nor terms.

The query should look something like this:

SELECT categories.* FROM
    wp_terms categories
    INNER JOIN wp_term_taxonomy ctax ON (categories.term_id = ctax.term_id)
    INNER JOIN wp_term_relationships crel ON (crel.term_taxonomy_id = ctax.term_taxonomy_id)
    INNER JOIN wp_posts posts ON (posts.ID = crel.object_id)
    INNER JOIN wp_term_relationships trel ON (posts.ID = trel.object_id)
    INNER JOIN wp_term_taxonomy ttax ON (trel.term_taxonomy_id = ttax.term_taxonomy_id)
    INNER JOIN wp_terms tags ON (ttax.term_id = tags.term_id)
WHERE
    ctax.taxonomy='product_cat'
    AND posts.post_type = 'post'
    AND posts.post_status = 'publish'
    AND ttax.taxonomy = 'product_tag'
    AND tags.term_id = <TAG_TERM_ID>
GROUP BY categories.term_id

Of course you should use $wpdb to execute it, put $wpdb->prefix in it and use proper escaping of params. So you'll end up with something like this:

function get_categories_with_product_in_tag( $tag_term_id ) {
    global $wpdb;

    return $wpdb->get_results( $wpdb->prepare(
        "SELECT categories.* FROM ".
        "{$wpdb->prefix}terms categories ".
        "INNER JOIN {$wpdb->prefix}term_taxonomy ctax ON (categories.term_id = ctax.term_id) ".
        "INNER JOIN {$wpdb->prefix}term_relationships crel ON (crel.term_taxonomy_id = ctax.term_taxonomy_id) ".
        "INNER JOIN {$wpdb->prefix}posts posts ON (posts.ID = crel.object_id) ".
        "INNER JOIN {$wpdb->prefix}term_relationships trel ON (posts.ID = trel.object_id) ".
        "INNER JOIN {$wpdb->prefix}term_taxonomy ttax ON (trel.term_taxonomy_id = ttax.term_taxonomy_id) ".
        "INNER JOIN {$wpdb->prefix}terms tags ON (ttax.term_id = tags.term_id) ".
        "WHERE ".
        "ctax.taxonomy='product_cat' ".
        "AND posts.post_type = 'post' ".
        "AND posts.post_status = 'publish' ".
        "AND ttax.taxonomy = 'product_tag' ".
        "AND tags.term_id = %d ".
        "GROUP BY categories.term_id",
        $tag_term_id
    ) );
}

Not tested, but something like this should work OK.

  • Nice work! I think you missed a %d though for TAG_TERM_ID – Tim Malone Apr 10 '18 at 21:18
  • @TimMalone ha! Yes i did. I’ve put <tag_term_id> placeholder instead... Let me edit that.. :) – Krzysiek Dróżdż Apr 10 '18 at 21:20

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.