Nested meta_query causing database to time out (or give HTTP error 502)

• jeffenwolf

• July 28, 2017 at 8:09 am

I’m having an issue creating a query to return valid “deals” (basically coupons). A deal is valid if it’s within the start and end date of the deal or after an optional “post date”.
This code block was working to return valid deals without the “post date” functionality:

<?php
					$count = 0;
					date_default_timezone_set('America/Chicago');
					$currentdate = date('Ymd');   
					
					
					$args = array (
						'posts_per_page' => -1,
						'post_type' => 'deals',
						'order' => 'ASC',
						'orderby' => 'meta_value_num',
						'meta_key'  => 'end_date',
						'meta_query' => array(
							'relation' => 'OR',																						
							array(
								//if start date is before today and end date is blank
								'relation' => 'AND',
								array(
									'key' => 'start_date',
									'value' => $currentdate,
									'compare' => '<='
								),
								array(
									'key' => 'end_date',
									'value' => '',
									'compare' => '='
								),
							),
												
						
							array(
								//else end date is not blank and todays date falls between the start and end date
								'relation' => 'AND',
								 array(
									'key' => 'end_date',
									'value' => '',
									'compare' => '!='
								),
								array(
									'relation' => 'AND',
									 array(
										'key' => 'start_date',
										'value' => $currentdate,
										'compare' => '<='
									),
									 array(
										'key' => 'end_date',
										'value' => $currentdate,
										'compare' => '>='
									),
								),
							),
						)
					);

But when I add post_date, the page that calls this query either gives a 502 error or the database goes offline.

$args = array (
						'posts_per_page' => -1,
						'post_type' => 'deals',
						'order' => 'ASC',
						'orderby' => 'meta_value_num',
						'meta_key'  => 'end_date',
						'meta_query' => array(
							'relation' => 'OR',
						
							array(
								//post date is before today

									'key' => 'post_date',
									'type' => 'DATE',
									'value' => $currentdate,
									'compare' => '<=',
									
								),													
						
							array(
								//if start date is before today and end date is blank
								'relation' => 'AND',
								array(
									'key' => 'start_date',
									'value' => $currentdate,
									'compare' => '<='
								),
								array(
									'key' => 'end_date',
									'value' => '',
									'compare' => '='
								),
							),
												
						
							array(
								//else end date is not blank and todays date falls between the start and end date
								'relation' => 'AND',
								 array(
									'key' => 'end_date',
									'value' => '',
									'compare' => '!='
								),
								array(
									'relation' => 'AND',
									 array(
										'key' => 'start_date',
										'value' => $currentdate,
										'compare' => '<='
									),
									 array(
										'key' => 'end_date',
										'value' => $currentdate,
										'compare' => '>='
									),
								),
							),
						)
					);

I’m probably doing something very inefficiently. How should this be structured?

• John Huebner

• July 28, 2017 at 8:56 am

This isn’t an inefficiency or ACF. It’s more of a problem with WP, although, that’s not really the case either. The problems are that your query is creating many, many joins doing multiple searches on a database column than is not optimized for searching.

This is explained in many places like https://wordpress.stackexchange.com/questions/158898/meta-query-terribly-slow and https://tommcfarlin.com/wp_query-and-multiple-meta-keys/.

There might not be a way to actually correct this, what follows is only a possible work around that has not been tested.

1. Split your OR meta queries and do 3 separate queries
2. Return just the post IDs from these 3 queries
3. Use array_merge() http://php.net/manual/en/function.array-merge.php to combine the results of the previous queries and array_unique() http://php.net/manual/en/function.array-unique.php to get only unique values $ids = array_unique(array_merge($array1, $array2, $array3));
4. Use the resulting array to do a 4th query using this array as the post__in argument. You would only need to do your sorting by the date in this last query.

• ForbiddenChunk

• April 9, 2019 at 11:27 pm

I’ve had a similar issue where my query had a lot of arrays;

'meta_query'    => array(
            'relation'      => 'AND',
            // array(
            //     'key'       => 'product_type',
            //     'value'     => $filterProductType,
            //     'compare'   => '=',
            // ),
            // array(
            //     'key'       => 'load_class',
            //     'value'     => $filterProductUsed,
            //     'compare'   => '=',
            // ),
            // array(
            //     'key'       => 'length_of_opening',
            //     'value'     => $lengthValue,
            //     'compare'   => '=',
            // ),
            // array(
            //     'key'       => 'width_of_opening',
            //     'value'     => $widthValue,
            //     'compare'   => '=',
            // ),
            // array(
            //     'key'       => 'depth',
            //     'value'     => $depthValue,
            //     'compare'   => '=',
            // ),
            array(
                'key'       => 'lockable',
                'value'     => $lockAble,
                'compare'   => 'LIKE',
            ),
            array(
                'key'       => 'grating_opening_orientation',
                'value'     =>  $grateOpening,
                'compare'   => 'LIKE',
            ),
            array(
                'key'       => 'cover_type',
                'value'     =>  $coverType,
                'compare'   => 'LIKE',
            ),
            array(
                'key'       => 'enhanced_skid_resistance',
                'value'     =>  $enhancedSkid,
                'compare'   => 'LIKE',
            ),
            array(
                'key'       => 'accessories',
                'value'     =>  $accessoriesOpt,
                'compare'   => 'LIKE',
            ),
            array(
                'key'       => 'sealable',
                'value'     =>  $sealableOpt,
                'compare'   => 'LIKE',
            ),
            array(
                'key'       => 'install_plus',
                'value'     =>  $installPlus,
                'compare'   => 'LIKE',
            ),
        ),

So been trying to do the above, however it doesn’t run the query anymore and comes back with no “products” found.

$results = array_unique ( array_intersect( $default_types, $first_result_types, $lockable_types, $grate_types ) );

$posts = new WP_Query( $results );
    if ( $posts->have_posts() ){
        while($posts->have_posts()){
            $posts->the_post();
            get_template_part( 'template-parts/content' );
        }
    }
    else {
        print '<h1>Sorry no products matched the filter</h1>';
    }