6

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.

  • I don't understand your first query, is that suppose to mirror the main query. What is $category and where is it coming from. Do you need posts that are in both taxonomies regardless of the terms in the second taxonomy. Please file an edit with the answers to this request – Pieter Goosen Mar 9 '16 at 11:41
  • For single query I think custom SQL! – Sumit Mar 9 '16 at 12:47
  • @Sumit I thought though, but couldn't get the relationship between the two taxonomies to work – how would you consolidate it? – Andrew Lazarus Mar 9 '16 at 12:48
12

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_%')" );
});
  • 1
    Thanks for your feedback. I will need to test this and come back to you. – Pieter Goosen Mar 10 '16 at 4:07
  • 1
    I have quickly tested this this morning before leaving for work, and I can confirm the duplicates, thanks for pointing this out. I quickly tested a possibility, which worked (you can view it here), but I'm not happy with that. There is another possibility which I need to test which will probably be much better and faster, but I can only test this this afternoon when I get back home. I will keep you posted on this. You can use use the revision in the meantime, just remember to flush the transient ;-) – Pieter Goosen Mar 10 '16 at 5:56
  • 1
    Got it, I have updated my answer. This is as fast as I could get it with what is build into WordPress. I hope this helps ;-) – Pieter Goosen Mar 10 '16 at 16:46
  • 1
    Any feedback on this issue, anything I can help with further regarding this issue. Just one final note, I hope you have flushed/deleted the transient before testing this again otherwise you might get incorrect results – Pieter Goosen Mar 11 '16 at 4:35
  • 2
    You have been a tremendous help with your exploration of optimisation – I didn't realise we could use transients to store this kind of information, so not only Id your answer aid with the optimisation of this question, but gave me insight to how I can use this technique for other purposes. I really appreciate your help @Pieter – Andrew Lazarus Mar 11 '16 at 9:04
0

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.

  • I can't change the structure of the site i'm afraid, you see these two taxonomies are not related, but they have a relationship. Categories and brands. For example, Accessories can have more than one brand, and brands can have more than just Accessories to offer. It would mean that being able to have a brand that could do different categories would require multiple instances of that brand. This quickly turns into chaos when trying to manage a website at scale. – Andrew Lazarus Mar 10 '16 at 7:40
  • Then you've only two option using a transient as Pieter answered or direct SQL! – Sumit Mar 10 '16 at 7:44

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.