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>';
}
You must be logged in to reply to this topic.
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!
🤔 Curious about the ACF user experience? So are we! Help guide the evolution of ACF by taking part in our first ever Annual Survey and guarantee you’re represented in the results. https://t.co/0cgr9ZFOJ5
— Advanced Custom Fields (@wp_acf) May 8, 2023
© 2023 Advanced Custom Fields.
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 Cookie Policy. If you continue to use this site, you consent to our use of cookies.