Efficient Taxonomy Intersection

0 Views

I have many uses for this but I want to know the most performant way of doing what is going to be an expensive operation.

As an example I will use a shop.

Read More

Given:

  • A product brand taxonomy
  • A product group taxonomy
  • A product post type
  • Archive templates for the above taxonomies

What is the most efficient, performant method, of showing a brand menu when in a product type archive, and a product type when in a brand archive, but, only show the terms that apply to the posts in that group.

So for example, if I am in the product group ‘womens’, it would show brands on the left hand side, but only the brands that are specified for products in the ‘womens’ product group. E.g. the brand ‘Fancy womens clothes Inc’ would show, but not ‘Manly mens Manly ltd’.

I need a generic answer, though I’m happy for the clothing products example to be used, and I know how to do this with a greedy algorithm by brute force, but that’s incredibly wasteful, and I’m not interested in a solution that would increase every page load by several seconds and load all the posts in full from the DB in the process

edit: Example 2:

Pikachu is a yellow pokemon, and is in the yellow tag, but Pikachu is also an electric pokemon, so it’s in the electric tag of the type taxonomy. How would I show only the types of pokemon that are yellow when in the yellow tag archive? e.g. all grass pokemon being green means that there would not be a grass menu item when in the yellow archive
but there would in the green ( yes I know there are grass pokemon that aren’t green )

Post Views: 0

Related posts

Leave a Reply

1 comment

  1. To generalize this is issue of retrieving all terms of taxonomy A that posts with specific term of taxonomy B have.

    While this is not impossible in several steps and plenty of looping through posts (which will indeed be inefficient), I think it’s reasonable to go through SQL for efficiency.

    My rough take on it would be:

    /**
 * Get all terms of $tax_to taxonomy that posts in $term_from of $tax_from have.
 *
 * @param string $tax_from  taxonomy name
 * @param string $term_from term slug
 * @param string $tax_to    taxonomy name
 *
 * @return array|WP_Error
 */
function get_intersected_terms( $tax_from, $term_from, $tax_to ) {

    global $wpdb;

    $term_from = get_term_by( 'slug', $term_from, $tax_from );

    $query = "
    SELECT term_id FROM {$wpdb->term_taxonomy} WHERE taxonomy = '{$tax_to}' AND term_taxonomy_id IN (
        SELECT term_taxonomy_id FROM {$wpdb->term_relationships} WHERE object_id IN (
            SELECT object_id FROM {$wpdb->term_relationships} WHERE term_taxonomy_id = {$term_from->term_taxonomy_id}
        )
    )
    ";

    $term_ids = $wpdb->get_col( $query );

    if( empty( $term_ids) )
        return array();

    return get_terms( $tax_to, array( 'include' => $term_ids ) );
}

// example
var_dump( get_intersected_terms( 'category', 'cat-a', 'post_tag' ) );