Is there a way of increasing the speed of this query?

Question

I need to cross reference two taxonomies, product_categories and brands. the only way I can think to do this is to get all the posts in one taxonomy, then get all the posts that exist in another taxonomy:

<?php

  $category = /* Taxonomy object selected from list */;

  // Get all the posts in this query
  $args = array(
    'post_type' => 'product',
    'tax_query' => array(
      array(
        'taxonomy' => $category->taxonomy,
        'field'    => 'slug',
        'terms'    => $category->slug
      )
    )
  );
  $product_items = get_posts( $args );

  // create a blank array ready for the IDs
  $product_items_ids = [];

  // For every post found, populate the array
  foreach ($product_items as $product_item) {
    // Create array from all post IDs in category
    $product_items_ids[] = $product_item->ID;
  }

  // Get all terms in Brand taxonomy that are in the array above
  $brand_items = wp_get_object_terms( $product_items_ids, 'brand' );

  // Output the information needed
  foreach ($brand_items as $brand_item) { ?>
    <li><a href="<?php echo get_term_link( $brand_item->slug,  $brand_item->taxonomy ); ?>"> <?php echo $brand_item->name; ?></a></li>
  <?php } ?>

I'm calling this in 5-10 times, and if the product listings become vast, this means i'm calling in every post on the site 10 times to load the menu etc.

Is there a more efficient way of doing this type of query?

---

Addition things to note:

The first query pulls in the posts assigned the taxonomy of $category->taxonomy.

$category is a taxonomy object selected in the admin by Advanced Custom Fields. For this example, say accessories is a term, and product_cat is the taxonomy @PieterGoosen

I need to return all the terms in brands that are also posts with the accessories term.

Answer 1

We can do quite a lot to improve performance of your code. Lets set some benchmarks first

BENCH MARKS

• I'm testing this with a

  • category taxonomy term which has 9 posts and

  • the post_tag taxonomy with 61 matching tags.

With your current code, I get the following results

• 69 queries in =/- 0.4s

That is pretty expensive and a huge amount of queries on such a small database and test subject

OPTIMIZATIONS

The first thing we will do, is to query only the post ID's from the posts because of the following reasons

• We do not need any post data

• We do not need post cache and post meta cache updated, we do not need that

• Obviously, only querying post ID's will increase performance drastically

Only querying the ID's have the drawback in that we also loose the term cache. Because we do not update the term cache, this will lead to a huge increase in db queries. In order to solve that, we will manually update the term cache with update_object_term_cache.

By this time, just on your query alone, you have gained 1db call and 0.02s, which is not that much, but it makes a huge difference on a huge database. The real gain will come in the next section

The really big gain is by passing the term object to get_term_link(), and not the term ID. If there is no terms in the term cache, and you pass the term ID to get_term_link(), instead of getting the term object from the cache, get_term_link() will query the db to get the term object. Just on test, this amounts to an extra 61 db calls, one per tag. Think about a few hundred tags.

We already have the term object, so we can simply pass the complete term object. You should always do that. Even if the term object is in cache, it is still very marginally slower to pass the term ID as we must still get the term object from the cache

I have cleaned up your code a bit. Note, I have used short array syntax which do need PHP 5.4+. Here is how your code could look like

$category       = get_category( 13 ); // JUST FOR TESTING< ADJUST TO YOUR NEEDS

$args = [
    'post_type' => 'product',
    'fields'    => 'ids', // Only query the post ID's, not complete post objects
    'tax_query' => [
        [
            'taxonomy'  => $category->taxonomy,
            'field'     => 'slug',
            'terms'     => $category->slug
        ]
    ]
];
$ids = get_posts( $args );

$links = [];
// Make sure we have ID'saves
if ( $ids ) {
    /**
     * Because we only query post ID's, the post caches are not updated which is
     * good and bad
     *
     * GOOD -> It saves on resources because we do not need post data or post meta data
     * BAD -> We loose the vital term cache, which will result in even more db calls
     *
     * To solve that, we manually update the term cache with update_object_term_cache
     */
    update_object_term_cache( $ids, 'product' );

    $term_names = [];

    foreach ( $ids as $id ) {
        $terms = get_object_term_cache( $id, 'post_tag' );
        foreach ( $terms as $term ) {
            if ( in_array( $term->name, $term_names ) )
                continue;

            $term_names[] = $term->name;

            $links[$term->name] = '<li><a href="' . get_term_link( $term ) . '">' . $term->name . '</a></li>';
        }
    }
}

if ( $links ) {
    ksort( $links );
    $link_string = implode( "\n\t" , $links );
} else {
    $link_string = '';
}

echo $link_string;

As it now stand, we have reduced the numbers down to 6 db queries in 0.04s which is a huge improvement.

We can even go further and store the results in a transient

$category       = get_category( 13 ); // JUST FOR TESTING< ADJUST TO YOUR NEEDS

$link_string    = '';
$transient_name = 'query_' . md5( $category->slug . $category->taxonomy );
if ( false === ( $link_string = get_transient( $transient_name ) ) ) {
    $args = [
        'post_type' => 'product',
        'fields'    => 'ids', // Only query the post ID's, not complete post objects
        'tax_query' => [
            [
                'taxonomy'  => $category->taxonomy,
                'field'     => 'slug',
                'terms'     => $category->slug
            ]
        ]
    ];
    $ids = get_posts( $args );

    $links = [];
    // Make sure we have ID'saves
    if ( $ids ) {
        /**
         * Because we only query post ID's, the post caches are not updated which is
         * good and bad
         *
         * GOOD -> It saves on resources because we do not need post data or post meta data
         * BAD -> We loose the vital term cache, which will result in even more db calls
         *
         * To solve that, we manually update the term cache with update_object_term_cache
         */
        update_object_term_cache( $ids, 'product' );

        $term_names = [];

        foreach ( $ids as $id ) {
            $terms = get_object_term_cache( $id, 'post_tag' );
            foreach ( $terms as $term ) {
                if ( in_array( $term->name, $term_names ) )
                    continue;

                $term_names[] = $term->name;

                $links[$term->name] = '<li><a href="' . get_term_link( $term ) . '">' . $term->name . '</a></li>';
            }
        }
    }


    if ( $links ) {
        ksort( $links );
        $link_string = implode( "\n\t" , $links );
    } else {
        $link_string = '';
    }

    set_transient( $transient_name, $link_string, 7 * DAY_IN_SECONDS );
}   

echo $link_string;

This will reduce everything to 2 queries in 0.002s. With the transient in place, we will just to flush the transient when we publish, update, delete or undelete posts. We will use the transition_post_status hook here

add_action( 'transition_post_status', function ()
{
    global $wpdb;
    $wpdb->query( "DELETE FROM $wpdb->options WHERE `option_name` LIKE ('_transient%_query_%')" );
    $wpdb->query( "DELETE FROM $wpdb->options WHERE `option_name` LIKE ('_transient_timeout%_query_%')" );
});

Answer 2

I am not so good with SQL. But this query does the job for me, I just combined two queries into one!

$brands = $wpdb->get_results("SELECT DISTINCT term_id "
        . "FROM {$wpdb->prefix}term_taxonomy "
        . "LEFT JOIN {$wpdb->prefix}term_relationships my_tr ON {$wpdb->prefix}term_taxonomy.term_taxonomy_id = my_tr.term_taxonomy_id "
        . "WHERE object_id IN (SELECT object_id FROM {$wpdb->prefix}term_relationships WHERE term_taxonomy_id = {$category->term_taxonomy_id}) "
        . "AND taxonomy = 'your_brand_taxonomy'");

However, I will recommend you to change the data structure with parent and child term instead of two different taxonomies.