Wordpress meta_query to filter out events

My event-post-type has two different meta_fields age_from and age_to

Now I want to filter events based on an age-span from a select box. Rule is that at least one age in the range age_from - age_to must match the span from the select box. Eventa have ages from min 6 to max 25.

Spans in the filter: 6-8 9-12 13-18 19-25

Example Event with age_from=8 and age_to=17

Select box choice 9-12 years

Event should show.

Any advice on design of meta_query is highly appreciated.

protected function pre_get_posts( WP_Query $query ) {
    $existing_rules = (array) $query->get( 'meta_query' );
    $values         = (array) $this->currentValue;

    preg_match_all('!\d+!', $this->currentValue[0], $ages);
    
    $age_from = $ages[0][0];
    $age_to = $ages[0][1];

    write_log( $values );

        $new_rules[] = array(
            
            'relation' => 'OR',
            
            array (             
                'key'     => $this->age_from,
                'value'   => [$age_from, $age_to],
                'compare' => 'BETWEEN',
            ),
            array (             
                'key'     => $this->age_to,
                'value'   => [$age_from, $age_to],
                'compare' => 'BETWEEN',
            )
        );
    
    $relationship = apply_filters( 'tribe_events_filter_additional_fields_relationship', 'AND' );

    $nest = apply_filters( 'tribe_events_filter_additional_fields_nest_meta_queries',
        version_compare( $GLOBALS['wp_version'], '4.1', '>=' )
    );
    
    if ( $nest ) {
        $new_rules = array(
            __CLASS__ => $new_rules,
        );
    }

    $meta_query = array_merge_recursive( $existing_rules, $new_rules );
    
    if ( ! empty( $relationship ) && $nest ) {
        $meta_query[ __CLASS__ ][ 'relation' ] = $relationship;
    } elseif ( ! empty( $relationship ) ) {
        $meta_query[ 'relation' ] = $relationship;
    }

    write_log( $meta_query );

    $query->set( 'meta_query', $meta_query );
    
}

Solution

I cannot comment on the code around the query criteria, but let's look at the condition logic before touching code:

1 2 3 4 5 6 7 8 9
      +-----+     Event
    +-----+       Filter 1
        +---+     Filter 2
            +---+ Filter 3
+---------------+ Filter 4

The above depicts the 4 cases your filter range should match the event:

When the filter starts before, but ends within event range.
When the filter starts within, and ends within the event range.
When the filter starts within, and ands after the event range.
When the filter starts before, and ends after the event range.

There are 2 rules here which hold true to all the above cases:

The start position of the filter is below (or equal to) the end position of the event.
The end position of the filter is above (or equal to) the end position of the event.

Let's take these two rules and see if they apply to filters we expect to be outside of an event's range:

1 2 3 4 5 6 7 8 9
      +-----+     Event
  +-+             Filter 5
              +-+ Filter 6

Filter 5 does not match, because its end position is before the start position of the event. Filter 6 does not match, because its start position is after the end position of the event.

So we can use this for the query:

WHERE [filter end] >= [event start] AND [filter start] <= [event end]

Or, swapping the variables around:

WHERE [event start] <= [filter end] AND [event end] >= [filter start]

Translating into your wp_meta_query criteria:

        $new_rules[] = array(
            
            'relation' => 'AND',
            
            array (             
                'key'     => $this->age_from,
                'value'   => $age_to,
                'compare' => '<=',
            ),
            array (             
                'key'     => $this->age_to,
                'value'   => $age_from,
                'compare' => '>=',
            )
        );