Make WordPress Core

Opened 4 years ago

#52971 new defect (bug)

WP_Query's meta_query With Multiple EXISTS keys and OR Relation Doesn't Work Properly

Reported by: oxibug's profile oxibug Owned by:
Milestone: Awaiting Review Priority: normal
Severity: critical Version: 5.7
Component: Query Keywords: needs-patch
Focuses: Cc:

Description

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();

}

Change History (0)

Note: See TracTickets for help on using tickets.