1

I need to display the total number of votes that have been made for a specific custom post type. Here's an example of how the wp_postmeta table looks for these keys/values:

meta_id post_id meta_key meta_value
22 4 vote 4
26 6 vote 0
27 7 vote 1
32 10 vote 10

Suppose the first and last rows are wp_postmeta rows for the custom post type movies. How can I get the total of 14?

Here's the code I've tried, but it's not working. It seems to be adding up the values of all post types and not just for the specified custom post type.

function meta_val( $key = '', $type = 'post', $status = 'publish' ) {
  global $wpdb;

  $r = $wpdb->get_col( $wpdb->prepare( "
    SELECT pm.meta_value FROM {$wpdb->postmeta} pm
    LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
    WHERE pm.meta_key = '%s' 
    AND p.post_status = '%s' 
    AND p.post_type = '%s'
  ", $key, $status, $type ) );

  return $r;
}

function total_votes( $post_type ) {
  $votes = meta_val( 'vote', 'movies' );
  $sum = array_sum( $votes );

  return $sum;
}
1
  • Great question. I'm going to try to write a MySQL statement that handles this all for you in one fell swoop. Aug 10, 2015 at 18:29

2 Answers 2

1

There may not be a need for a custom query, but I'd recommend it rather than hitting the database: - once for the WP_Query - once more for each post entry

And then manually working out the math in PHP. Do it all at once with one MySQL statement.

select sum(PM.meta_value) from wp_postmeta PM
join wp_posts P on P.ID = PM.post_id
where P.post_type='page'
    and PM.meta_key='test'

With your code above, replace the $r=... line with this:

$r = $wpdb->get_results( $wpdb->prepare( "
    SELECT SUM(pm.meta_value) FROM {$wpdb->postmeta} pm
    LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
    WHERE pm.meta_key = '%s' 
    AND p.post_status = '%s' 
    AND p.post_type = '%s'
  ", $key, $status, $type ) );

$r[0] should be your total. :)

2

Try this, should get you started.. (not tested though)

function count_total_vote() {
    $args = array(
        'post_type' => 'post', // Your post type
        'status'    => 'publish',
        'meta_key'  => 'vote', // Meta Key
    );

    $total = 0;
    $votes = new WP_Query( $args );
    if ( $votes->have_posts() ) {
        while ( $votes->have_posts() ) {
        $votes->the_post();
            $total += get_post_meta( get_the_ID(), 'vote', true );
        }
    }

    echo $total;

}  

No need for a custom query to database.

4
  • +1 for "the WordPress way", though it could run into some serious performance issues with x postmeta calls. Aug 10, 2015 at 20:19
  • Amm.. @EricHolmes Can you explain why? Would come really handy in my current project as I am dealing with a lot of meta fields.
    – Abhik
    Aug 11, 2015 at 5:09
  • 1
    get_post_meta is hitting the database for the metadata. This isn't a problem when you are dealing with 10, 15, even 100 posts. But even at 200 posts, you're making 201 DB calls instead of 1, and that can start to become a noticeable amount of time, because most servers handle approximately 10-30 simultaneous calls.This means you have to send 7 batches of calls for 201 requests - just means more page load time. Aug 11, 2015 at 16:49
  • no problem! It's about catching things like that - site speed on the back end almost never has 1-off issues, it's 10 or 15 things like this that all accumulate 100ms to the page load :). Aug 11, 2015 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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