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;
}
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). =)
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:250means a sub-appearance is made. – Pete Hayman Commented Sep 19, 2018 at 14:46