Meta Query Array Error 500

admin管理员组

文章数量:1130349

I have two CPTs – Match and Player. Each Match post entry contains details relating to a specific match (who played, scorers, etc.). This info is inputted via ACF Post Object fields corresponding with an individual Player. What I'm looking to achieve, though, is to generate on each Player post a total of how many appearances, sub appearances and goals.

To do this, I am using the following meta_query to get the total where a player started a match. The query is cumbersome, but it does the job. The problem I'm now having, though, is that attempts to generate the total number of sub appearances using the same query structure exhausts memory and returns an Error 500.

$args = array('post_type'  => 'match',
              'meta_query' => array('relation' => 'OR',
                 array('relation' => 'AND',
                   array('key'=> 'pl12',
                         'value'=> $playerID,
                         'compare'=> '=',),
                   array('key'=> 'si12',
                         'value'=> '0',
                         'compare'=> '!=',),
                   ),
                   array('relation' => 'AND',
                     array('key'=> 'pl13',
                           'value'=> $playerID,
                           'compare'=> '=',),
                     array('key'=> 'si13',
                           'value'=> '0',
                           'compare'=> '!=',),
                   ),
                   ...
                   array('relation' => 'AND',
                     array('key'=> 'pl18',
                           'value'=> $playerID,
                           'compare'=> '=',),
                   array('key'=> 'si18',
                         'value'=> '0',
                         'compare'=> '!=',),
                   ),
                 ),
              );
$subs = new WP_Query( $args );
echo "(".$subs->found_posts.")";
wp_reset_postdata(); 

UPDATE: SOLUTION

Using functions.php as suggested, custom SQL queries have provided a reliable and working solution to provide an output of total appearances, sub appearances and goals for each Player using the Match post data.

function get_player_stats( $player_id ) {
    global $wpdb;

$stats = [];

$stats['apps'] = (int) $wpdb->get_var( $wpdb->prepare( "
    SELECT COUNT(pm.meta_id) FROM {$wpdb->postmeta} pm
        INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
    WHERE pm.meta_key IN ('PL1','PL2','PL3','PL4','PL5','PL6','PL7','PL8','PL9','PL10','PL11')
        AND pm.meta_value = %d
        AND p.post_type   = 'match'
        AND p.post_status = 'publish'
", $player_id ) );

$stats['subs'] = (int) $wpdb->get_var( $wpdb->prepare( "
    SELECT COUNT(DISTINCT pm.meta_id) FROM {$wpdb->postmeta} pm
        INNER JOIN {$wpdb->postmeta} pm2 ON pm2.post_id = pm.post_id
        INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
    WHERE 1
        AND (
            pm.meta_key IN ('PL12',  'PL13',  'PL14',  'PL15',  'PL16',  'PL17',  'PL18')
            AND pm.meta_value = %d
        )
        AND (
            pm2.meta_key IN ('si12','si13','si14','si15','si16','si17','si18')
            AND ( SUBSTR(pm2.meta_key, 3) = SUBSTR(pm.meta_key, 3) )
            AND pm2.meta_value+0 > 0
        )
        AND p.post_type   = 'match'
        AND p.post_status = 'publish'
    ", $player_id ) );

    $stats['goals'] = (int) $wpdb->get_var( $wpdb->prepare( "
        SELECT COUNT(pm.meta_id) FROM {$wpdb->postmeta} pm
            INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
            WHERE pm.meta_key REGEXP '^S[0-9]+$'
            AND pm.meta_value = %d
            AND p.post_type   = 'match'
            AND p.post_status = 'publish'
    ", $player_id ) );

return $stats;

}

I have two CPTs – Match and Player. Each Match post entry contains details relating to a specific match (who played, scorers, etc.). This info is inputted via ACF Post Object fields corresponding with an individual Player. What I'm looking to achieve, though, is to generate on each Player post a total of how many appearances, sub appearances and goals.

To do this, I am using the following meta_query to get the total where a player started a match. The query is cumbersome, but it does the job. The problem I'm now having, though, is that attempts to generate the total number of sub appearances using the same query structure exhausts memory and returns an Error 500.

$args = array('post_type'  => 'match',
              'meta_query' => array('relation' => 'OR',
                 array('relation' => 'AND',
                   array('key'=> 'pl12',
                         'value'=> $playerID,
                         'compare'=> '=',),
                   array('key'=> 'si12',
                         'value'=> '0',
                         'compare'=> '!=',),
                   ),
                   array('relation' => 'AND',
                     array('key'=> 'pl13',
                           'value'=> $playerID,
                           'compare'=> '=',),
                     array('key'=> 'si13',
                           'value'=> '0',
                           'compare'=> '!=',),
                   ),
                   ...
                   array('relation' => 'AND',
                     array('key'=> 'pl18',
                           'value'=> $playerID,
                           'compare'=> '=',),
                   array('key'=> 'si18',
                         'value'=> '0',
                         'compare'=> '!=',),
                   ),
                 ),
              );
$subs = new WP_Query( $args );
echo "(".$subs->found_posts.")";
wp_reset_postdata(); 

UPDATE: SOLUTION

Using functions.php as suggested, custom SQL queries have provided a reliable and working solution to provide an output of total appearances, sub appearances and goals for each Player using the Match post data.

function get_player_stats( $player_id ) {
    global $wpdb;

$stats = [];

$stats['apps'] = (int) $wpdb->get_var( $wpdb->prepare( "
    SELECT COUNT(pm.meta_id) FROM {$wpdb->postmeta} pm
        INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
    WHERE pm.meta_key IN ('PL1','PL2','PL3','PL4','PL5','PL6','PL7','PL8','PL9','PL10','PL11')
        AND pm.meta_value = %d
        AND p.post_type   = 'match'
        AND p.post_status = 'publish'
", $player_id ) );

$stats['subs'] = (int) $wpdb->get_var( $wpdb->prepare( "
    SELECT COUNT(DISTINCT pm.meta_id) FROM {$wpdb->postmeta} pm
        INNER JOIN {$wpdb->postmeta} pm2 ON pm2.post_id = pm.post_id
        INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
    WHERE 1
        AND (
            pm.meta_key IN ('PL12',  'PL13',  'PL14',  'PL15',  'PL16',  'PL17',  'PL18')
            AND pm.meta_value = %d
        )
        AND (
            pm2.meta_key IN ('si12','si13','si14','si15','si16','si17','si18')
            AND ( SUBSTR(pm2.meta_key, 3) = SUBSTR(pm.meta_key, 3) )
            AND pm2.meta_value+0 > 0
        )
        AND p.post_type   = 'match'
        AND p.post_status = 'publish'
    ", $player_id ) );

    $stats['goals'] = (int) $wpdb->get_var( $wpdb->prepare( "
        SELECT COUNT(pm.meta_id) FROM {$wpdb->postmeta} pm
            INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
            WHERE pm.meta_key REGEXP '^S[0-9]+$'
            AND pm.meta_value = %d
            AND p.post_type   = 'match'
            AND p.post_status = 'publish'
    ", $player_id ) );

return $stats;

}

• wp-query
• meta-query
• 500-internal-error

Share Improve this question edited Nov 2, 2018 at 12:28 Pete Hayman asked Sep 19, 2018 at 8:34 Pete HaymanPete Hayman 7199 bronze badges 5

• Can you please clean up the question removing unnecessary "appreciate", "this means", "I have a WordPress site". Just ask the question with no poetry, be dry as dust. Or else nobody will read. – Max Yudin Commented Sep 19, 2018 at 9:22
• WordPress does a good job many times: in my experience not when it comes to queries. Have you considered writing your own SQL query for this? – kero Commented Sep 19, 2018 at 10:23
• I had been wondering if this might be best done with an SQL query @kero, so I'll take a look – thanks. – Pete Hayman Commented Sep 19, 2018 at 10:47
• @PeteHayman what is the value of "SI" (e.g. SI1)? Is it a Post Object field, or is the value a number of the player's total sub-appearances in the match? – Sally CJ Commented Sep 19, 2018 at 14:25
• @SallyCJ The value of SI is a number; it is the minute in the match at which a sub-appearance starts, so anything that isn't 0 means a sub-appearance is made. – Pete Hayman Commented Sep 19, 2018 at 14:46

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

In my previous answer (that I've deleted..), I suggested using custom SQL queries, which worked for me. However, since it didn't work (well) for you, let's try using this function:

function get_player_stats( $player_id ) {
    $apps = 0;  // Total appearances.
    $subs = 0;  // Total sub-appearances.
    $goals = 0; // Total goals.

    // Get all published `match` posts.
    $posts = get_posts( [
        'post_type'      => 'match',
        'posts_per_page' => -1,
    ] );

    foreach ( $posts as $post ) {
        for ( $n = 1; $n <= 18; $n++ ) {
            // Check if the player is $player_id
            $pl = get_post_meta( $post->ID, 'pl' . $n, true );
            if ( $player_id == $pl ) {
                $apps++;

                // Check his sub-out appearance.
                $so = get_post_meta( $post->ID, 'so' . $n, true );
                $subs += $so ? 1 : 0;

                // Check his sub-in appearance.
                $si = get_post_meta( $post->ID, 'si' . $n, true );
                $subs += $si ? 1 : 0;
            }

            // Check if he scored goal {$n}-th.
            $s = get_post_meta( $post->ID, 's' . $n, true );
            if ( $player_id == $s ) {
                $goals++;
            }
        }
    }

    // WP_Query was called; so resets $wp_query
    wp_reset_query();

    return compact( 'apps', 'subs', 'goals' );
}

If you have more than 18 players (in any matches), change the 18 in the $n <= 18; to the correct number of players; e.g. 20 as in $n <= 20;.

And here's how you could use the get_player_stats() function, which returns an associative array with apps, subs, and goals as the keys:

$stats = get_player_stats( $playerID );
//var_dump( $stats );

echo $stats['apps'] . ' apps, ' .
    $stats['subs'] . ' subs, and ' .
    $stats['goals'] . ' goals.<br>';

I hope this answer works for you, and let me know if you need any clarification (on any parts of the code). =)

I have two CPTs – Match and Player. Each Match post entry contains details relating to a specific match (who played, scorers, etc.). This info is inputted via ACF Post Object fields corresponding with an individual Player. What I'm looking to achieve, though, is to generate on each Player post a total of how many appearances, sub appearances and goals.

To do this, I am using the following meta_query to get the total where a player started a match. The query is cumbersome, but it does the job. The problem I'm now having, though, is that attempts to generate the total number of sub appearances using the same query structure exhausts memory and returns an Error 500.

$args = array('post_type'  => 'match',
              'meta_query' => array('relation' => 'OR',
                 array('relation' => 'AND',
                   array('key'=> 'pl12',
                         'value'=> $playerID,
                         'compare'=> '=',),
                   array('key'=> 'si12',
                         'value'=> '0',
                         'compare'=> '!=',),
                   ),
                   array('relation' => 'AND',
                     array('key'=> 'pl13',
                           'value'=> $playerID,
                           'compare'=> '=',),
                     array('key'=> 'si13',
                           'value'=> '0',
                           'compare'=> '!=',),
                   ),
                   ...
                   array('relation' => 'AND',
                     array('key'=> 'pl18',
                           'value'=> $playerID,
                           'compare'=> '=',),
                   array('key'=> 'si18',
                         'value'=> '0',
                         'compare'=> '!=',),
                   ),
                 ),
              );
$subs = new WP_Query( $args );
echo "(".$subs->found_posts.")";
wp_reset_postdata(); 

UPDATE: SOLUTION

Using functions.php as suggested, custom SQL queries have provided a reliable and working solution to provide an output of total appearances, sub appearances and goals for each Player using the Match post data.

function get_player_stats( $player_id ) {
    global $wpdb;

$stats = [];

$stats['apps'] = (int) $wpdb->get_var( $wpdb->prepare( "
    SELECT COUNT(pm.meta_id) FROM {$wpdb->postmeta} pm
        INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
    WHERE pm.meta_key IN ('PL1','PL2','PL3','PL4','PL5','PL6','PL7','PL8','PL9','PL10','PL11')
        AND pm.meta_value = %d
        AND p.post_type   = 'match'
        AND p.post_status = 'publish'
", $player_id ) );

$stats['subs'] = (int) $wpdb->get_var( $wpdb->prepare( "
    SELECT COUNT(DISTINCT pm.meta_id) FROM {$wpdb->postmeta} pm
        INNER JOIN {$wpdb->postmeta} pm2 ON pm2.post_id = pm.post_id
        INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
    WHERE 1
        AND (
            pm.meta_key IN ('PL12',  'PL13',  'PL14',  'PL15',  'PL16',  'PL17',  'PL18')
            AND pm.meta_value = %d
        )
        AND (
            pm2.meta_key IN ('si12','si13','si14','si15','si16','si17','si18')
            AND ( SUBSTR(pm2.meta_key, 3) = SUBSTR(pm.meta_key, 3) )
            AND pm2.meta_value+0 > 0
        )
        AND p.post_type   = 'match'
        AND p.post_status = 'publish'
    ", $player_id ) );

    $stats['goals'] = (int) $wpdb->get_var( $wpdb->prepare( "
        SELECT COUNT(pm.meta_id) FROM {$wpdb->postmeta} pm
            INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
            WHERE pm.meta_key REGEXP '^S[0-9]+$'
            AND pm.meta_value = %d
            AND p.post_type   = 'match'
            AND p.post_status = 'publish'
    ", $player_id ) );

return $stats;

}

I have two CPTs – Match and Player. Each Match post entry contains details relating to a specific match (who played, scorers, etc.). This info is inputted via ACF Post Object fields corresponding with an individual Player. What I'm looking to achieve, though, is to generate on each Player post a total of how many appearances, sub appearances and goals.

To do this, I am using the following meta_query to get the total where a player started a match. The query is cumbersome, but it does the job. The problem I'm now having, though, is that attempts to generate the total number of sub appearances using the same query structure exhausts memory and returns an Error 500.

$args = array('post_type'  => 'match',
              'meta_query' => array('relation' => 'OR',
                 array('relation' => 'AND',
                   array('key'=> 'pl12',
                         'value'=> $playerID,
                         'compare'=> '=',),
                   array('key'=> 'si12',
                         'value'=> '0',
                         'compare'=> '!=',),
                   ),
                   array('relation' => 'AND',
                     array('key'=> 'pl13',
                           'value'=> $playerID,
                           'compare'=> '=',),
                     array('key'=> 'si13',
                           'value'=> '0',
                           'compare'=> '!=',),
                   ),
                   ...
                   array('relation' => 'AND',
                     array('key'=> 'pl18',
                           'value'=> $playerID,
                           'compare'=> '=',),
                   array('key'=> 'si18',
                         'value'=> '0',
                         'compare'=> '!=',),
                   ),
                 ),
              );
$subs = new WP_Query( $args );
echo "(".$subs->found_posts.")";
wp_reset_postdata(); 

UPDATE: SOLUTION

Using functions.php as suggested, custom SQL queries have provided a reliable and working solution to provide an output of total appearances, sub appearances and goals for each Player using the Match post data.

function get_player_stats( $player_id ) {
    global $wpdb;

$stats = [];

$stats['apps'] = (int) $wpdb->get_var( $wpdb->prepare( "
    SELECT COUNT(pm.meta_id) FROM {$wpdb->postmeta} pm
        INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
    WHERE pm.meta_key IN ('PL1','PL2','PL3','PL4','PL5','PL6','PL7','PL8','PL9','PL10','PL11')
        AND pm.meta_value = %d
        AND p.post_type   = 'match'
        AND p.post_status = 'publish'
", $player_id ) );

$stats['subs'] = (int) $wpdb->get_var( $wpdb->prepare( "
    SELECT COUNT(DISTINCT pm.meta_id) FROM {$wpdb->postmeta} pm
        INNER JOIN {$wpdb->postmeta} pm2 ON pm2.post_id = pm.post_id
        INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
    WHERE 1
        AND (
            pm.meta_key IN ('PL12',  'PL13',  'PL14',  'PL15',  'PL16',  'PL17',  'PL18')
            AND pm.meta_value = %d
        )
        AND (
            pm2.meta_key IN ('si12','si13','si14','si15','si16','si17','si18')
            AND ( SUBSTR(pm2.meta_key, 3) = SUBSTR(pm.meta_key, 3) )
            AND pm2.meta_value+0 > 0
        )
        AND p.post_type   = 'match'
        AND p.post_status = 'publish'
    ", $player_id ) );

    $stats['goals'] = (int) $wpdb->get_var( $wpdb->prepare( "
        SELECT COUNT(pm.meta_id) FROM {$wpdb->postmeta} pm
            INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
            WHERE pm.meta_key REGEXP '^S[0-9]+$'
            AND pm.meta_value = %d
            AND p.post_type   = 'match'
            AND p.post_status = 'publish'
    ", $player_id ) );

return $stats;

}

• wp-query
• meta-query
• 500-internal-error

Share Improve this question edited Nov 2, 2018 at 12:28 Pete Hayman asked Sep 19, 2018 at 8:34 Pete HaymanPete Hayman 7199 bronze badges 5

• Can you please clean up the question removing unnecessary "appreciate", "this means", "I have a WordPress site". Just ask the question with no poetry, be dry as dust. Or else nobody will read. – Max Yudin Commented Sep 19, 2018 at 9:22
• WordPress does a good job many times: in my experience not when it comes to queries. Have you considered writing your own SQL query for this? – kero Commented Sep 19, 2018 at 10:23
• I had been wondering if this might be best done with an SQL query @kero, so I'll take a look – thanks. – Pete Hayman Commented Sep 19, 2018 at 10:47
• @PeteHayman what is the value of "SI" (e.g. SI1)? Is it a Post Object field, or is the value a number of the player's total sub-appearances in the match? – Sally CJ Commented Sep 19, 2018 at 14:25
• @SallyCJ The value of SI is a number; it is the minute in the match at which a sub-appearance starts, so anything that isn't 0 means a sub-appearance is made. – Pete Hayman Commented Sep 19, 2018 at 14:46

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

In my previous answer (that I've deleted..), I suggested using custom SQL queries, which worked for me. However, since it didn't work (well) for you, let's try using this function:

function get_player_stats( $player_id ) {
    $apps = 0;  // Total appearances.
    $subs = 0;  // Total sub-appearances.
    $goals = 0; // Total goals.

    // Get all published `match` posts.
    $posts = get_posts( [
        'post_type'      => 'match',
        'posts_per_page' => -1,
    ] );

    foreach ( $posts as $post ) {
        for ( $n = 1; $n <= 18; $n++ ) {
            // Check if the player is $player_id
            $pl = get_post_meta( $post->ID, 'pl' . $n, true );
            if ( $player_id == $pl ) {
                $apps++;

                // Check his sub-out appearance.
                $so = get_post_meta( $post->ID, 'so' . $n, true );
                $subs += $so ? 1 : 0;

                // Check his sub-in appearance.
                $si = get_post_meta( $post->ID, 'si' . $n, true );
                $subs += $si ? 1 : 0;
            }

            // Check if he scored goal {$n}-th.
            $s = get_post_meta( $post->ID, 's' . $n, true );
            if ( $player_id == $s ) {
                $goals++;
            }
        }
    }

    // WP_Query was called; so resets $wp_query
    wp_reset_query();

    return compact( 'apps', 'subs', 'goals' );
}

If you have more than 18 players (in any matches), change the 18 in the $n <= 18; to the correct number of players; e.g. 20 as in $n <= 20;.

And here's how you could use the get_player_stats() function, which returns an associative array with apps, subs, and goals as the keys:

$stats = get_player_stats( $playerID );
//var_dump( $stats );

echo $stats['apps'] . ' apps, ' .
    $stats['subs'] . ' subs, and ' .
    $stats['goals'] . ' goals.<br>';

I hope this answer works for you, and let me know if you need any clarification (on any parts of the code). =)

本文标签： Meta Query Array Error 500