I have a custom meta query which is terribly slow or doesn’t even load till the end. With up to three arrays in 'meta_query' the query works fine, with four and up it doesn’t work anymore.
When searching for a reason I found this post but I am absolutely not familiar with custom db queries.
Any help is much appreciated! Thank you!
<?php
$args = array(
'post_type' => $post_type,
'posts_per_page' => -1,
'meta_query' => array(
'relation' => 'OR',
array(
'key'=>'_author',
'value'=> $author_single["fullname"],
'compare' => '='
),
array(
'key'=>'_publisher',
'value'=> $author_single["fullname"],
'compare' => '='
),
array(
'key'=>'_contributor_1',
'value'=> $author_single["fullname"],
'compare' => '='
),
array(
'key'=>'_contributor_2',
'value'=> $author_single["fullname"],
'compare' => '='
),
array(
'key'=>'_contributor_3',
'value'=> $author_single["fullname"],
'compare' => '='
)
)
);
$posts = new WP_Query($args);
if( $posts->have_posts() ) : while( $posts->have_posts() ) : $posts->the_post(); ?>
<li><a href="https://wordpress.stackexchange.com/questions/158898/<?php echo get_the_permalink(); ?>"><?php the_title(); ?></a></li>
<?php endwhile; endif; ?>
– – – – –
Updated code with the additions boger made:
page.php
<?php
$args = array(
'post_type' => $post_type,
'posts_per_page' => -1,
'meta_query' => array(
'relation' => 'OR',
array(
'key'=>'_author',
'value'=> $author_single["fullname"],
'compare' => '='
),
array(
'key'=>'_publisher',
'value'=> $author_single["fullname"],
'compare' => '='
),
array(
'key'=>'_contributor_1',
'value'=> $author_single["fullname"],
'compare' => '='
),
array(
'key'=>'_contributor_2',
'value'=> $author_single["fullname"],
'compare' => '='
),
array(
'key'=>'_contributor_3',
'value'=> $author_single["fullname"],
'compare' => '='
)
)
);
add_filter( 'posts_clauses', 'wpse158898_posts_clauses', 10, 2 );
$posts = new WP_Query($args);
if( $posts->have_posts() ) : while( $posts->have_posts() ) : $posts->the_post(); ?>
<li><a href="https://wordpress.stackexchange.com/questions/158898/<?php echo get_the_permalink(); ?>"><?php the_title(); ?></a></li>
<?php endwhile; endif;
remove_filter( 'posts_clauses', 'wpse158898_posts_clauses', 10 ); ?>
functions.php
function wpse158898_posts_clauses( $pieces, $query ) {
global $wpdb;
$relation = isset( $query->meta_query->relation ) ? $query->meta_query->relation : 'AND';
if ( $relation != 'OR' ) return $pieces; // Only makes sense if OR.
$prepare_args = array();
$key_value_compares = array();
foreach ( $query->meta_query->queries as $meta_query ) {
// Doesn't work for IN, NOT IN, BETWEEN, NOT BETWEEN, NOT EXISTS.
if ( ! isset( $meta_query['value'] ) || is_array( $meta_query['value'] ) ) return $pieces; // Bail if no value or is array.
$key_value_compares[] = '(pm.meta_key = %s AND pm.meta_value ' . $meta_query['compare'] . ' %s)';
$prepare_args[] = $meta_query['key'];
$prepare_args[] = $meta_query['value'];
}
$sql=" JOIN " . $wpdb->postmeta . ' pm on pm.post_id = ' . $wpdb->posts . '.ID'
. ' AND (' . implode( ' ' . $relation . ' ', $key_value_compares ) . ')';
array_unshift( $prepare_args, $sql );
$pieces['join'] = call_user_func_array( array( $wpdb, 'prepare' ), $prepare_args );
$pieces['where'] = preg_replace( '/ AND[^w]+wp_postmeta.*$/s', '', $pieces['where'] ); // Zap postmeta clauses.
return $pieces;
}
– – –
$posts->request outputs
$args = array(
'post_type' => $post_type,
'posts_per_page' => -1,
'meta_query' => array(
'relation' => 'OR',
array(
'key'=>'_author',
'value'=> "Hanna Meier",
'compare' => '='
),
array(
'key'=>'_publisher',
'value'=> "Friedhelm Peters",
'compare' => '='
)
)
);
without the custom query
SELECT wp_vacat_posts.* FROM wp_vacat_posts INNER JOIN wp_vacat_postmeta ON (wp_vacat_posts.ID = wp_vacat_postmeta.post_id)
INNER JOIN wp_vacat_postmeta AS mt1 ON (wp_vacat_posts.ID = mt1.post_id) WHERE 1=1 AND wp_vacat_posts.post_type="product" AND (wp_vacat_posts.post_status="publish" OR wp_vacat_posts.post_status="private") AND ( (wp_vacat_postmeta.meta_key = '_author' AND CAST(wp_vacat_postmeta.meta_value AS CHAR) = 'Hanna Meier')
OR (mt1.meta_key = '_publisher' AND CAST(mt1.meta_value AS CHAR) = 'Friedhelm Peters') ) GROUP BY wp_vacat_posts.ID ORDER BY wp_vacat_posts.post_date DESC
with the custom query
SELECT wp_vacat_posts.* FROM wp_vacat_posts
JOIN wp_vacat_postmeta pm on pm.post_id = wp_vacat_posts.ID AND ((pm.meta_key = '_author' AND pm.meta_value="Hanna Meier") OR (pm.meta_key = '_publisher' AND pm.meta_value="Friedhelm Peters")) WHERE 1=1 AND wp_vacat_posts.post_type="product" AND (wp_vacat_posts.post_status="publish" OR wp_vacat_posts.post_status="private") AND ( (wp_vacat_postmeta.meta_key = '_author' AND CAST(wp_vacat_postmeta.meta_value AS CHAR) = 'Hanna Meier')
OR (mt1.meta_key = '_publisher' AND CAST(mt1.meta_value AS CHAR) = 'Friedhelm Peters') ) GROUP BY wp_vacat_posts.ID ORDER BY wp_vacat_posts.post_date DESC