Display link to taxonomy archive only if it has posts with certain custom field values

Question

Posts on my site describe future events, with the event date recorded in a custom field in Unix time.

Generally, only upcoming events are relevant to visitors. As such, I'd like to display navigational links to taxonomy archive only if the taxonomy has at least one upcoming event (event date > today's date).

Is there a smarter way than the following?

<?php
$terms = get_terms( array(
    'taxonomy' => 'genre',
    'hide_empty' => true,
) );

$today = time() - 86400;

foreach($terms as $term) {
    $query = new WP_Query( array( 'meta_key' => 'event_date', 'meta_value' => $today, meta_compare => '>=', 'tax_query' => array( array( 'taxonomy' => 'genre', 'field' => 'term_id', 'terms' => $term->term_id, ), ), ) );
    if (($query->found_posts) > 1) {
    echo '<li><a href="/genre/' . $term->slug . '">' . $term->name . '</a></li>';
    }
} ?>

This works as expected, but adds about 100 queries to each page load which is a little disturbing.

Answer 1

There are two solutions that are a little bit smarter, that I can think of...

1. Don't retrieve everything

You don't need all info about these posts. All you need is the count, so... You don't have to get all fields for these posts and you don't need to get them all...

A "smarter" way to optimize this code is to query only for IDs of posts matching your query and to limit the query to only one result...

<?php
$terms = get_terms( array(
    'taxonomy' => 'genre',
    'hide_empty' => true,
) );

$today = time() - 86400;

foreach ($terms as $term) {
    $query = new WP_Query( array(
        'meta_key' => 'event_date',
        'meta_value' => $today,
        'meta_compare' => '>=', 
        'tax_query' => array( 
            array( 'taxonomy' => 'genre', 'field' => 'term_id', 'terms' => $term->term_id ),
        ),
        'posts_per_page' => 1,
        'fields' => 'ids'
    ) );
    if ( $query->found_posts > 0 ) {
        echo '<li><a href="/genre/' . $term->slug . '">' . $term->name . '</a></li>';
    }
}
?>

2. And if that's not optimized enough...

You can always write custom SQL query that will get only relevant terms... But it's always a risky way, because such queries will bypass filters... So if there is a filter that is hiding a term or some posts, then your custom query will ignore it.

PS.

This is not the best way to print term link:

<a href="/genre/' . $term->slug . '">

You should use get_term_link instead:

<a href="'. esc_attr( get_term_link( $term ) ) .'">