0

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;

}

  • 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 Sep 19 '18 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 Sep 19 '18 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 Sep 19 '18 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 Sep 19 '18 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 Sep 19 '18 at 14:46
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). =)

  • 1
    I'm now getting an output with the new function code (hooray!). The total goals looks right, but the apps and subs totals are too high. I think I know why. The apps total is the number of times a specific $playerID is in the fields between PL1-11 and subs is the total where that $playerID is in the fields between PL12-18 and where SI12-18 != 0 – Pete Hayman Oct 30 '18 at 18:55
  • 1
    The totals the function code outputs looks like apps is counting all occurrences of a $playerID between PL1-18; subs all instances between PL12-18 (from what I can tell). I'm unsure how to adjust the REGEXP to accommodate this. I've done a pastebin here of an apps query for one player that returned the desired total via phpMyAdmin if that helps explain? – Pete Hayman Oct 30 '18 at 18:59
  • 1
    ...and that's got it! I made a couple of slight amendments to the function code, but the output is now present and correct. I'll post the code in my original question and up-vote your answer, as it has got me a solution to my issue. – Pete Hayman Nov 2 '18 at 12:29
  • 1
    I replaced the $range bit with a list of the fields to be included in the IN part of the query. I could see the logic behind $range and its use, but it didn't seem to want to deliver the output. – Pete Hayman Nov 2 '18 at 12:30
  • 1
    Hi Pete. Try this: SELECT DISTINCT p.ID FROM wp_posts p INNER JOIN wp_postmeta pm ON pm.post_id = p.ID WHERE p.post_type = 'match' AND p.post_status = 'publish' AND ( pm.meta_key REGEXP '^pl[0-9]+$' AND pm.meta_value+0 >= 1 ) ORDER BY p.post_date DESC which queries all published match posts, sorted by date in descending order. – Sally CJ Nov 6 '18 at 4:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.