Home › Forums › Backend Issues (wp-admin) › Nested meta_query causing database to time out (or give HTTP error 502)
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?
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.
$ids = array_unique(array_merge($array1, $array2, $array3));
post__in
argument. You would only need to do your sorting by the date in this last query.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>';
}
The topic ‘Nested meta_query causing database to time out (or give HTTP error 502)’ is closed to new replies.
Welcome to the Advanced Custom Fields community forum.
Browse through ideas, snippets of code, questions and answers between fellow ACF users
Helping others is a great way to earn karma, gain badges and help ACF development!
We use cookies to offer you a better browsing experience, analyze site traffic and personalize content. Read about how we use cookies and how you can control them in our Privacy Policy. If you continue to use this site, you consent to our use of cookies.