woocommerce offtopic - Display list of categories that contain products with a specific tag

admin管理员组

文章数量:1323723

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

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

• woocommerce-offtopic
• tags

Share Improve this question asked Jul 10, 2016 at 17:21 seanukseanuk 3111 silver badge22 bronze badges 1

• 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 Commented Apr 10, 2018 at 21:19

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

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 = 'product'
    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 = 'product' ".
        "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.

P.S. It is also possible to make query for multiple tag ids:

Replace: tags.term_id = <TAG_TERM_ID> With: tags.term_id IN (TAG_TERM_ID_1,2,3)

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.

本文标签： woocommerce offtopicDisplay list of categories that contain products with a specific tag