Meta_query key for nested fields

• pawciak

• September 29, 2022 at 9:51 am

Hello,

I do have a custom post type called products, that has a content build around repeaters. I have the following content construction:

MAIN REPEATER (name: sales)
field1: all_regions_name
field2: all_regions_url
field3: all_regions_date_start
field4: all_regions_date_end
nested repeater (name: different_regions)
field1: name
field2: url
field3: date_start
field4: date_end

Based on that I want to build a WP_Query that displays only posts which based on current date:
A. is included in the range FROM all_regions_date_start TO all_regions_date_end OR
B. is included in the range from nested repeater date_start TO date_end

I have added this code:

function sales_replace_repeater_field( $where ) {
     $where = str_replace( "meta_key = 'sales_$", "meta_key LIKE 'sales_%", $where );
     return $where;
}
add_filter( 'posts_where', 'sales_replace_repeater_field' );

and this one:

function diff_replace_repeater_field( $where ) {
     $where = str_replace( "meta_key = 'different_regions_$", "meta_key LIKE 'different_regions_%", $where );
     return $where;
}
add_filter( 'posts_where', 'diff_replace_repeater_field' );

Here are my loop args:

$today = date('Ymd');
 $sales_query = new WP_Query( array(
        'post_type' => 'products',
		'meta_query'=> array(
			'relation' => 'AND',
			array(
			  'key' => 'sales_$_all_regions_start_date',
			  'compare' => '<=',
			  'value' => $today
			),
			array(
			  'key' => 'sales_$_all_regions_end_date',
			  'compare' => '>=',
			  'value' => $today
			)
		),
        'tax_query' => array(
            array(
                'taxonomy' => 'category',
                'field' => 'slug',
                'terms' => array( $sales_product->slug ),
                'operator' => 'IN'
            )
        )
    ) );

But I’ve stucked. I have no idea, how to add there condition/meta_query key for the second (nested) repeater.

How to achieve that? What is then a key for that nested repeater? Because both:
– different_regions_$_start_date
– different_regions_$_end_date

Doesn’t work.. Thanks!!

• John Huebner

• September 29, 2022 at 10:25 am

You cannot do a query like this on repeater fields.

The actual field keys are build, for example for sales=>different_regions=>start_date, like this

"sales_{$row_index}_different_regions_{$row_index}_start_date"

This cannot be accomplished using WP_Query.

In addition to this there isn’t any way to do any type of a query that will associate the indexes used for start_date with the indexes used for end_date. This is simply not possible in an SQL query.

Here is an example. Lets say that you have these start and end dates in the repeater, these are just examples and not necessarily what is stored in the db.

January 1, 2022 => February 1, 2022
March 1, 2022 => April 1, 2022

if today was February 10, 2022 then this post would match because this date is between one of the start dates and one of the end dates.

• pawciak

• September 30, 2022 at 4:05 am

oh dangg.. Thanks John for the clarification. Wasn’t aware of that. Having in mind your experience – what would be then the most optimal way of achieving that? I was thinking that if I have a CPT called products and to create another CPT like promotions and then based on that somehow connect both..

• John Huebner

• September 30, 2022 at 11:06 am

I have only one experience that could potentially relate to this. In my case I was creating a custom events calendar for a client. The client wanted to have events that could have multiple dates, for example a class that happens every week.

They also wanted the events to show multiple times in the “Upcoming Events” list, once for each date. This is not possible with a standard WP query as WP will only return a post once in each query.

I had a couple of choices. The first was to make them create a new event for every date. The problems with this were that it would be a PITA to use and there would not be a consistent URL for the event. Having a single event page with a consistent UEL multiple dates would be better for SEO.

The only logical way I could provide this was to use a repeater. The main issues here were going to be having them appear in lists multiple times and how I was going to sort them.

What I ended up doing was using child posts.

Each event post has a repeater that allows entering start and end dates. Each event post also has other fields of data related to the post.

I used parent/child posts. When an event is saved a child post is created for each date entered into the repeater. To the child post I also copied other fields that would be needed for searching, filtering and ordering the events.

In addition to the above I also added actions/filters for when an event was updated to check the existing child posts and compare the dates, deleting and adding child posts as needed. There are also filters/action that happen when an event is trashed, untrashed and deleted to apply those same actions to all the child posts.

In the admin I used a pre_get_posts filter to hide all of the child posts, the client does not even know they exist.

On the front end I used a pre_get_posts filter for archives to only query the child posts and not the parent posts. All child post URLs redirect to the single parent event post. All the links in the archive actually pointed to the parent post, not the child post.

This was complicated, but the only way I could find to accomplish the needed end result.

Your case is far more complicated, but it has the same idea. You want to be able to query and sort by start/end date. You need something that has only one start date and end date.

Another alternative:

Save the dates and the posts they are associated with into a lookup array as a WP option.

The array I would set up would be something like

$option_name = array(
  // an array of post id => array pairs, the array will hold dates
  {$post_id} = array(
    array(
      //each sub array holds a start date and end date
      'start_date' => 'value',
      'end_date' => 'value'
    ),
    array(
      //each sub array holds a start date and end date
      'start_date' => 'value',
      'end_date' => 'value'
    )
    // etc
  }
)

When I want to search I would load this option, loop over the array, find the entries that match the date I’m looking for and collect a list of post IDs then use this list of post IDs in the “post__in” argument of a WP_Query.