[wp-trac] [WordPress Trac] #52971: WP_Query's meta_query With Multiple EXISTS keys and OR Relation Doesn't Work Properly
WordPress Trac
noreply at wordpress.org
Mon Apr 5 12:31:54 UTC 2021
#52971: WP_Query's meta_query With Multiple EXISTS keys and OR Relation Doesn't
Work Properly
--------------------------+-----------------------------
Reporter: oxibug | Owner: (none)
Type: defect (bug) | Status: new
Priority: normal | Milestone: Awaiting Review
Component: Query | Version: 5.7
Severity: critical | Keywords: needs-patch
Focuses: |
--------------------------+-----------------------------
Hi There!
Another Very Weird Issue :)
Using WP_Query's [meta_query] with **OR relation** and Multiple **EXISTS
Keys** generates a very long unnecessary inner joins relation and **stuck
in an infinite loop** and **consume the MySQL resources** with high load.
I'm trying to get all post IDs that have one of 50 meta keys to replace
with new keys, So I collect all meta keys using foreach statement with
**OR relation** and pass it to the [meta_query] in WP_Query .. Very simple
but I noticed that there's no result and infinite page load and using high
resources of MySQL.
I create the SQL statement with $wpdb and it works very fast, So I create
a new WP_MetaQuery object to see the result and I noticed a huge
unnecessary INNER JOINs relation.
**Here's a full example:**
1. Insert 100 Test Posts and 50 Metas For Each: Append [?action=insert] in
the URL
2. Try to get posts using WP_Query: Append
[?action=wp_query_and_meta_query] in URL - **RESULT: Infinite Loop**
3. Try to use $wpdb instead: Append [?action=wpdb_query_get_ids] in URL -
**RESULT: Success, Takes few seconds**
4. See the SQL statement generated by WP_MetaQuery: add
[?action=show_meta_query_clause] - **RESULT: a Huge unnecessary **INNER
JOIN x AS x# ON y** relation**
5. [?action=wpdb_query_delete] to delete all added posts only.
{{{
add_action('wp', 'oxibug_trigger_action');
/**
* Trigger Suitable Function
*
* @return void
*/
function oxibug_trigger_action() {
if( ! isset( $_GET['action'] ) ) {
return;
}
$action = wp_strip_all_tags( $_GET['action'] );
switch( strtolower( $action ) ) {
case 'insert': {
oxibug_insert_posts_and_metas();
} break;
case 'wp_query_and_meta_query': {
oxibug_WP_Query_and_meta_query();
} break;
case 'show_meta_query_clause': {
oxibug_show_meta_query_clause();
} break;
case 'wpdb_query_get_ids': {
oxibug_wpdb_query_get_ids();
} break;
case 'wpdb_query_delete': {
oxibug_wpdb_query_delete();
} break;
}
}
/**
* Return New Meta Key
*
* @param mixed $key
* @return string
*/
function oxibug_get_new_meta_key( $key ) {
return sanitize_text_field( sprintf( 'oxibug_xyz%s', $key ) );
}
/**
* Return Key-Value Pairs array with Old and New meta keys
*
* @return array
*/
function oxibug_legacy_and_new_meta_keys() {
return [
'oxibug_abc_post_main_color' =>
oxibug_get_new_meta_key('_page_main_color'),
'oxibug_abc_page_layout' =>
oxibug_get_new_meta_key('_page_layout'),
'oxibug_abc_post_sbwide' =>
oxibug_get_new_meta_key('_page_sb_wide'),
'oxibug_abc_post_sbnarrow' =>
oxibug_get_new_meta_key('_page_sb_narrow'),
'oxibug_abc_page_sbwide' =>
oxibug_get_new_meta_key('_page_sb_wide'),
'oxibug_abc_page_sbnarrow' =>
oxibug_get_new_meta_key('_page_sb_narrow'),
'oxibug_abc_self_video_m4v_url' =>
oxibug_get_new_meta_key('_format_video_selfhosted_mp4'),
'oxibug_abc_self_video_ogv_url' =>
oxibug_get_new_meta_key('_format_video_selfhosted_ogv'),
'oxibug_abc_self_video_webmv_url' =>
oxibug_get_new_meta_key('_format_video_selfhosted_webmv'),
'oxibug_abc_self_video_poster_url' =>
oxibug_get_new_meta_key('_format_video_selfhosted_poster'),
/* Audio */
'oxibug_abc_soundcloud_url' =>
oxibug_get_new_meta_key('_format_audio_oembed'),
'oxibug_abc_self_audio_mp3_url' =>
oxibug_get_new_meta_key('_format_audio_selfhosted_mp3'),
'oxibug_abc_self_audio_oga_url' =>
oxibug_get_new_meta_key('_format_audio_selfhosted_ogg'),
'oxibug_abc_self_audio_m4a_url' =>
oxibug_get_new_meta_key('_format_audio_selfhosted_m4a'),
/* Post Formats: Image | Video */
'oxibug_abc_lightbox_check' =>
oxibug_get_new_meta_key('_format_image_lighbox_enable'),
'oxibug_abc_post_banner_caption' =>
oxibug_get_new_meta_key('_format_status_banner_caption'),
'oxibug_abc_quote_text' =>
oxibug_get_new_meta_key('_format_quote_text'),
'oxibug_abc_quote_author' =>
oxibug_get_new_meta_key('_format_quote_author_name'),
'oxibug_abc_url_text' =>
oxibug_get_new_meta_key('_format_link_text'),
'oxibug_abc_url_destination' =>
oxibug_get_new_meta_key('_format_link_url'),
/*
* Layout Settings - if Old = [hide] turn ON the new option
*
* */
'oxibug_abc_post_breadcrumb' =>
oxibug_get_new_meta_key('_hide_breadcrumb'),
'oxibug_abc_post_meta_info' =>
oxibug_get_new_meta_key('_hide_metas'),
'oxibug_abc_post_share_box' =>
oxibug_get_new_meta_key('_hide_share_icons'),
'oxibug_abc_post_tags' =>
oxibug_get_new_meta_key('_hide_tags_box'),
'oxibug_abc_post_author_box' =>
oxibug_get_new_meta_key('_hide_author_box'),
'oxibug_abc_related_posts' =>
oxibug_get_new_meta_key('_hide_related_posts_box'),
'oxibug_abc_posts_navigation' =>
oxibug_get_new_meta_key('_hide_nav_box'),
/* Review Items */
'oxibug_abc_post_review_types' =>
oxibug_get_new_meta_key('_review_type'), /* [disabled] => Add 0 to the
new meta [_review_show] */
'oxibug_abc_post_review_position' =>
oxibug_get_new_meta_key('_review_position'),
'oxibug_abc_post_reviews_summation' =>
oxibug_get_new_meta_key('_review_items_avg'), /* This field is Dynamic -
SUM of all review items */
'oxibug_abc_review_item' =>
oxibug_get_new_meta_key('_review_items'),
'oxibug_abc_post_review_title' =>
oxibug_get_new_meta_key('_review_title'),
'oxibug_abc_post_review_desc' =>
oxibug_get_new_meta_key('_review_desc'),
'oxibug_abc_post_review_summary_title' =>
oxibug_get_new_meta_key('_review_summary_title'),
'oxibug_abc_post_review_summary_desc' =>
oxibug_get_new_meta_key('_review_summary_desc'),
'oxibug_abc_post_review_user_rates' =>
oxibug_get_new_meta_key('_review_user_ratings_status'),
'oxibug_abc_post_review_user_rates_bgcolor' =>
oxibug_get_new_meta_key('_review_user_ratings_result_bgcolor'),
'oxibug_abc_post_review_btn_text' =>
oxibug_get_new_meta_key('_review_add_btn_text'),
'oxibug_abc_post_review_btn_icon' =>
oxibug_get_new_meta_key('_review_add_btn_icon'),
'oxibug_abc_post_review_btn_url' =>
oxibug_get_new_meta_key('_review_add_btn_url'),
'oxibug_abc_post_review_btn_bgcolor' =>
oxibug_get_new_meta_key('_review_add_btn_bgcolor'),
'oxibug_abc_post_review_pros_word' =>
oxibug_get_new_meta_key('_review_pros_word'),
'oxibug_abc_post_review_pros_icon' =>
oxibug_get_new_meta_key('_review_pros_icon'),
'oxibug_abc_post_review_pros_list' =>
oxibug_get_new_meta_key('_review_pros_list'),
'oxibug_abc_post_review_cons_word' =>
oxibug_get_new_meta_key('_review_cons_word'),
'oxibug_abc_post_review_cons_icon' =>
oxibug_get_new_meta_key('_review_cons_icon'),
'oxibug_abc_post_review_cons_list' =>
oxibug_get_new_meta_key('_review_cons_list'),
/* Post Views */
'oxibug_abc_post_views_count' =>
oxibug_get_new_meta_key('_post_views'),
];
}
/**
* Insert 100 Test Posts and some Meta Keys
*
*/
function oxibug_insert_posts_and_metas() {
$legacy_and_new_keys = oxibug_legacy_and_new_meta_keys();
$result = [];
for( $i=0; $i<100; $i++ ) {
$post_id = wp_insert_post( [
'post_type' => 'post',
'post_title' => wp_strip_all_tags( sprintf( 'Test Meta
Query Post #:%s', $i ) ),
'post_content' => sprintf( 'Test Meta Query Post Content
#:%s', $i ),
'post_content_filtered' => '',
'post_excerpt' => '',
'post_status' => 'publish',
// 'post_author' => 1,
// 'post_category' => [],
'comment_status' => '',
'ping_status' => '',
'post_password' => '',
'to_ping' => '',
'pinged' => '',
'post_parent' => 0,
'menu_order' => 0,
'guid' => '',
'import_id' => 0,
'context' => '',
'post_date' => '',
'post_date_gmt' => '',
], TRUE, TRUE );
if( ! is_wp_error( $post_id ) ) {
$result[ $post_id ] = [];
foreach( (array) array_keys( $legacy_and_new_keys ) as
$_legacy_key ) {
$upstatus = update_post_meta( $post_id, $_legacy_key, ''
);
$result[ $post_id ][ $_legacy_key ] = $upstatus ? 'Added'
: 'Failed';
}
/* Clean Cache */
clean_post_cache( $post_id );
}
else {
echo 'ERROR: Insert Post Failed!';
}
}
/* DEBUG */
echo sprintf( '%d Posts Inserted', count( $result ) );
// echo print_r( $result );
}
/**
* --- DEBUG ---
* Show the Meta Query SQL Statement
*
*/
function oxibug_show_meta_query_clause() {
/**
*
* @var wpdb
* */
global $wpdb;
$legacy_meta_keys = oxibug_legacy_and_new_meta_keys();
$meta_query = [
'relation' => 'OR'
];
foreach( (array) array_keys( $legacy_meta_keys ) as $_legacy_key ) {
$meta_query[] = [
'key' => $_legacy_key,
'compare' => 'EXISTS'
];
}
$objMetaQuery = new WP_Meta_Query( $meta_query );
echo print_r( $objMetaQuery->get_sql( 'post', $wpdb->posts, 'ID', NULL
) );
}
/**
* Query using WP_Query and meta_query key inside it
*
*/
function oxibug_WP_Query_and_meta_query() {
/**
*
* @var wpdb
* */
global $wpdb;
$legacy_meta_keys = oxibug_legacy_and_new_meta_keys();
$meta_query = [
'relation' => 'OR'
];
foreach( (array) array_keys( $legacy_meta_keys ) as $_legacy_key ) {
$meta_query[] = [
'key' => $_legacy_key,
'compare' => 'EXISTS'
];
}
$obj_WP_Query = new WP_Query( [
'numberposts' => -1,
'paged' => null,
'post_type' => 'post',
'post_status' => 'publish',
'meta_query' => $meta_query,
'fields' => 'ids',
'cache_results' => false,
] );
/*
* Stuck in an Infinite Loop Because of the Huge SQL Statement
* generated by [meta_query]
*
* */
if( $obj_WP_Query->have_posts() ) {
echo sprintf( '%d Posts Found', $obj_WP_Query->found_posts );
/* DEBUG - NOT Working - */
// echo print_r( $obj_WP_Query->meta_query->get_sql( 'post',
$wpdb->posts, 'ID', NULL ) );
}
else {
echo 'No Posts Found';
}
$obj_WP_Query->reset_postdata();
wp_cache_flush();
}
/**
* Get Posts IDs by one of the meta_key(s) provided
*
*/
function oxibug_wpdb_query_get_ids() {
global $wpdb;
$legacy_meta_keys = oxibug_legacy_and_new_meta_keys();
$qry = "SELECT DISTINCT tbl_posts.ID FROM {$wpdb->posts} tbl_posts
INNER JOIN {$wpdb->postmeta} tbl_metas ON ( tbl_posts.ID =
tbl_metas.post_id ) WHERE";
/* Add [OR] in the second condition */
$where_clause = 0;
foreach( (array) array_keys( $legacy_meta_keys ) as $_legacy_key ) {
$where_clause++;
$qry .= ( $where_clause === 1 ) ?
$wpdb->prepare( ' tbl_metas.meta_key = %s', esc_sql(
$_legacy_key ) ) :
$wpdb->prepare( ' OR tbl_metas.meta_key = %s', esc_sql(
$_legacy_key ) );
}
$result = $wpdb->get_results( $qry );
$wpdb->flush();
echo print_r( wp_list_pluck( $result, 'ID' ) );
}
/**
* --- DEBUG ---
*
* DELETE all added Posts by meta_key(s)
*
*/
function oxibug_wpdb_query_delete() {
global $wpdb;
$legacy_meta_keys = oxibug_legacy_and_new_meta_keys();
$qry = "DELETE tbl_posts, tbl_metas FROM {$wpdb->posts} tbl_posts
INNER JOIN {$wpdb->postmeta} tbl_metas ON ( tbl_posts.ID =
tbl_metas.post_id ) WHERE";
$where_clause = 0;
foreach( (array) array_keys( $legacy_meta_keys ) as $_legacy_key ) {
$where_clause++;
$qry .= ( $where_clause === 1 ) ?
$wpdb->prepare( ' tbl_metas.meta_key = %s', esc_sql(
$_legacy_key ) ) :
$wpdb->prepare( ' OR tbl_metas.meta_key = %s', esc_sql(
$_legacy_key ) );
}
$result = $wpdb->get_results( $qry );
$wpdb->flush();
}
}}}
--
Ticket URL: <https://core.trac.wordpress.org/ticket/52971>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform
More information about the wp-trac
mailing list